bzr commit into mysql-6.0-bugfixing branch (jon.hauglid:2830) Bug#43867

[Jon Olav Hauglid]

#At file:///export/home/z/mysql-6.0-codebase-bugfixing-bug43867/ based on revid:tor.didriksen@stripped

 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.

    added:
      mysql-test/r/partition_sync.result
      mysql-test/t/partition_sync.test
=== added file 'mysql-test/r/partition_sync.result'
--- a/mysql-test/r/partition_sync.result	1970-01-01 00:00:00 +0000
+++ b/mysql-test/r/partition_sync.result	2009-09-22 08:52:30 +0000
@@ -0,0 +1,25 @@
+#
+# Bug #43867 ALTER TABLE on a partitioned table 
+#            causes unnecessary deadlocks
+#
+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 2
+BEGIN;
+SELECT * FROM t1;
+a
+0
+1
+# Connection 1
+ALTER TABLE t1 DROP PARTITION p3;
+ERROR HY000: Error in list of partitions to DROP
+# Connection 2
+# This failed with deadlock and should not do so.
+SELECT * FROM t1;
+a
+0
+1
+# Connection 1
+DROP TABLE t1;

=== added file 'mysql-test/t/partition_sync.test'
--- a/mysql-test/t/partition_sync.test	1970-01-01 00:00:00 +0000
+++ b/mysql-test/t/partition_sync.test	2009-09-22 08:52:30 +0000
@@ -0,0 +1,41 @@
+--source include/have_partition.inc
+# Save the initial number of concurrent sessions.
+--source include/count_sessions.inc
+
+--echo #
+--echo # Bug #43867 ALTER TABLE on a partitioned table 
+--echo #            causes unnecessary deadlocks
+--echo #
+
+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);
+
+connect(con1,localhost,root);
+
+--echo # Connection 2
+connection con1;
+BEGIN;
+SELECT * FROM t1;
+
+--echo # Connection 1
+connection default;
+--error ER_DROP_PARTITION_NON_EXISTENT
+ALTER TABLE t1 DROP PARTITION p3;
+
+--echo # Connection 2
+connection con1;
+--echo # This failed with deadlock and should not do so.
+SELECT * FROM t1;
+
+--echo # Connection 1
+connection default;
+disconnect con1;
+DROP TABLE t1;
+
+
+# Check that all connections opened by test cases in this file are really
+# gone so execution of other tests won't be affected by their presence.
+--source include/wait_until_count_sessions.inc


Attachment: [text/bzr-bundle] bzr/[e-mail]