Menu
I have a small curiosity about MySQL table locks.
Say I want to lock two tables. I execute this command:
LOCK TABLES table1 WRITE, table2 WRITE
And then I checked if the tables have indeed been locked by executing:
SHOW OPEN TABLES IN mydatabase WHERE In_use > 0
I have noticed tho that if I run two lock commands sequentitally for example:
LOCK TABLES table1 WRITE
LOCK TABLES table2 WRITE
And then check which tables are locked using the same command only table2 is marked as locked. Why is this so?
Thanks
752
The most common reason implicit locks are created is an INSERT operation: successfully inserted rows are not visible to other transactions until the inserting transaction commits, and it is a common situation that a single transaction inserts many rows, so it is cheaper to not create explicit locks for newly inserted ...
MySQL uses table locking (instead of row locking or column locking) on all table types, except InnoDB and BDB tables, to achieve a very high lock speed.
The correct way to use LOCK TABLES and UNLOCK TABLES with transactional tables, such as InnoDB tables, is to begin a transaction with SET autocommit = 0 (not START TRANSACTION ) followed by LOCK TABLES , and to not call UNLOCK TABLES until you commit the transaction explicitly.
LOCK TABLES is not transaction-safe and implicitly commits any active transaction before attempting to lock the tables.
So, in the first case, you have one transaction which hold 2 tables locked, in the second only one, because LOCK TABLES table1 WRITE had been commited
119
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
