List:Commits« Previous MessageNext Message »
From:Jon Olav Hauglid Date:September 9 2009 1:01pm
Subject:bzr commit into mysql-5.4 branch (jon.hauglid:2813) Bug#42147
View as plain text  
#At file:///export/home/z/mysql-next-bugfixing-bug42147/ based on revid:luis.soares@stripped

 2813 Jon Olav Hauglid	2009-09-09
      Bug #42147 Concurrent DML and LOCK TABLE ... READ for InnoDB 
                 table cause warnings in errlog
      
      Concurrent execution of LOCK TABLES ... READ statement and DML statements 
      affecting the same InnoDB table on debug builds of MySQL server might lead 
      to "Found lock of type 6 that is write and read locked" warnings appearing 
      in error log.
      
      The problem is that the table-level locking code allows a thread to acquire
      TL_READ_NO_INSERT lock on a table even if there is another thread which holds 
      TL_WRITE_ALLOW_WRITE lock on the same table. At the same time, the locking
      code assumes that that such locks are incompatible (for example, see check_locks()).
      
      This doesn't lead to any problems other than warnings in error log for
      debug builds of server since for InnoDB tables TL_READ_NO_INSERT type of
      lock is only used for LOCK TABLES and for this statement InnoDB also
      performs its own table-level locking.
      
      Unfortunately, the table lock compatibility matrix cannot be updated to disallow
      TL_READ_NO_INSERT when another thread holds TL_WRITE_ALLOW_WRITE without 
      causing starvation of LOCK TABLE READ in InnoDB under high write load. 
      This patch therefore contains no code changes.
      
      The issue will be fixed later when LOCK TABLE READ has been updated
      to not use table locks. This bug will therefore be marked as 
      "To be fixed later".
      
      Code comment in thr_lock.c expanded to clarify the issue and a 
      test case based on the bug description added to innodb_mysql_lock.test.
      Note that a global suppression rule has been added to both MTR v1 and v2
      for the "Found lock of type 6 that is write and read locked" warning.
      These suppression rules must be removed once this bug fix properly fixed.

    modified:
      mysql-test/include/mtr_warnings.sql
      mysql-test/lib/v1/mtr_report.pl
      mysql-test/r/innodb_mysql_lock.result
      mysql-test/t/innodb_mysql_lock.test
      mysys/thr_lock.c
=== modified file 'mysql-test/include/mtr_warnings.sql'
--- a/mysql-test/include/mtr_warnings.sql	2009-07-10 11:44:11 +0000
+++ b/mysql-test/include/mtr_warnings.sql	2009-09-09 13:01:25 +0000
@@ -172,6 +172,14 @@ INSERT INTO global_suppressions VALUES
   */
  ("Can't find file: '.\\\\test\\\\\\?{8}.frm'"),
 
+ /*
+   BUG#42147 - Concurrent DML and LOCK TABLE ... READ for InnoDB 
+   table cause warnings in errlog
+   Note: This is a temporary suppression until Bug#42147 can be 
+   fixed properly. See bug page for more information.
+  */
+ ("Found lock of type 6 that is write and read locked"),
+
  ("THE_LAST_SUPPRESSION")||
 
 

