Search code examples
mysqlinnodbdeadlock

InnoDB deadlocks when using LOCK TABLES

I'm aware that InnoDB can detect and resolve deadlocks on row-level, but what happens when you use LOCK TABLES?

For some reasons I find it occasionally easier to lock an entire table than to figure out the logic of avoiding potential deadlocks.

So, let's assume you do:

LOCK TABLES tableA WRITE, tableB WRITE

Now, two transactions (T1 and T2) attempt this at the same time. For some reason we assume that T1 could not acquire a lock on tableA, but could get one for tableB. Then T2 comes along, gets a lock on tableA but not on tableB (T1 has that). These two now wait for each other. What happens?

  1. Is this how the locks would be granted; regardless of the order specified by LOCK TABLES? If not then would it not mean that two identical statements could never deadlock each other?
  2. Is there a way to handle this properly? Must it just time out?

According to the manual, it is okay to use LOCK TABLES within a transaction, as specified here: https://dev.mysql.com/doc/refman/5.7/en/lock-tables-and-transactions.html

Solution

  • LOCK TABLES (and you need to say READ or WRITE, etc) will lock all the tables simultaneously. There is no chance of two multi-table LOCK statements getting into a deadlock. UNLOCK TABLES unlocks them simultaneously.