Bug #56292 Deadlock with ALTER TABLE and MERGE tables
Submitted: 26 Aug 2010 14:37 Modified: 20 Nov 2010 23:01
Reporter: Jon Olav Hauglid Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Locking Severity:S3 (Non-critical)
Version:5.5 OS:Any
Assigned to: Jon Olav Hauglid CPU Architecture:Any
Triage: Triaged: D2 (Serious)

[26 Aug 2010 14:37] Jon Olav Hauglid
Description:
ALTER TABLE on a MERGE table can cause deadlock with two
other connections if we reach a situation where:

1) A connection doing ALTER TABLE can't upgrade to MDL_EXCLUSIVE on the merge
table, but holds TL_READ_NO_INSERT on the source tables.
2) A connection doing DELETE on a source table can't get TL_WRITE on it
since ALTER TABLE holds TL_READ_NO_INSERT.
3) A connection doing SELECT on the merge table can't get TL_READ on 
the source tables since TL_WRITE is ahead in the lock queue, but holds MDL_SHARED_READ on the merge table preventing ALTER TABLE from upgrading.

How to repeat:
--source include/have_debug_sync.inc

CREATE TABLE t1(a INT) engine=MyISAM;
CREATE TABLE t2(a INT) engine=MyISAM;
CREATE TABLE m1(a INT) engine=MERGE UNION=(t1, t2);

INSERT INTO t1 VALUES (1), (2);
INSERT INTO t2 VALUES (3), (4);

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

connection con1;
SET DEBUG_SYNC= 'mdl_upgrade_shared_lock_to_exclusive SIGNAL upgrade WAIT_FOR continue';
--send ALTER TABLE m1 engine=MERGE UNION=(t2, t1)

connection con2;
SET DEBUG_SYNC= 'now WAIT_FOR upgrade';
--send DELETE FROM t2 WHERE a = 3

connection con3;
--sleep 2
--send SELECT * FROM m1

connection default;
--sleep 2
SET DEBUG_SYNC= 'now SIGNAL continue';

connection con1;
--reap
connection con2;
--reap
connection con3;
--reap
connection default;
DROP TABLE m1, t1, t2;
[30 Aug 2010 10:59] 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/117130

3124 Jon Olav Hauglid	2010-08-30
      Bug #56292 Deadlock with ALTER TABLE and MERGE tables
      
      ALTER TABLE on a MERGE table could cause a deadlock with two
      other connections if we reached a situation where:
      
      1) A connection doing ALTER TABLE can't upgrade to MDL_EXCLUSIVE on the
      parent table, but holds TL_READ_NO_INSERT on the child tables.
      2) A connection doing DELETE on a child table can't get TL_WRITE on it
      since ALTER TABLE holds TL_READ_NO_INSERT.
      3) A connection doing SELECT on the parent table can't get TL_READ on 
      the child tables since TL_WRITE is ahead in the lock queue, but holds
      MDL_SHARED_READ on the parent table preventing ALTER TABLE from upgrading.
      
      For regular tables, this deadlock is avoided by having ALTER TABLE
      take a MDL_SHARED_NO_WRITE metadata lock on the table. This prevents
      DELETE from acquiring MDL_SHARED_WRITE on the table before ALTER TABLE
      tries to upgrade to MDL_EXCLUSIVE. In the example above, SELECT would
      therefore not be blocked by the pending DELETE as DELETE would not be
      able to enter TL_WRITE in the table lock queue.
      
      This patch fixes the problem for merge tables by using the same metadata
      lock type for child tables as for the parent table. The child tables will
      in this case therefore be locked with MDL_SHARED_NO_WRITE, preventing
      DELETE from acquiring a metadata lock and enter into the table lock queue.
      
      Change in behavior: By taking the same metadata lock for child tables
      as for the parent table, LOCK TABLE on the parent table will now also
      implicitly lock the child tables. Merge.test/.result has been updated
      to reflect this change.
      
      Test case added to mdl_sync.test.
[8 Sep 2010 8:28] 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/117756

3133 Jon Olav Hauglid	2010-09-08
      Bug #56292 Deadlock with ALTER TABLE and MERGE tables
      
      ALTER TABLE on a MERGE table could cause a deadlock with two
      other connections if we reached a situation where:
      
      1) A connection doing ALTER TABLE can't upgrade to MDL_EXCLUSIVE on the
      parent table, but holds TL_READ_NO_INSERT on the child tables.
      2) A connection doing DELETE on a child table can't get TL_WRITE on it
      since ALTER TABLE holds TL_READ_NO_INSERT.
      3) A connection doing SELECT on the parent table can't get TL_READ on 
      the child tables since TL_WRITE is ahead in the lock queue, but holds
      MDL_SHARED_READ on the parent table preventing ALTER TABLE from upgrading.
      
      For regular tables, this deadlock is avoided by having ALTER TABLE
      take a MDL_SHARED_NO_WRITE metadata lock on the table. This prevents
      DELETE from acquiring MDL_SHARED_WRITE on the table before ALTER TABLE
      tries to upgrade to MDL_EXCLUSIVE. In the example above, SELECT would
      therefore not be blocked by the pending DELETE as DELETE would not be
      able to enter TL_WRITE in the table lock queue.
      
      This patch fixes the problem for merge tables by using the same metadata
      lock type for child tables as for the parent table. The child tables will
      in this case therefore be locked with MDL_SHARED_NO_WRITE, preventing
      DELETE from acquiring a metadata lock and enter into the table lock queue.
      
      Change in behavior: By taking the same metadata lock for child tables
      as for the parent table, LOCK TABLE on the parent table will now also
      implicitly lock the child tables. Since LOCK TABLE on the parent table
      now takes more than one metadata lock, it is possible for LOCK TABLE
      ... WRITE on the parent table or child tables to give ER_LOCK_DEADLOCK
      error.
      
      Test case added to mdl_sync.test.
      Merge.test/.result has been updated to reflect the change to LOCK TABLE.
[8 Sep 2010 8:30] Jon Olav Hauglid
Pushed to mysql-5.5-runtime (5.5.7-m3).
[4 Nov 2010 1:36] Paul Dubois
Noted in 5.5.7 changelog.

ALTER TABLE on a MERGE table could result in deadlock with other
connections.
[9 Nov 2010 19:45] Bugs System
Pushed into mysql-5.5 5.5.7-rc (revid:sunanda.menon@sun.com-20101109182959-otkxq8vo2dcd13la) (version source revid:sunanda.menon@sun.com-20101109182959-otkxq8vo2dcd13la) (merge vers: 5.5.7-rc) (pib:21)
[13 Nov 2010 16:12] Bugs System
Pushed into mysql-trunk 5.6.99-m5 (revid:alexander.nozdrin@oracle.com-20101113155825-czmva9kg4n31anmu) (version source revid:alexander.nozdrin@oracle.com-20101113152450-2zzcm50e7i4j35v7) (merge vers: 5.6.1-m4) (pib:21)
[13 Nov 2010 16:33] Bugs System
Pushed into mysql-next-mr (revid:alexander.nozdrin@oracle.com-20101113160336-atmtmfb3mzm4pz4i) (version source revid:vasil.dimov@oracle.com-20100629074804-359l9m9gniauxr94) (pib:21)