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
Triage: Triaged: D3 (Medium)

[25 Mar 2009 22:10] Davi Arnaut
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.
[2 Jun 2009 17:42] Mattias Jonsson
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?
[10 Jul 2009 9:40] Sveta Smirnova
Bug does not exists in version 5.1
[16 Jul 2009 9:27] Alexander Nozdrin
Bug#40973 seems to be a duplicate of this one.
[10 Aug 2009 9:20] Mattias Jonsson
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.)
[20 Aug 2009 10:44] Konstantin Osipov
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.
[2 Sep 2009 7:49] Jon Olav Hauglid
Related to bug#46654
[22 Sep 2009 8:52] 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/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.
[22 Sep 2009 9:48] Jon Olav Hauglid
Pushed to mysql-6.0-codebase-bugfixing (version 6.0.14-alpha).
[30 Sep 2009 8:16] 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 6:22] Jon Stephens
Documented bugfix in the 6.0.14 changelog as follows:

      ALTER TABLE on a partitioned table caused unnecessary deadlocks.

Closed.
[1 Oct 2009 9:16] Jon Stephens
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
[4 Nov 2009 12:00] 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/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.
[4 Nov 2009 12:23] Jon Olav Hauglid
Pushed into mysql-next-mr-runtime (version 5.6.0)
[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:56] 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)
[25 Nov 2009 15:55] Paul Dubois
Noted in 5.6.0 changelog.

Already fixed in 6.0.x.
[10 Dec 2009 13: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/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.
[16 Feb 2010 16:50] 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 17:00] Bugs System
Pushed into mysql-next-mr (revid:alik@sun.com-20100216101208-33qkfwdr0tep3pf2) (version source revid:kostja@sun.com-20091211103945-198h3pt8w7ypk20u) (pib:16)
[16 Feb 2010 19:30] Jon Stephens
Re-opened by push involving tests only; no new changelog entry needed. Re-closing.
[6 Mar 2010 10:55] 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 23:57] Paul Dubois
Moved 5.6.0 changelog entry to 5.5.3.