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

[27 Oct 2006 12:25] Dmitry Lenev
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;
[27 Oct 2006 12:55] Dmitry Lenev
Note that DROP TABLE is also affected (i.e. the same problem will arise if one will use DROP TABLE instead of CREATE TRIGGER).
[27 Oct 2006 20:40] Sveta Smirnova
Thank you for the report.

Verified as described.
[6 Apr 2007 11:50] Sorin Stoiana
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!
[25 Jun 2007 6:34] Matthias Urlichs
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.
[11 Aug 2007 12:53] Konstantin Osipov
Putting bug to verified because:
 * the patch didn't pass code review
 * it is not "In progress"
[29 Nov 2007 11:42] 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/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
[6 Dec 2007 10:00] Bugs System
Pushed into 5.1.23-rc
[6 Dec 2007 10:02] Bugs System
Pushed into 6.0.5-alpha
[18 Dec 2007 21:45] Paul Dubois
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.