=== modified file 'mysql-test/lib/v1/mtr_report.pl'
--- a/mysql-test/lib/v1/mtr_report.pl	2009-02-05 08:59:29 +0000
+++ b/mysql-test/lib/v1/mtr_report.pl	2009-09-09 13:01:25 +0000
@@ -377,6 +377,9 @@ sub mtr_report_stats ($) {
 		/Slave: Can't DROP 'c7'.* 1091/ or
 		/Slave: Key column 'c6'.* 1072/ or
 
+    # Warnings generated until bug#42147 is properly resolved
+    /Found lock of type 6 that is write and read locked/ or
+
 		# rpl_idempotency.test produces warnings for the slave.
 		($testname eq 'rpl.rpl_idempotency' and
 		 (/Slave: Can\'t find record in \'t1\' Error_code: 1032/ or

=== modified file 'mysql-test/r/innodb_mysql_lock.result'
--- a/mysql-test/r/innodb_mysql_lock.result	2009-07-08 12:09:38 +0000
+++ b/mysql-test/r/innodb_mysql_lock.result	2009-09-09 13:01:25 +0000
@@ -21,4 +21,36 @@ INSERT INTO t1 VALUES (2);
 ERROR 40001: Deadlock found when trying to get lock; try restarting transaction
 # Cleanup
 commit;
+set @@autocommit=1;
 commit;
+set @@autocommit=1;
+set @@autocommit=1;
+#
+# Bug #42147 Concurrent DML and LOCK TABLE ... READ for InnoDB 
+#            table cause warnings in errlog
+#
+#
+# Note that this test for now relies on a global suppression of 
+# the warning "Found lock of type 6 that is write and read locked"
+# This suppression rule can be removed once Bug#42147 is properly
+# fixed. See bug page for more info.
+#
+DROP TABLE IF EXISTS t1;
+CREATE TABLE t1 (i INT) engine= innodb;
+# Connection 2
+# Get user-level lock
+SELECT get_lock('bug42147_lock', 60);
+get_lock('bug42147_lock', 60)
+1
+# Connection 1
+INSERT INTO t1 SELECT get_lock('bug42147_lock', 60);
+# Connection 2
+LOCK TABLES t1 READ;
+SELECT release_lock('bug42147_lock');
+release_lock('bug42147_lock')
+1
+# Connection 1
+# Connection 2
+UNLOCK TABLES;
+# Connection 1
+DROP TABLE t1;

=== modified file 'mysql-test/t/innodb_mysql_lock.test'
--- a/mysql-test/t/innodb_mysql_lock.test	2009-07-08 12:09:38 +0000
+++ b/mysql-test/t/innodb_mysql_lock.test	2009-09-09 13:01:25 +0000
@@ -1,5 +1,8 @@
 -- source include/have_innodb.inc
 
+# Save the initial number of concurrent sessions.
+--source include/count_sessions.inc
+
 --echo #
 --echo # Bug #22876 Four-way deadlock
 --echo #
@@ -51,8 +54,68 @@ INSERT INTO t1 VALUES (2);
 connection con2;
 --reap
 commit;
+set @@autocommit=1;
 connection con1;
 commit;
+set @@autocommit=1;
 connection con3;
 --reap
+set @@autocommit=1;
 connection default;
+
+disconnect con1;
+disconnect con3;
+
+--echo #
+--echo # Bug #42147 Concurrent DML and LOCK TABLE ... READ for InnoDB 
+--echo #            table cause warnings in errlog
+--echo #
+
+--echo #
+--echo # Note that this test for now relies on a global suppression of 
+--echo # the warning "Found lock of type 6 that is write and read locked"
+--echo # This suppression rule can be removed once Bug#42147 is properly
+--echo # fixed. See bug page for more info.
+--echo #
+
+--disable_warnings
+DROP TABLE IF EXISTS t1;
+--enable_warnings
+
+CREATE TABLE t1 (i INT) engine= innodb;
+
+--echo # Connection 2
+--echo # Get user-level lock
+connection con2;
+SELECT get_lock('bug42147_lock', 60);
+
+--echo # Connection 1
+connection default;
+--send INSERT INTO t1 SELECT get_lock('bug42147_lock', 60)
+
+--echo # Connection 2
+connection con2;
+let $wait_condition=
+  SELECT COUNT(*) > 0 FROM information_schema.processlist
+  WHERE state = 'User lock' 
+  AND info = 'INSERT INTO t1 SELECT get_lock(\'bug42147_lock\', 60)';
+--source include/wait_condition.inc
+LOCK TABLES t1 READ;
+SELECT release_lock('bug42147_lock');
+
+--echo # Connection 1
+connection default;
+--reap
+
+--echo # Connection 2
+connection con2;
+UNLOCK TABLES;
+
+--echo # Connection 1
+connection default;
+disconnect con2;
+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 'mysys/thr_lock.c'
--- a/mysys/thr_lock.c	2009-03-11 17:17:00 +0000
+++ b/mysys/thr_lock.c	2009-09-09 13:01:25 +0000
@@ -564,13 +564,30 @@ thr_lock(THR_LOCK_DATA *data, THR_LOCK_O
     if (lock->write.data)
     {
       /*
-        We can allow a read lock even if there is already a write lock
-	 on the table in one the following cases:
-	 - This thread alread have a write lock on the table
-	 - The write lock is TL_WRITE_ALLOW_READ or TL_WRITE_DELAYED
-           and the read lock is TL_READ_HIGH_PRIORITY or TL_READ
-         - The write lock is TL_WRITE_CONCURRENT_INSERT or TL_WRITE_ALLOW_WRITE
-	   and the read lock is not TL_READ_NO_INSERT
+        We can allow a read lock even if there is already a
+        write lock on the table if they are owned by the same
+        thread or if they satisfy the following lock
+        compatibility matrix:
+
+           Request
+          /-------
+         H|++++  WRITE_ALLOW_WRITE
+         e|+++-  WRITE_ALLOW_READ
+         l|+++-  WRITE_CONCURRENT_INSERT
+         d|++++  WRITE_DELAYED
+           ||||
+           |||\= READ_NO_INSERT
+           ||\ = READ_HIGH_PRIORITY
+           |\  = READ_WITH_SHARED_LOCKS
+           \   = READ
+
+        + = Request can be satisified.
+        - = Request cannot be satisified.
+
+        READ_NO_INSERT and WRITE_ALLOW_WRITE should in principle
+        be incompatible. However this will cause starvation of
+        LOCK TABLE READ in InnoDB under high write load.
+        See Bug#42147 for more information.
       */
 
       DBUG_PRINT("lock",("write locked 1 by thread: 0x%lx",


Attachment: [text/bzr-bundle] bzr/jon.hauglid@sun.com-20090909130125-kq79wf5kjyav900i.bundle
Thread
bzr commit into mysql-5.4 branch (jon.hauglid:2813) Bug#42147Jon Olav Hauglid9 Sep