Bug #46654 False deadlock on concurrent DML/DDL with partitions, inconsistent behavior
Submitted: 11 Aug 2009 15:15 Modified: 7 Mar 2010 1:58
Reporter: Elena Stepanova Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Locking Severity:S3 (Non-critical)
Version:5.4.4-alpha OS:Any
Assigned to: Jon Olav Hauglid CPU Architecture:Any
Tags: regression
Triage: Triaged: D2 (Serious)

[11 Aug 2009 15:15] Elena Stepanova
Description:
If one connection is running a multi-statement transaction which involves a single partitioned table, and another connection attempts to alter the table, the first connection gets ER_LOCK_DEADLOCK and cannot proceed anymore, even when the DDL statement in another connection is timed out (which happens after innodb_lock_wait_timeout seconds).

If the same ALTER statement is issued in the second connection again, it does not get timed out at all (see last lines in 'How to repeat' test case).

Behaviour for the same test case, but on a table without partitions, is different: there is no deadlock detection at all, but the deadlock between DML and DDL seems to exist, as the DML connection waits for DDL to time out.

Summary for the provided test case:

With partitions:
- INSERT 1: OK
- ALTER 1: waits until timed out
- INSERT 2 (issued while ALTER 1 is waiting): Deadlock
- INSERT 3 (issued after ALTER 2 is timed out): Deadlock
- ALTER 2: hangs until transaction in connection 1 is ended

Without partitions:
- INSERT 1: OK
- ALTER 1: waits until timed out
- INSERT 2 (issued while ALTER 1 is waiting): waits until ALTER in connection 2 is timed out
- INSERT 3 (issued after ALTER 2 is timed out): OK
- ALTER 2: waits until timed out

How to repeat:
t/ddl_dml_deadlock.test:

#--------------------------------------------

USE test;

--source include/have_innodb.inc
--source include/have_partition.inc

--disable_warnings
DROP TABLE IF EXISTS tbl_with_partitions;
--enable_warnings
CREATE TABLE tbl_with_partitions ( i INT ) 
	ENGINE = InnoDB 
	PARTITION BY HASH(i);

connect(ddl,localhost,root,,test,,);
	
connection default;

--echo # In connection 1 we insert into the table
--echo # with disabled autocommit

SET AUTOCOMMIT = 0;
INSERT INTO tbl_with_partitions VALUES (1);

connection ddl;

--echo # In connection 2 we try to alter the table
--echo # the query waits

send ALTER TABLE tbl_with_partitions ADD COLUMN f INT;

connection default;

--echo # Now in connection 1 we try to insert into the same table again 

--error 0,ER_LOCK_DEADLOCK
INSERT INTO tbl_with_partitions VALUES (2);
if ($mysql_errno)
{
	--echo # We are getting error $mysql_errno trying to insert into the table
}

--echo # Now we will wait until ALTER times out (this time it should)

connection ddl;

--error 0,ER_LOCK_WAIT_TIMEOUT
reap;

connection default;

--error 0,ER_LOCK_DEADLOCK
INSERT INTO tbl_with_partitions VALUES (3);
if ($mysql_errno)
{
	--echo # ALTER timed out, but we are still getting error $mysql_errno 
	--echo # trying to insert into the table in connection 1
}

# This part is for the 2nd problem:
# further attempt to perform the same ALTER is not timed out at all
# If these lines are disabled, the test will hang 

#connection ddl;
#--disable_abort_on_error
#ALTER TABLE tbl_with_partitions ADD COLUMN f INT;

#--------------------------------------------

perl ./mysql-test-run.pl --mysqld=--innodb_lock_wait_timeout=3 ddl_dml_deadlock
[11 Aug 2009 15:25] Elena Stepanova
For 5.1.37, the behaviour with and without partitions is the same, and looks identical to what happens without partitions on 5.4.4.
[12 Aug 2009 2:11] Elena Stepanova
Looks similar to bug#46272, but not quite sure whether it's a duplicate due to the different behavior for partitioned tables vs non-partitioned, which the other bug does not mention.
[25 Aug 2009 16:52] Konstantin Osipov
The fix is to remove tdc_remove_table in sql_partition from two places where they are unnecessary.
[27 Aug 2009 9:25] Jon Olav Hauglid
Using the test case in the bug description, the only call to 
tdc_remove_table() comes from the initial "DROP TABLE IF EXISTS tbl_with_partitions";

