MySQL Bugs: #20048: FLUSH TABLES WITH READ LOCK causes a deadlock

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

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

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

Fix will be in 5.0.22 and 5.1.10

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.

Its NPTL (Native POSIX Thread Library). Docs should reflect this change.

Updated changelog entries per Colin's note.