MySQL Multiple table locks

Question

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

Answer 1

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