The two deadlocks are reported from Open_table_context::request_backoff_action() with identical backtraces to the deadlock in Bug#46272.
[27 Aug 2009 11:19] Jon Olav Hauglid
ALTER TABLE calls prep_alter_part_table() in sql_partition.cc which sets TABLE_SHARE's version = 0. This happens before ALTER TABLE starts waiting for the shared lock to disappear inside upgrade_shared_lock_to_exclusive().

The following INSERT will notice that share->version now is different from 
refresh_version and call Open_table_context::request_backoff_action() which triggers the DEADLOCK error.
[17 Sep 2009 7:31] 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/83557

2822 Jon Olav Hauglid	2009-09-17
      Bug #46654 False deadlock on concurrent DML/DDL with partitions, 
                 inconsistent behavior
      
      This is a preliminary version of the patch.
      
      The problem was that if one connection is running a multi-statement transaction 
      which involves a single partitioned table, and another connection attempts to 
      alter the table, the first connection gets ER_LOCK_DEADLOCK and cannot proceed 
      anymore, even when the ALTER TABLE statement in another connection is timed out 
      (which happens after innodb_lock_wait_timeout seconds).
      
      The reason for this was that the prepare phase for ALTER TABLE for partitioned
      tables removed the table from the table definition cache before it started
      waiting on the lock. The transaction running in the first connection would
      notice this and report ER_LOCK_DEADLOCK. The removal was unnecessary as it
      also is done at a later point when the lock has been taken.
      
      This patch changes the ALTER TABLE code so that tdc_remove_table() is only 
      called when the prepare phase fails.
      
      Test case added in bug46654.test. The test will be merged into an existing
      file before the final commit/push.
[22 Sep 2009 8:31] 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/84057

2830 Jon Olav Hauglid	2009-09-22
      Bug #46654 False deadlock on concurrent DML/DDL with partitions, 
                 inconsistent behavior
      
      The problem was that if one connection is running a multi-statement 
      transaction which involves a single partitioned table, and another 
      connection attempts to alter the table, the first connection gets 
      ER_LOCK_DEADLOCK and cannot proceed anymore, even when the ALTER TABLE 
      statement in another connection has timed out or failed.
      
      The reason for this was that the prepare phase for ALTER TABLE for 
      partitioned tables removed all instances of the table from the table 
      definition cache before it started waiting on the lock. The transaction 
      running in the first connection would notice this and report ER_LOCK_DEADLOCK. 
      
      This patch changes the prep_alter_part_table() ALTER TABLE code so that 
      tdc_remove_table() is no longer called. Instead, only the TABLE instance
      changed by prep_alter_part_table() is marked as needing reopen.
      
      The patch also removes an unnecessary call to tdc_remove_table() from 
      mysql_unpack_partition() as the changed TABLE object is destroyed by the 
      caller at a later point.
      
      Test case added in partition_sync.test.
[22 Sep 2009 11:45] 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/84112

2832 Jon Olav Hauglid	2009-09-22
      Bug #46654 False deadlock on concurrent DML/DDL with partitions, 
                 inconsistent behavior
      
      The problem was that if one connection is running a multi-statement 
      transaction which involves a single partitioned table, and another 
      connection attempts to alter the table, the first connection gets 
      ER_LOCK_DEADLOCK and cannot proceed anymore, even when the ALTER TABLE 
      statement in another connection has timed out or failed.
      
      The reason for this was that the prepare phase for ALTER TABLE for 
      partitioned tables removed all instances of the table from the table 
      definition cache before it started waiting on the lock. The transaction 
      running in the first connection would notice this and report ER_LOCK_DEADLOCK. 
      
      This patch changes the prep_alter_part_table() ALTER TABLE code so that 
      tdc_remove_table() is no longer called. Instead, only the TABLE instance
      changed by prep_alter_part_table() is marked as needing reopen.
      
      The patch also removes an unnecessary call to tdc_remove_table() from 
      mysql_unpack_partition() as the changed TABLE object is destroyed by the 
      caller at a later point.
      
      Test case added in partition_sync.test.
