MySQL Bugs: #9459: deadlock with flush with lock, and lock table write

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	CPU Architecture:	Any

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.

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

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.

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

Pushed to 4.1.14 and 5.0.12.

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>