MySQL Bugs: #43867: ALTER TABLE on a partitioned table causes unnecessary deadlocks

Bug #43867	ALTER TABLE on a partitioned table causes unnecessary deadlocks
Submitted:	25 Mar 2009 22:10	Modified:	7 Mar 2010 23:57
Reporter:	Davi Arnaut (OCA)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Partitions	Severity:	S2 (Serious)
Version:	6.0-runtime, azalea	OS:	Any
Assigned to:	Jon Olav Hauglid	CPU Architecture:	Any
Tags:	regression

Description:
The method that prepares a ALTER TABLE on a partitioned table forces the flush of the table object by setting the share version to 0 regardless of the outcome of the operation.

Reseting the table version causes problems for transactions holding a shared metadata lock on the table as opening such table would require releasing the shared metadata lock, which is not possible as the transaction needs to guarantee serializability across multiple transactions.

Hence, when a old version of a table is found inside a transaction that already hold locks, it's not possible to follow the back-off deadlock avoidance protocol and a error is returned instead.

Even worse, it does it without holding LOCK_open or a exclusive metadata lock on the table:

1) The setting might be unnoticed (no explicit synchronization)
2) Too aggressive as it leads to unnecessary back-off
3) Doesn't wait for pending transactions to complete

How to repeat:
CREATE TABLE t1 (a int) PARTITION BY RANGE (a)
(PARTITION p0 VALUES LESS THAN (1),
 PARTITION p1 VALUES LESS THAN (2));

INSERT INTO t1 VALUES (0),(1);

connection con1;
BEGIN;
SELECT * FROM t1;
connection default;
--error ER_DROP_PARTITION_NON_EXISTENT
ALTER TABLE t1 DROP PARTITION p3;
connection con1;
SELECT * FROM t1;

The last select fails with:

query 'SELECT * FROM t1' failed: 1213: Deadlock found when trying to get lock; try restarting transaction

Suggested fix:
Only reset the table version if necessary and take a exclusive lock before doing so.

May be connected to bug#40181.

However I can run the test case without any deadlock in the current 6.0-bugteam tree.

Davi, can you re-verify against a current 6.0-bugteam tree?

Bug does not exists in version 5.1

Bug#40973 seems to be a duplicate of this one.

After applying the fix for bug#40181, the status is:
problem 1) no syncronization, Fixed. Now using tdc_remove_table instead.

problem 2) breaks pending transactions on failing alter partition commands. Due to the different alterations of the partitions the table definition is removed from the cache early to ensure that the old definition never is used again. This is done so that it is never missed in any alter partition branch, and therefore before some checks which could discover errors.

problem 3) Does not wait on pending transactions. This could be fixed, so that regardless if the alter partition command succeeds or fails it always waits on pending transactions.

Possible fixes, either wait for transactions on error before closing all tables and break all running transactions or document current behavior (a failing ALTER TABLE t XXX PARTITION will break all pending transactions to return the error message directly.)

Matthias, 
the fix for this bug is to remove the unnecessary tdc_remove_table() from the partitioned code, as I suggested in the review for Bug#40181.
One should not call tdc_remove_table() without an exclusive metadata lock,
and partitioned code does it in two places.

Related to bug#46654

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

2830 Jon Olav Hauglid	2009-09-22
      Bug #43867 ALTER TABLE on a partitioned table causes unnecessary 
                 deadlocks
      
      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 to drop a non-existing partition,
      (which of course will fail), the first connection still gets 
      ER_LOCK_DEADLOCK and cannot proceed anymore.
            
      This bug is no longer reproducable. This has also been tested with the
      patch for Bug#46654 "False deadlock on concurrent DML/DDL with partitions, 
      inconsistent behavior" which concerned a similar problem but where the 
      ALTER TABLE is semantically correct.
      
      Test case added in partition_sync.test.

Pushed to mysql-6.0-codebase-bugfixing (version 6.0.14-alpha).

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)

Documented bugfix in the 6.0.14 changelog as follows:

      ALTER TABLE on a partitioned table caused unnecessary deadlocks.

Closed.

Thank you for your bug report. This issue has been committed to our source repository of that product and will be incorporated into the next release.

If necessary, you can access the source repository and build the latest available version, including the bug fix. More information about accessing the source trees is available at

    http://dev.mysql.com/doc/en/installing-source.html

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

2909 Jon Olav Hauglid	2009-11-04
      Bug #43867 ALTER TABLE on a partitioned table causes unnecessary 
                 deadlocks
      
      Backport of revno: 2617.68.35
      
      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 to drop a non-existing partition,
      (which of course will fail), the first connection still gets 
      ER_LOCK_DEADLOCK and cannot proceed anymore.
      
      This bug is no longer reproducable. This has also been tested with the
      patch for Bug#46654 "False deadlock on concurrent DML/DDL with partitions, 
      inconsistent behavior" which concerned a similar problem but where the 
      ALTER TABLE is semantically correct.
      
      Test case added in partition_sync.test.

Pushed into mysql-next-mr-runtime (version 5.6.0)

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)

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)

Noted in 5.6.0 changelog.

Already fixed in 6.0.x.

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

3028 Jon Olav Hauglid	2009-12-10
      Backport of revno: 2617.80.1
      Also re-enables the test for Bug #43867
      
      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.

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)

Pushed into mysql-next-mr (revid:alik@sun.com-20100216101208-33qkfwdr0tep3pf2) (version source revid:kostja@sun.com-20091211103945-198h3pt8w7ypk20u) (pib:16)

Re-opened by push involving tests only; no new changelog entry needed. Re-closing.

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)

Moved 5.6.0 changelog entry to 5.5.3.