CODEWITHSUNDEEP
mysql
Gabriel Spiteri
Gabriel Spiteri

Reputation: 4978

MySQL Multiple table locks

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

Upvotes: 13

Views: 12900

Answers (2)

Mike
Mike

Reputation: 131

Lock tables will first unlock all tables locked by the current session before performing the specified lock. So the call to lock table 2 is unlocking table 1.

Upvotes: 1

ravnur
ravnur

Reputation: 2852

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

Upvotes: 12

Related Questions