List:Commits« Previous MessageNext Message »
From:Jon Olav Hauglid Date:August 20 2010 2:54pm
Subject:bzr commit into mysql-5.5-bugfixing branch (jon.hauglid:3117) Bug#54332
View as plain text  
#At file:///export/home/x/mysql-5.5-runtime-bug54332/ based on revid:jon.hauglid@stripped

 3117 Jon Olav Hauglid	2010-08-20
      Bug #54332 Deadlock with two connections doing LOCK TABLE+INSERT DELAYED
      
      The problem was that deadlocks involving INSERT DELAYED were not detected.
      
      The reason for this is that two threads are involved in INSERT DELAYED:
      the connection thread and the handler thread. The connection thread would
      wait while the handler thread acquired locks and opened the table.
      In essence, this adds an edge to the wait-for-graph between the 
      connection thread and the handler thread that the deadlock detector is
      unaware of. Therefore any deadlocks involving INSERT DELAYED were not 
      detected.
      
      This patch fixes the problem by having the connection thread lock on the
      table before starting the handler thread. This allows the deadlock detector
      to detect any possible deadlocks resulting from trying to lock the table.
      If a lock is successfully acquired, the handler thread is started and
      given a copy of the ticket representing the lock. The connection thread
      then releases any locks it has taken during the process before continuing.
      When the handler thread then tries to lock and open the table, it will
      find that it already has the lock and therefore not acquire any new locks.
      
      Test cases added to delayed.test.

    modified:
      mysql-test/r/delayed.result
      mysql-test/t/delayed.test
      sql/sql_insert.cc
=== modified file 'mysql-test/r/delayed.result'
--- a/mysql-test/r/delayed.result	2009-12-11 09:39:38 +0000
+++ b/mysql-test/r/delayed.result	2010-08-20 14:53:58 +0000
@@ -345,3 +345,53 @@ CREATE TABLE t1 LIKE t2;
 ERROR 42S01: Table 't1' already exists
 DROP TABLE t2;
 DROP TABLE t1;
+#
+# Bug#54332 Deadlock with two connections doing LOCK TABLE+INSERT DELAYED
+#
+DROP TABLE IF EXISTS t1, t2;
+CREATE TABLE t1 (a INT);
+CREATE TABLE t2 (a INT);
+# Test 1: Using LOCK TABLE
+# Connection con1
+LOCK TABLE t1 WRITE;
+# Connection default
+LOCK TABLE t2 WRITE;
+# Sending:
+INSERT DELAYED INTO t1 VALUES (1);
+# Connection con1
+# Wait until INSERT DELAYED is blocked on table 't1'.
+INSERT DELAYED INTO t2 VALUES (1);
+ERROR 40001: Deadlock found when trying to get lock; try restarting transaction
+UNLOCK TABLES;
+# Connection default
+# Reaping: INSERT DELAYED INTO t1 VALUES (1)
+UNLOCK TABLES;
+# Test 2: Using ALTER TABLE
+START TRANSACTION;
+SELECT * FROM t1 WHERE a=0;
+a
+# Connection con1
+# Sending:
+ALTER TABLE t1 COMMENT 'test';
+# Connection default
+# Wait until ALTER TABLE is blocked on table 't1'.
+INSERT DELAYED INTO t1 VALUES (3);
+ERROR 40001: Deadlock found when trying to get lock; try restarting transaction
+COMMIT;
+# Connection con1
+# Reaping: ALTER TABLE t1 COMMENT 'test'
+# Test 3: Using RENAME TABLE
+# Connection default
+START TRANSACTION;
+INSERT INTO t1 VALUES (4);
+# Connection con1
+# Sending:
+RENAME TABLE t1 to t3;
+# Connection default
+# Wait until RENAME TABLE is blocked on table 't1'.
+INSERT DELAYED INTO t1 VALUES (5);
+COMMIT;
+# Connection con1
+# Reaping: RENAME TABLE t1 to t3
+# Connection default
+DROP TABLE t2, t3;

=== modified file 'mysql-test/t/delayed.test'
--- a/mysql-test/t/delayed.test	2010-08-06 11:29:37 +0000
+++ b/mysql-test/t/delayed.test	2010-08-20 14:53:58 +0000
@@ -388,3 +388,107 @@ CREATE TABLE t1 LIKE t2;
 
 DROP TABLE t2;
 DROP TABLE t1;
