MySQL Lists are EOL. Please join:

List:Commits« Previous MessageNext Message »
From:Konstantin Osipov Date:December 9 2009 3:56pm
Subject:bzr commit into mysql-5.6-next-mr branch (kostja:3015) Bug#46673
View as plain text  
#At file:///opt/local/work/next-4284/ based on revid:kostja@stripped

 3015 Konstantin Osipov	2009-12-09
      Backport of:
      ------------------------------------------------------------
      revno: 2617.68.10
      committer: Dmitry Lenev <dlenev@stripped>
      branch nick: mysql-next-bg46673
      timestamp: Tue 2009-09-01 19:57:05 +0400
      message:
        Fix for bug #46673 "Deadlock between FLUSH TABLES WITH READ LOCK and DML".
      
        Deadlocks occured when one concurrently executed transactions with
        several statements modifying data and FLUSH TABLES WITH READ LOCK
        statement or SET READ_ONLY=1 statement.
      
        These deadlocks were introduced by the patch for WL 4284: "Transactional
        DDL locking"/Bug 989: "If DROP TABLE while there's an active transaction,
        wrong binlog order" which has changed FLUSH TABLES WITH READ LOCK/SET
        READ_ONLY=1 to wait for pending transactions.
        What happened was that FLUSH TABLES WITH READ LOCK blocked all further
        statements changing tables by setting global_read_lock global variable
        and has started waiting for all pending transactions to complete.
        Then one of those transactions tried to executed DML, detected that
        global_read_lock non-zero and tried to wait until global read lock will
        be released (i.e. global_read_lock becomes 0), indeed, this led to a
        deadlock.
      
        Proper solution for this problem should probably involve full integration
        of global read lock with metadata locking subsystem (which will allow to
        implement waiting for pending transactions without blocking DML in them).
        But since it requires significant changes another, short-term solution
        for the problem is implemented in this patch.
      
        Basically, this patch restores behavior of FLUSH TABLES WITH READ LOCK/
        SET READ_ONLY=1 before the patch for WL 4284/bug 989. By ensuring that
        extra references to TABLE_SHARE are not stored for active metadata locks
        it changes these statements not to wait for pending transactions.
        As result deadlock is eliminated.
        Note that this does not change the fact that active FLUSH TABLES WITH
        READ LOCK lock or SET READ_ONLY=1 prevent modifications to tables as
        they also block transaction commits.
     @ mysql-test/r/flush_block_commit.result
        Adjusted test case after change in FLUSH TABLES WITH READ LOCK behavior
        - it is no longer blocked by a pending transaction.
     @ mysql-test/r/mdl_sync.result
        Added test for bug #46673 "Deadlock between FLUSH TABLES WITH READ LOCK
        and DML".
     @ mysql-test/r/read_only_innodb.result
        Adjusted test case after change in SET READ_ONLY behavior - it is no
        longer blocked by a pending transaction.
     @ mysql-test/t/flush_block_commit.test
        Adjusted test case after change in FLUSH TABLES WITH READ LOCK behavior
        - it is no longer blocked by a pending transaction.
     @ mysql-test/t/mdl_sync.test
        Added test for bug #46673 "Deadlock between FLUSH TABLES WITH READ LOCK
        and DML".
     @ mysql-test/t/read_only_innodb.test
        Adjusted test case after change in SET READ_ONLY behavior - it is no
        longer blocked by a pending transaction.
     @ sql/sql_base.cc
        Disable caching of pointers to TABLE_SHARE objects in MDL subsystem.
        This means that transactions holding metadata lock on the table will
        no longer have extra reference to the TABLE_SHARE (due to this lock)
        and will no longer block concurrent FLUSH TABLES/FLUSH TABLES WITH
        READ LOCK. Note that this does not change the fact that FLUSH TABLES
        WITH READ LOCK prevents concurrent transactions from modifying data
        as it also blocks all commits.

    modified:
      mysql-test/r/flush_block_commit.result
      mysql-test/r/mdl_sync.result
      mysql-test/r/read_only_innodb.result
      mysql-test/t/flush_block_commit.test
      mysql-test/t/mdl_sync.test
      mysql-test/t/read_only_innodb.test
      sql/sql_base.cc
