Bug #20048 FLUSH TABLES WITH READ LOCK causes a deadlock
Submitted: 24 May 2006 14:17 Modified: 26 May 2006 16:20
Reporter: Michael Widenius Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Locking Severity:S3 (Non-critical)
Version:5.0 OS:Linux (SuSE 10.0)
Assigned to: Michael Widenius CPU Architecture:Any

[24 May 2006 14:17] Michael Widenius
Description:
FLUSH TABLES WITH READ LOCK causes a deadlock in some senarios

How to repeat:
#
# Test of deadlock problem when doing FLUSH TABLE with read lock
# (Bug was in NTPL threads in Linux when using different mutex while
#  waiting for a condtion variable)

create table t1 (c1 int);
create table t2 (c1 int);

connect (con1,localhost,root,,);
connect (con3,localhost,root,,);

connection con1;
lock table t1 write;

connection con2;
send flush tables with read lock;
--sleep 1

connection con3;
send insert into t2 values(1);
--sleep 1

connection con1;
unlock tables;
disconnect con1;

connection con2;
reap;
disconnect con2;

connection con3;
# It hangs here (insert into t2 does not end).
reap;
disconnect con3;

connection default;
drop table t1, t2;

Suggested fix:
This is caused by a bug in NTPL threads where you use different mutex to pthread_cond_wait(COND_refresh, mutex). Even if the mutex is correctly used (according to posix) in this case, NTPL threads fails to deliver a broadcast in some cases.

Fix is to add a new condition variable to handle condtion signaling in case of global read locks
[24 May 2006 14:19] 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/commits/6835
[26 May 2006 10:27] Michael Widenius
Fix will be in 5.0.22 and 5.1.10
[26 May 2006 16:20] Paul Dubois
Noted in 5.0.23, 5.1.10 changelogs.

A bug in NTPL threads on Linux could result in a deadlock with FLUSH
TABLES WITH READ LOCK under some conditions.
[9 Oct 2006 2:01] Colin Charles
Its NPTL (Native POSIX Thread Library). Docs should reflect this change.
[9 Oct 2006 2:22] Jon Stephens
Updated changelog entries per Colin's note.