Locking a table within a transaction?

Select count(*) from table within a transaction, locks the talbe on msSQL 2000.

And many other DBMS I guess – Luis Siquot May 17 at 14:14 Thanks! As @MarkR explained, that did the trick – Flavien May 17 at 14:37 It is not actually necessary; it won't lock the table and any other query would also start a snapshot (in repeatable-read txn isolation). SELECTs never create locks in Innodb inn repeatable-read transaction isolation.

– MarkR May 17 at 20:56.

Perhaps you have missed the point of transactions. If you use repeatable-read transaction isolation, inserts, updates etc, can happen during your transaction, BUT YOU WILL NOT SEE THEM.So as far as your process is concerned, the table is locked for inserts/updates. Except they are still happening, they're still durable to disc, and other processes can continue.

After you do your first "select", a snapshot is created, and you are effectively reading that snapshot, not the latest version. If this is what you want, repeatable-read works well for you.

Thanks for the explanation. I actually didn't fully understand that. So as @Luis Siquot suggested, at the very beggining of my transaction I put this select count(*) from table and after that I was able to select whenever I wanted in this "snapshot" – Flavien May 17 at 14:36 In fact the snapshot starts whenever it needs to; there is (usually) no need to explicitly start it.As soon as you read from an innodb table, the snapshot starts.

If you really want you can do START TRANSACTION WITH CONSISTENT SNAPSHOT which starts one immediately. – MarkR May 17 at 20:55 very clear (i didn't know), but anyway after START TRANSACTION WITH CONSISTENT SNAPSHOT, I think you must do SELECT COUNT(*) FROM TABLE in order to ´add´ the TABLE to the snapshot? Or START TRANSACTION WITH CONSISTENT SNAPSHOT locks entry DB(s)?

– Luis Siquot May 17 at 21:32 No, you do not need to "add" any tables to the snapshot; once it starts it is consistent for all tables. You don't really need to use the WITH CONSISTENT SNAPSHOT normally either. SELECT COUNT(*) is often a very inefficient query, and should be avoided if it's not necessary (on large tables).

– MarkR May 170 at 11:47.

If you're using PHP, so when there is a transaction going on, you can set a SESSION variable to tell the script not to do anything with the database, i.e. $_SESSION'on_going_transaction' = true. When the transaction is completed, just destroy the SESSION variable so that another transaction can occur.

This is much easier.

This solution is not thread safe. Scripts in a totally different session (or no session at all)could make database modifications while one sessions's 'on_going_transaction' variable is set. – Asaph May 17 at 13:40 Yes my problem is that there may be multiple threads running at the same time so I have to make sure that no other thread can make an insert on a specific table while I'm running my transaction – Flavien May 17 at 13:45.

Within a transaction, locks the talbe on msSQL 2000.

Perhaps you have missed the point of transactions.

I cant really gove you an answer,but what I can give you is a way to a solution, that is you have to find the anglde that you relate to or peaks your interest. A good paper is one that people get drawn into because it reaches them ln some way.As for me WW11 to me, I think of the holocaust and the effect it had on the survivors, their families and those who stood by and did nothing until it was too late.