Locking a table within a transaction

3
votes

I would like to be able to lock an entire table to prevent any INSERTs or UPDATEs in it between the "beginTransaction" and the ending "commit" or "rollback".

I know that beginning a transaction results in an implicit UNLOCK TABLES and that a LOCK table results in a implicit COMMIT... so is there any way to do what I want?

3
mysqltransactionslocking
Why would you want to do this? The whole point of transactions is to enable concurrent operations on databases. - Lightness Races in Orbit
@Tomalak Geret'kal I just wanted to make sure that between a select TABLE A and a select in TABLE B there was no insert in TABLE B. - Flavien

3 Answers

2
votes

Why? 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.

1
votes
select count(*) from table

within a transaction, locks the talbe on msSQL 2000

-1
votes

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.