Bug #9459 deadlock with flush with lock, and lock table write
Submitted: 29 Mar 2005 16:55 Modified: 8 Aug 2005 16:06
Reporter: Martin Friebe (Gold Quality Contributor) (OCA) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:4.1.10a OS:Any (*)
Assigned to: Ingo Strüwing

[29 Mar 2005 16:55] Martin Friebe
Description:
the command sequence below (Example 1) (issued from within the same connection), causes a deadlock.

A kill command issued to the hanging thread is also ignored.
(you must obtain a WRITE lock, the sequence works fine, if the first command only obtains a read lock)

The documentation does state that a new "lock table", will release previous locks. I couldn't find any reference, if that includes locks from "flush with lock", however an read lock request, after a flush lock, will succeed, a write lock will fail.
The read lock seems not to realease the lock obtained by flush (is that wanted?), as the write lock continues to fail.

How to repeat:
#Example 1

lock table some_table write;
flush tables with read lock;
# will hang / cannot be killed

#Example  2
#
#issuing the commands in opposite order

flush tables with read lock;
lock table a2 write;
#ERROR 1223 (HY000): Can't execute the query because you have a conflicting read lock

lock table a2 read;
# is fine

lock table a2 write;
#ERROR 1223 (HY000): Can't execute the query because you have a conflicting read lock
# still failing

Suggested fix:
for case 1:
remove the deadlock: either have flush with lock, behave the same way like lock table, and release other locks (in the same process), or by making flush issuing an error.

case 2: decide (and possibly implement) wanted behaviour, and document.
[3 Aug 2005 9:16] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/27841
[3 Aug 2005 9:30] Ingo Strüwing
The patch fixes the deadlock.

The rest of the bug report describes expected behaviour.

Please understand that FLUSH TABLES WITH READ LOCK acquires a global read lock, which is a different mechanism than LOCK TABLE. A LOCK TABLE releases previous locks from LOCK TABLE, but does not influence the global read lock. UNLOCK TABLE, however, does releases previous locks from LOCK TABLE *and* the global read lock (if it is held by the current session).

While the global read lock is in effect, read locks are allowed on tables (implicit and by LOCK TABLE), but write locks are not.
[5 Aug 2005 13:37] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/27919
[7 Aug 2005 20:36] Ingo Strüwing
Pushed to 4.1.14 and 5.0.12.
[8 Aug 2005 16:06] Mike Hillyer
Documented in 4.1.14 and 5.0.12 changelogs:

<listitem><para><literal>FLUSH TABLES WITH READ LOCK</literal> combined with <literal>LOCK TABLE .. WRITE</literal> caused deadlock. (Bug #9459)</para></listitem>