+
+
+--echo #
+--echo # Bug#54332 Deadlock with two connections doing LOCK TABLE+INSERT DELAYED
+--echo #
+
+--disable_warnings
+DROP TABLE IF EXISTS t1, t2;
+--enable_warnings
+
+CREATE TABLE t1 (a INT);
+CREATE TABLE t2 (a INT);
+
+--echo # Test 1: Using LOCK TABLE
+
+--echo # Connection con1
+connect (con1, localhost, root);
+LOCK TABLE t1 WRITE;
+
+--echo # Connection default
+connection default;
+LOCK TABLE t2 WRITE;
+--echo # Sending:
+--send INSERT DELAYED INTO t1 VALUES (1)
+
+--echo # Connection con1
+connection con1;
+--echo # Wait until INSERT DELAYED is blocked on table 't1'.
+let $wait_condition=
+  SELECT COUNT(*) = 1 FROM information_schema.processlist
+  WHERE state = "Waiting for table metadata lock"
+  AND info = "INSERT DELAYED INTO t1 VALUES (1)";
+--source include/wait_condition.inc
+--error ER_LOCK_DEADLOCK
+INSERT DELAYED INTO t2 VALUES (1);
+UNLOCK TABLES;
+
+--echo # Connection default
+connection default;
+--echo # Reaping: INSERT DELAYED INTO t1 VALUES (1)
+--reap
+UNLOCK TABLES;
+
+--echo # Test 2: Using ALTER TABLE
+
+START TRANSACTION;
+SELECT * FROM t1 WHERE a=0;
+
+--echo # Connection con1
+connection con1;
+--echo # Sending:
+--send ALTER TABLE t1 COMMENT 'test'
+
+--echo # Connection default
+connection default;
+--echo # Wait until ALTER TABLE is blocked on table 't1'.
+let $wait_condition=
+  SELECT COUNT(*) = 1 FROM information_schema.processlist
+  WHERE state = "Waiting for table metadata lock"
+  AND info = "ALTER TABLE t1 COMMENT 'test'";
+--source include/wait_condition.inc
+--error ER_LOCK_DEADLOCK
+INSERT DELAYED INTO t1 VALUES (3);
+COMMIT;
+
+--echo # Connection con1
+connection con1;
+--echo # Reaping: ALTER TABLE t1 COMMENT 'test'
+--reap
+
+--echo # Test 3: Using RENAME TABLE
+
+--echo # Connection default
+connection default;
+START TRANSACTION;
+INSERT INTO t1 VALUES (4);
+
+--echo # Connection con1
+connection con1;
+--echo # Sending:
+--send RENAME TABLE t1 to t3
+
+--echo # Connection default
+connection default;
+--echo # Wait until RENAME TABLE is blocked on table 't1'.
+let $wait_condition=
+  SELECT COUNT(*) = 1 FROM information_schema.processlist
+  WHERE state = "Waiting for table metadata lock"
+  AND info = "RENAME TABLE t1 to t3";
+--source include/wait_condition.inc
+INSERT DELAYED INTO t1 VALUES (5);
+COMMIT;
+
+--echo # Connection con1
+connection con1;
+--echo # Reaping: RENAME TABLE t1 to t3
+--reap
+
+disconnect con1;
+--source include/wait_until_disconnected.inc
+
+--echo # Connection default
+connection default;
+DROP TABLE t2, t3;

=== modified file 'sql/sql_insert.cc'
--- a/sql/sql_insert.cc	2010-08-20 08:24:32 +0000
+++ b/sql/sql_insert.cc	2010-08-20 14:53:58 +0000
@@ -1999,6 +1999,19 @@ bool delayed_get_table(THD *thd, TABLE_L
     */
     if (delayed_insert_threads >= thd->variables.max_insert_delayed_threads)
       DBUG_RETURN(0);
+
+    /*
+      In order for the deadlock detector to be able to find any deadlocks
+      caused by locking this table, we take the lock inside the connection
+      thread. If this goes ok, the ticket is cloned and added to the list
+      of granted locks held by the handler thread. Finally, we release
+      any locks taken here by the connection thread.
+    */
+    MDL_ticket *mdl_savepoint= thd->mdl_context.mdl_savepoint();
+    if (thd->mdl_context.acquire_lock(&table_list->mdl_request,
+                                      thd->variables.lock_wait_timeout))
+      DBUG_RETURN(0);
+
     thd_proc_info(thd, "Creating delayed handler");
     mysql_mutex_lock(&LOCK_delayed_create);
     /*
@@ -2008,7 +2021,10 @@ bool delayed_get_table(THD *thd, TABLE_L
     if (! (di= find_handler(thd, table_list)))
     {
       if (!(di= new Delayed_insert()))
+      {
+        thd->mdl_context.rollback_to_savepoint(mdl_savepoint);
         goto end_create;
+      }
       mysql_mutex_lock(&LOCK_thread_count);
       thread_count++;
       mysql_mutex_unlock(&LOCK_thread_count);
@@ -2019,6 +2035,7 @@ bool delayed_get_table(THD *thd, TABLE_L
       {
         /* The error is reported */
 	delete di;
+        thd->mdl_context.rollback_to_savepoint(mdl_savepoint);
         goto end_create;
       }
       di->table_list= *table_list;			// Needed to open table
@@ -2027,6 +2044,25 @@ bool delayed_get_table(THD *thd, TABLE_L
       di->table_list.db= di->thd.db;
       di->lock();
       mysql_mutex_lock(&di->mutex);
+
+      /*
+        Clone the ticket representing the lock on the target table for
+        the insert and add it to the list of granted locks held by
+        the handler thread. Then release any locks taken by the
+        connection thread by rolling back to the savepoint taken above.
+      */
+      error= di->thd.mdl_context.clone_ticket(&table_list->mdl_request);
+      thd->mdl_context.rollback_to_savepoint(mdl_savepoint);
+      table_list->mdl_request.ticket= NULL;
+      if (error)
+      {
+        mysql_mutex_unlock(&di->mutex);
+	di->unlock();
+	delete di;
+	my_error(ER_OUT_OF_RESOURCES, MYF(ME_FATALERROR));
+        goto end_create;
+      }
+
       if ((error= mysql_thread_create(key_thread_delayed_insert,
                                       &di->thd.real_id, &connection_attrib,
                                       handle_delayed_insert, (void*) di)))


Attachment: [text/bzr-bundle] bzr/jon.hauglid@oracle.com-20100820145358-r81k0fcsira0jbtz.bundle
Thread
bzr commit into mysql-5.5-bugfixing branch (jon.hauglid:3117) Bug#54332Jon Olav Hauglid20 Aug