[22 Sep 2009 11:46] Jon Olav Hauglid
Pushed to mysql-6.0-codebase-bugfixing (version 6.0.14-alpha).
[23 Sep 2009 6:40] 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/84242

2834 Jon Olav Hauglid	2009-09-23
      Followup to Bug#46654 False deadlock on concurrent DML/DDL with partitions, 
                            inconsistent behavior
      
      Partition_sync.test uses features only available in debug builds.
      Disabling the test for non-debug builds.
[30 Sep 2009 8:17] Bugs System
Pushed into 6.0.14-alpha (revid:alik@sun.com-20090929093622-1mooerbh12e97zux) (version source revid:alik@sun.com-20090923103200-kyo2bakdo6tfb2fb) (merge vers: 6.0.14-alpha) (pib:11)
[1 Oct 2009 0:08] Paul Dubois
Noted in 6.0.14 changelog.

Deadlock occurred if one session was running a multiple-statement
transaction that involved a single partitioned table and another 
session attempted to alter the table.

Setting report to NDI pending push into 5.4.x.
[20 Nov 2009 12:54] Bugs System
Pushed into 5.6.0-beta (revid:davi.arnaut@sun.com-20091119234808-xbjpkwaxjt5x5c0b) (version source revid:jon.hauglid@sun.com-20090928163426-2lg1gofzz44xzzxf) (merge vers: 6.0.14-alpha) (pib:13)
[20 Nov 2009 12:57] Bugs System
Pushed into 6.0.14-alpha (revid:kostja@sun.com-20091120124947-yi6h2jbgw0kbciwm) (version source revid:jon.hauglid@sun.com-20090928163426-2lg1gofzz44xzzxf) (merge vers: 6.0.14-alpha) (pib:13)
[20 Nov 2009 23:41] Paul Dubois
Noted in 5.6.0 changelog.
[10 Dec 2009 13:42] Jon Olav Hauglid
Pushed to mysql-next-4284 (5.6.0-beta).
[16 Jan 2010 23:12] Mattias Jonsson
This also affects 5.1, see bug#49881 (I closed that as a duplicate of this).

Is it possible to backport this to 5.1?
[21 Jan 2010 10:39] Jon Olav Hauglid
The patch depends on several other changesets that are not in 5.1, 
so a backport would not be straight-forward.
[21 Jan 2010 19:38] Nicolas Brousse
What about a port to 5.5 ?
[22 Jan 2010 13:16] Jon Olav Hauglid
Yes, this fix will be part of a 5.5 release.
[16 Feb 2010 16:44] Bugs System
Pushed into 6.0.14-alpha (revid:alik@sun.com-20100216101445-2ofzkh48aq2e0e8o) (version source revid:kostja@sun.com-20091211154405-c9yhiewr9o5d20rq) (merge vers: 6.0.14-alpha) (pib:16)
[16 Feb 2010 16:54] Bugs System
Pushed into mysql-next-mr (revid:alik@sun.com-20100216101208-33qkfwdr0tep3pf2) (version source revid:kostja@sun.com-20091211103945-198h3pt8w7ypk20u) (pib:16)
[17 Feb 2010 0:55] Paul Dubois
Setting report to Need Merge pending push of Celosia into release tree.
[6 Mar 2010 10:54] Bugs System
Pushed into 5.5.3-m3 (revid:alik@sun.com-20100306103849-hha31z2enhh7jwt3) (version source revid:vvaintroub@mysql.com-20100216221947-luyhph0txl2c5tc8) (merge vers: 5.5.99-m3) (pib:16)
[7 Mar 2010 1:58] Paul Dubois
Moved 5.6.0 changelog entry to 5.5.3