MySQL Lists are EOL. Please join:

List:Commits« Previous MessageNext Message »
From:Jon Olav Hauglid Date:February 4 2010 9:00am
Subject:bzr commit into mysql-5.5-next-mr branch (jon.hauglid:3073) Bug#50821
View as plain text  
#At file:///export/home/z/mysql-next-4284-bug50821/ based on revid:dlenev@stripped

 3073 Jon Olav Hauglid	2010-02-04
      Bug #50821 Deadlock between LOCK TABLES and ALTER TABLE
      
      This was a deadlock between ALTER TABLE and another DML statement
      (or LOCK TABLES ... READ). ALTER TABLE would wait trying to upgrade
      its lock to MDL_EXCLUSIVE and the DML statement would wait trying
      to acquire a TL_READ_NO_INSERT table level lock.
      
      This could happen if one connection first acquired a MDL_SHARED_READ
      lock on a table. In another connection ALTER TABLE is then started.
      ALTER TABLE eventually blocks trying to upgrade to MDL_EXCLUSIVE,
      but while holding a TL_WRITE_ALLOW_READ table level lock.
      
      If the first connection then tries to acquire TL_READ_NO_INSERT,
      it will block and we have a deadlock since neither connection can
      proceed.
      
      This patch fixes the problem by allowing TL_READ_NO_INSERT 
      locks to be granted if another connection holds TL_WRITE_ALLOW_READ
      on the same table. This will allow the DML statement to proceed
      such that it eventually can release its MDL lock which in turn
      makes ALTER TABLE able to proceed.
      
      Note that TL_READ_NO_INSERT was already partially compatible with
      TL_WRITE_ALLOW_READ as the latter would be granted if the former
      lock was held. This patch just makes the opposite true as well.
      
      Also note that since ALTER TABLE takes an upgradable MDL lock,
      there will be no starvation of ALTER TABLE statements by
      statements acquiring TL_READ or TL_READ_NO_INSERT.
      
      Test case added to lock_sync.test.

    modified:
      mysql-test/r/lock_sync.result
      mysql-test/t/lock_sync.test
      mysys/thr_lock.c
=== modified file 'mysql-test/r/lock_sync.result'
--- a/mysql-test/r/lock_sync.result	2010-02-01 11:43:06 +0000
+++ b/mysql-test/r/lock_sync.result	2010-02-04 09:00:36 +0000
@@ -60,6 +60,8 @@ release_lock("lock_bug45143_wait")
 1
 # Switch to connection 'con_bug45143_1'.
 # Reap INSERT statement.
+Warnings:
+Note	1592	Statement may not be safe to log in statement format.
 # Switch to connection 'con_bug45143_3'.
 # Reap LOCK TABLES statement.
 unlock tables;
@@ -69,3 +71,23 @@ set debug_sync= 'RESET';
 set @@global.general_log= @old_general_log;
 drop view v1;
 drop table t1;
+#
+# Bug#50821 Deadlock between LOCK TABLES and ALTER TABLE
+#
+DROP TABLE IF EXISTS t1, t2;
+CREATE TABLE t1(id INT);
+CREATE TABLE t2(id INT);
+# Connection con2
+START TRANSACTION;
+SELECT * FROM t1;
+id
+# Connection default
+# Sending:
+ALTER TABLE t1 ADD COLUMN j INT;
+# Connection con2
+# This used to cause a deadlock.
+INSERT INTO t2 SELECT * FROM t1;
+COMMIT;
+# Connection default
+# Reaping ALTER TABLE t1 ADD COLUMN j INT
+DROP TABLE t1, t2;

=== modified file 'mysql-test/t/lock_sync.test'
--- a/mysql-test/t/lock_sync.test	2010-02-01 11:43:06 +0000
+++ b/mysql-test/t/lock_sync.test	2010-02-04 09:00:36 +0000
@@ -4,6 +4,10 @@
 --source include/have_debug_sync.inc
 # We need InnoDB to be able use TL_WRITE_ALLOW_WRITE type of locks in our tests.
 --source include/have_innodb.inc
+# The test for Bug#50821 requires binary logging turned on.
+# With binary logging on, sub-queries in DML statements acquire
+# TL_READ_NO_INSERT which was needed to reproduce this deadlock bug.
+--source include/have_log_bin.inc
 # Until bug#41971 'Thread state on embedded server is always "Writing to net"'
 # is fixed this test can't be run on embedded version of server.
 --source include/not_embedded.inc
@@ -118,6 +122,49 @@ drop view v1;
 drop table t1;
 
 
+--echo #
+--echo # Bug#50821 Deadlock between LOCK TABLES and ALTER TABLE
+--echo #
+
+--disable_warnings
+DROP TABLE IF EXISTS t1, t2;
+--enable_warnings
+
+CREATE TABLE t1(id INT);
+CREATE TABLE t2(id INT);
+
+--echo # Connection con2
+connect (con2, localhost, root);
+START TRANSACTION;
+SELECT * FROM t1;
+
+--echo # Connection default
+connection default;
+--echo # Sending:
+--send ALTER TABLE t1 ADD COLUMN j INT
+
+--echo # Connection con2
+connection con2;
+let $wait_condition=
+  SELECT COUNT(*) = 1 FROM information_schema.processlist
+  WHERE state = "Waiting for table" 
+  AND info = "ALTER TABLE t1 ADD COLUMN j INT";
+--source include/wait_condition.inc
+
+--echo # This used to cause a deadlock.
+INSERT INTO t2 SELECT * FROM t1;
+
+COMMIT;
+
+--echo # Connection default
+connection default;
+--echo # Reaping ALTER TABLE t1 ADD COLUMN j INT
+--reap
+
+DROP TABLE t1, t2;
+disconnect con2;
+
+
 # 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

=== modified file 'mysys/thr_lock.c'
--- a/mysys/thr_lock.c	2010-02-01 23:22:16 +0000
+++ b/mysys/thr_lock.c	2010-02-04 09:00:36 +0000
@@ -549,7 +549,7 @@ thr_lock(THR_LOCK_DATA *data, THR_LOCK_O
            Request
           /-------
          H|++++  WRITE_ALLOW_WRITE
-         e|+++-  WRITE_ALLOW_READ
+         e|++++  WRITE_ALLOW_READ
          l|+++-  WRITE_CONCURRENT_INSERT
          d|++++  WRITE_DELAYED
            ||||
@@ -572,8 +572,7 @@ thr_lock(THR_LOCK_DATA *data, THR_LOCK_O
       if (thr_lock_owner_equal(data->owner, lock->write.data->owner) ||
 	  (lock->write.data->type <= TL_WRITE_DELAYED &&
 	   (((int) lock_type <= (int) TL_READ_HIGH_PRIORITY) ||
-	    (lock->write.data->type != TL_WRITE_CONCURRENT_INSERT &&
-	     lock->write.data->type != TL_WRITE_ALLOW_READ))))
+	    (lock->write.data->type != TL_WRITE_CONCURRENT_INSERT))))
       {						/* Already got a write lock */
 	(*lock->read.last)=data;		/* Add to running FIFO */
 	data->prev=lock->read.last;


Attachment: [text/bzr-bundle] bzr/jon.hauglid@sun.com-20100204090036-bu3x1qwj4wjg3xd6.bundle
Thread
bzr commit into mysql-5.5-next-mr branch (jon.hauglid:3073) Bug#50821Jon Olav Hauglid4 Feb
  • Re: bzr commit into mysql-5.5-next-mr branch (jon.hauglid:3073)Bug#50821Dmitry Lenev4 Feb