MySQL Bugs: #23713: LOCK TABLES + CREATE TRIGGER + FLUSH TABLES WITH READ LOCK = deadlock

Bug #23713	LOCK TABLES + CREATE TRIGGER + FLUSH TABLES WITH READ LOCK = deadlock
Submitted:	27 Oct 2006 12:25	Modified:	18 Dec 2007 21:45
Reporter:	Dmitry Lenev	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Stored Routines	Severity:	S3 (Non-critical)
Version:	5.0.27-bk	OS:	Any
Assigned to:	Davi Arnaut	CPU Architecture:	Any

Description:
Execution of CREATE TRIGGER statement in connection which has some tables locked with LOCK TABLES may cause deadlock if in another connection one issues FLUSH TABLES WITH READ LOCK.

How to repeat:
# Here is the script for mysqltest demonstrating the problem
create table t1 (i int);
connect (conn1,localhost,root,,test,,);
connect (conn2,localhost,root,,test,,);
connection conn1;
lock tables t1 write;
connection conn2;
--send flush tables with read lock;
--sleep 10
connection conn1;
# deadlock !!!
create trigger t1_bi before insert on t1 for each row begin end;
unlock tables;
connection conn2;
--reap
select * from t1;

Note that DROP TABLE is also affected (i.e. the same problem will arise if one will use DROP TABLE instead of CREATE TRIGGER).

Thank you for the report.

Verified as described.

Hello, 

I'd like to know an ETA of when the patch will make it into a production release. I'm working on an app which requires LOCK TABLES + CREATE/DROP TRIGGER. 

Thanks!

Apparently it's not just triggers which cause that deadlock. my backup system gets into a state where "flush tables with read lock" just doesn't complete, but the other threads are not doign anything except insert/update/delete (plus maybe a temporary table thrown in).

Thus the patch as written seems incomplete.

Putting bug to verified because:
 * the patch didn't pass code review
 * it is not "In progress"

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/38818

ChangeSet@1.2671, 2007-11-29 09:42:26-02:00, davi@mysql.com +10 -0
  Bug#23713 LOCK TABLES + CREATE TRIGGER + FLUSH TABLES WITH READ LOCK = deadlock
  
  This bug is actually two bugs in one, one of which is CREATE TRIGGER under
  LOCK TABLES and the other is CREATE TRIGGER under LOCK TABLES simultaneous
  to a FLUSH TABLES WITH READ LOCK (global read lock). Both situations could
  lead to a server crash or deadlock.
  
  The first problem arises from the fact that when under LOCK TABLES, if the
  table is in the set of locked tables, the table is already open and it doesn't
  need to be reopened (not a placeholder). Also in this case, if the table is
  not write locked, a exclusive lock can't be acquired because of a possible
  deadlock with another thread also holding a (read) lock on the table. The
  second issue arises from the fact that one should never wait for a global
  read lock if it's holding any locked tables, because the global read lock
  is waiting for these tables and this leads to a circular wait deadlock.
  
  The solution for the first case is to check if the table is write locked
  and upgraded the write lock to a exclusive lock and fail otherwise for non
  write locked tables. Grabbin the exclusive lock in this case also means
  to ensure that the table is opened only by the calling thread. The second
  issue is partly fixed by not waiting for the global read lock if the thread
  is holding any locked tables.
  
  The second issue is only partly addressed in this patch because it turned
  out to be much wider and also affects other DDL statements. Reported as
  Bug#32395

Pushed into 5.1.23-rc

Pushed into 6.0.5-alpha

Noted in 5.1.23, 6.0.5 changelogs.

A CREATE TRIGGER statement could cause a deadlock or server crash
if it referred to a table for which a table lock had been acquired with
LOCK TABLES.