=== modified file 'mysql-test/r/flush_block_commit.result'
--- a/mysql-test/r/flush_block_commit.result	2009-12-04 23:02:48 +0000
+++ b/mysql-test/r/flush_block_commit.result	2009-12-09 15:56:34 +0000
@@ -17,9 +17,9 @@ COMMIT;
 # Verify that 'con1' was blocked and data did not move.
 SELECT * FROM t1;
 a
-1
 UNLOCK TABLES;
 # Switch to connection con1
+# Reaping COMMIT
 # Switch to connection con1
 BEGIN;
 SELECT * FROM t1 FOR UPDATE;

=== modified file 'mysql-test/r/mdl_sync.result'
--- a/mysql-test/r/mdl_sync.result	2009-12-09 15:48:42 +0000
+++ b/mysql-test/r/mdl_sync.result	2009-12-09 15:56:34 +0000
@@ -173,3 +173,24 @@ drop table t2;
 # Switching to connection 'default'.
 # Clean-up.
 drop table t1;
+#
+# Test for bug #46673 "Deadlock between FLUSH TABLES WITH READ LOCK
+#                      and DML".
+#
+drop tables if exists t1;
+create table t1 (i int);
+# Switching to connection 'con46673'.
+begin;
+insert into t1 values (1);
+# Switching to connection 'default'.
+# Statement below should not get blocked. And if after some
+# changes to code it is there should not be a deadlock between
+# it and transaction from connection 'con46673'.
+flush tables with read lock;
+unlock tables;
+# Switching to connection 'con46673'.
+delete from t1 where i = 1;
+commit;
+# Switching to connection 'default'.
+# Clean-up
+drop table t1;

=== modified file 'mysql-test/r/read_only_innodb.result'
--- a/mysql-test/r/read_only_innodb.result	2009-12-04 23:02:48 +0000
+++ b/mysql-test/r/read_only_innodb.result	2009-12-09 15:56:34 +0000
@@ -7,10 +7,12 @@ insert into table_11733 values(11733);
 set global read_only=1;
 select @@global.read_only;
 @@global.read_only
-0
+1
 select * from table_11733 ;
-ERROR 40001: Deadlock found when trying to get lock; try restarting transaction
+a
+11733
 COMMIT;
+ERROR HY000: The MySQL server is running with the --read-only option so it cannot execute this statement
 set global read_only=0;
 drop table table_11733 ;
 drop user test@localhost;

=== modified file 'mysql-test/t/flush_block_commit.test'
--- a/mysql-test/t/flush_block_commit.test	2009-12-04 23:02:48 +0000
+++ b/mysql-test/t/flush_block_commit.test	2009-12-09 15:56:34 +0000
@@ -29,26 +29,25 @@ BEGIN;
 INSERT INTO t1 VALUES(1);
 --echo # Switch to connection con2
 connection con2;
---send FLUSH TABLES WITH READ LOCK
+FLUSH TABLES WITH READ LOCK;
 --echo # Switch to connection con1
 connection con1;
 --echo # Sending:
-COMMIT;
+--send COMMIT
 --echo # Switch to connection con2
 connection con2;
---reap
 --echo # Wait until COMMIT gets blocked.
-#let $wait_condition=
-#  select count(*) = 1 from information_schema.processlist
-#  where state = "Waiting for release of readlock" and info = "COMMIT";
-#--source include/wait_condition.inc
+let $wait_condition=
+  select count(*) = 1 from information_schema.processlist
+  where state = "Waiting for release of readlock" and info = "COMMIT";
+--source include/wait_condition.inc
 --echo # Verify that 'con1' was blocked and data did not move.
 SELECT * FROM t1;
 UNLOCK TABLES;
 --echo # Switch to connection con1
 connection con1;
-#--echo # Reaping COMMIT
-#--reap
+--echo # Reaping COMMIT
+--reap
 
 # No deadlock ?
 

=== modified file 'mysql-test/t/mdl_sync.test'
--- a/mysql-test/t/mdl_sync.test	2009-12-09 15:48:42 +0000
+++ b/mysql-test/t/mdl_sync.test	2009-12-09 15:56:34 +0000
@@ -343,6 +343,43 @@ disconnect con46044;
 disconnect con46044_2;
 drop table t1;
 
