Detect deadlocks with LOCK TABLES with autocommit = 0 and InnoDB tables-CodePudding

https://dev.mysql.com/doc/refman/8.0/en/innodb-deadlock-detection.html

InnoDB is aware of table locks if innodb_table_locks = 1 (the default) and autocommit = 0, and the MySQL layer above it knows about row-level locks. Otherwise, InnoDB cannot detect deadlocks where a table lock set by a MySQL LOCK TABLES statement or a lock set by a storage engine other than InnoDB is involved. Resolve these situations by setting the value of the innodb_lock_wait_timeout system variable.

According to the documentation, I understand that using innodb tables and using LOCK TABLES as indicated below will detect deadlocks:

SET autocommit=0;
LOCK TABLES t1 WRITE, t2 READ, ...;
... do something with tables t1 and t2 here ...
COMMIT;
UNLOCK TABLES;

Is this correct or did I misunderstand? Please correct me

CodePudding user response：

If you use a single LOCK TABLES statement to lock all the tables you will access during your transaction, the tables are locked atomically. There is no chance for deadlock.

For example, in the example you included in your question (the example shown in the manual), since you lock tables t1 and t2, a concurrent session that attempts to lock either one of these tables will be blocked. Or if the concurrent session locks them first, then your session will be blocked for both table locks. The one who waits will not lock either table until the other unlocks.

If the sessions lock one table at a time, this is not atomic, and if they accumulate locks, then they might lock one table and then be blocked waiting for a lock on the other table. Thus it would be a deadlock.

In fact, they do not accumulate table locks with successive LOCK TABLES statements.

https://dev.mysql.com/doc/refman/8.0/en/lock-tables.html:

UNLOCK TABLES explicitly releases any table locks held by the current session. LOCK TABLES implicitly releases any table locks held by the current session before acquiring new locks.

So deadlocks do not occur. If you try to execute one LOCK TABLES after another, you effectively let the other session acquire the table locks that your session held. So no deadlock.