+
+--echo #
+--echo # Test for bug #46673 "Deadlock between FLUSH TABLES WITH READ LOCK
+--echo #                      and DML".
+--echo #
+--disable_warnings
+drop tables if exists t1;
+--enable_warnings
+connect (con46673, localhost, root,,);
+connection default;
+create table t1 (i int);
+
+--echo # Switching to connection 'con46673'.
+connection con46673;
+begin;
+insert into t1 values (1);
+
+--echo # Switching to connection 'default'.
+connection default;
+--echo # Statement below should not get blocked. And if after some
+--echo # changes to code it is there should not be a deadlock between
+--echo # it and transaction from connection 'con46673'.
+flush tables with read lock;
+unlock tables;
+
+--echo # Switching to connection 'con46673'.
+connection con46673;
+delete from t1 where i = 1;
+commit;
+
+--echo # Switching to connection 'default'.
+connection default;
+--echo # Clean-up
+disconnect con46673;
+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

=== modified file 'mysql-test/t/read_only_innodb.test'
--- a/mysql-test/t/read_only_innodb.test	2009-12-04 23:02:48 +0000
+++ b/mysql-test/t/read_only_innodb.test	2009-12-09 15:56:34 +0000
@@ -16,8 +16,6 @@ DROP TABLE IF EXISTS table_11733 ;
 grant CREATE, SELECT, DROP on *.* to test@localhost;
 
 connect (con1,localhost,test,,test);
-connect (con2,localhost,root,,);
-
 
 connection default;
 set global read_only=0;
@@ -30,22 +28,15 @@ BEGIN;
 insert into table_11733 values(11733);
 
 connection default;
-send set global read_only=1;
-
-connection con2;
-let $wait_condition=
-  select count(*) = 1 from information_schema.processlist
-  where state = "Flushing tables" and info = "set global read_only=1";
---source include/wait_condition.inc
+set global read_only=1;
 
 connection con1;
 select @@global.read_only;
---error ER_LOCK_DEADLOCK
 select * from table_11733 ;
+--error ER_OPTION_PREVENTS_STATEMENT
 COMMIT;
 
 connection default;
-reap;
 set global read_only=0;
 drop table table_11733 ;
 drop user test@localhost;
@@ -90,6 +81,5 @@ DROP TABLE t1;
 DROP USER test@localhost;
 
 disconnect con1;
-disconnect con2;
 
 --echo echo End of 5.1 tests 

=== modified file 'sql/sql_base.cc'
--- a/sql/sql_base.cc	2009-12-09 15:48:42 +0000
+++ b/sql/sql_base.cc	2009-12-09 15:56:34 +0000
@@ -2648,7 +2648,9 @@ bool open_table(THD *thd, TABLE_LIST *ta
     DBUG_RETURN(FALSE);
   }
 
+#ifdef DISABLED_UNTIL_GRL_IS_MADE_PART_OF_MDL
   if (!(share= (TABLE_SHARE *) mdl_ticket->get_cached_object()))
+#endif
   {
     if (!(share= get_table_share_with_create(thd, table_list, key,
                                              key_length, OPEN_VIEW,
@@ -2714,13 +2716,16 @@ bool open_table(THD *thd, TABLE_LIST *ta
     if (table_list->i_s_requested_object &  OPEN_VIEW_ONLY)
       goto err_unlock;
 
+#ifdef DISABLED_UNTIL_GRL_IS_MADE_PART_OF_MDL
     /*
       We are going to to store extra reference to the share in MDL-subsystem
       so we need to increase reference counter;
     */
     reference_table_share(share);
     mdl_ticket->set_cached_object(share, table_share_release_hook);
+#endif
   }
+#ifdef DISABLED_UNTIL_GRL_IS_MADE_PART_OF_MDL
   else
   {
     if (table_list->view)
@@ -2741,6 +2746,7 @@ bool open_table(THD *thd, TABLE_LIST *ta
     */
     reference_table_share(share);
   }
+#endif
 
   if (share->version != refresh_version)
   {


Attachment: [text/bzr-bundle] bzr/kostja@sun.com-20091209155634-6mypyruinnqn3sq9.bundle
Thread
bzr commit into mysql-5.6-next-mr branch (kostja:3015) Bug#46673Konstantin Osipov9 Dec