| Bug #42147 | Concurrent DML and LOCK TABLE ... READ for InnoDB table cause warnings in errlog | ||
|---|---|---|---|
| Submitted: | 16 Jan 2009 6:06 | Modified: | 19 Jun 2014 22:23 |
| Reporter: | Dmitry Lenev | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: Locking | Severity: | S3 (Non-critical) |
| Version: | 5.0.77-bzr, 5.1, 6.0 | OS: | Any |
| Assigned to: | Jon Olav Hauglid | CPU Architecture: | Any |
[16 Jan 2009 6:06]
Dmitry Lenev
[16 Jan 2009 7:14]
Valeriy Kravchuk
Thank you for a bug report. Verified just as described: openxs@suse:/home2/openxs/dbs/5.0/mysql-test> ./mysql-test-run.pl 42147 Logging: ./mysql-test-run.pl 42147 MySQL Version 5.0.76 Using ndbcluster when necessary, mysqld supports it Setting mysqld to support SSL connections Binaries are debug compiled mysql-test-run: WARNING: Could not find all required ndb binaries, all ndb tests will fail, use --skip-ndbcluster to skip testing it. Using MTR_BUILD_THREAD = 0 Using MASTER_MYPORT = 9306 Using MASTER_MYPORT1 = 9307 Using SLAVE_MYPORT = 9308 Using SLAVE_MYPORT1 = 9309 Using SLAVE_MYPORT2 = 9310 Using NDBCLUSTER_PORT = 9311 Using IM_PORT = 9313 Using IM_MYSQLD1_PORT = 9314 Using IM_MYSQLD2_PORT = 9315 Killing Possible Leftover Processes Removing Stale Files Creating Directories Installing Master Database ======================================================= Starting Tests in the 'main' suite TEST RESULT TIME (ms) ------------------------------------------------------- 42147 [ fail ] mysqltest: The specified result file does not exist: '/home2/openxs/dbs/5.0/mysql-test/r/42147.result' The result from queries just before the failure was: create table t1 (i int) engine= innodb; insert into t1 values (sleep(5));; lock tables t1 read; unlock tables; More results from queries before failure can be found in /home2/openxs/dbs/5.0/mysql-test/var/log/42147.log Aborting: 42147 failed in default mode. To continue, re-run with '--force'. Stopping All Servers skipped 9 bytes from file: socket (3) openxs@suse:/home2/openxs/dbs/5.0/mysql-test> tail var/log/master.err Version: '5.0.76-debug-log' socket: '/home2/openxs/dbs/5.0/mysql-test/var/tmp/master.sock' port: 9306 Source distribution Warning at 'read lock with old write lock': Found lock of type 5 that is write and read locked Warning at 'start of release lock': Found lock of type 5 that is write and read locked 081220 12:05:02 [Note] Got signal 15 to shutdown mysqld 081220 12:05:02 [Note] /home2/openxs/dbs/5.0/libexec/mysqld: Normal shutdown 081220 12:05:02 InnoDB: Starting shutdown... 081220 12:05:04 InnoDB: Shutdown completed; log sequence number 0 45505 081220 12:05:04 [Note] /home2/openxs/dbs/5.0/libexec/mysqld: Shutdown complete
[16 Jan 2009 7:17]
Valeriy Kravchuk
Similar warning (other lock type, though) on recent 6.0.10 from bzr: 081220 12:08:19 InnoDB: Started; log sequence number 0 0 081220 12:08:20 [Note] Event Scheduler: Loaded 0 events 081220 12:08:20 [Note] /home2/openxs/dbs/6.0/libexec/mysqld: ready for connections. Version: '6.0.10-alpha-debug-log' socket: '/home2/openxs/dbs/6.0/mysql-test/var/tmp/master.sock' port: 9306 Source distribution Warning at 'read lock with old write lock': Found lock of type 6 that is write and read locked Warning at 'start of release lock': Found lock of type 6 that is write and read locked 081220 12:08:26 [Note] Got signal 15 to shutdown mysqld 081220 12:08:26 [Note] /home2/openxs/dbs/6.0/libexec/mysqld: Normal shutdown 081220 12:08:26 [Note] Event Scheduler: Purging the queue. 0 events 081220 12:08:26 InnoDB: Starting shutdown... 081220 12:08:28 InnoDB: Shutdown completed; log sequence number 0 48015 081220 12:08:29 [Note] Debug sync points hit: 21 081220 12:08:29 [Note] Debug sync points executed: 0 081220 12:08:29 [Note] Debug sync points max active per thread: 0 081220 12:08:29 [Note] /home2/openxs/dbs/6.0/libexec/mysqld: Shutdown complete
[9 Jul 2009 9:23]
Konstantin Osipov
Also causing sporadic foreign_key_all_engines test failures in the foreign keys feature tree.
[23 Jul 2009 12:35]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/79182 2828 Jon Olav Hauglid 2009-07-23 Bug #42147 Concurrent DML and LOCK TABLE ... READ for InnoDB table cause warnings in errlog Table lock compatibility matrix updated to disallow TL_READ_NO_INSERT if another connection holds TL_WRITE_ALLOW_WRITE. Code comment updated to match implemented behaviour (it didn't before). Test case added to innodb_mysql_lock.test.
[19 Aug 2009 11:24]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/81060 2866 Jon Olav Hauglid 2009-08-19 Bug #42147 Concurrent DML and LOCK TABLE ... READ for InnoDB table cause warnings in errlog Table lock compatibility matrix updated to disallow TL_READ_NO_INSERT if another connection holds TL_WRITE_ALLOW_WRITE. Code comment updated to match implemented behaviour (it didn't before). Test case added to innodb_mysql_lock.test.
[25 Aug 2009 8:41]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/81478 2877 Jon Olav Hauglid 2009-08-25 Bug #42147 Concurrent DML and LOCK TABLE ... READ for InnoDB table cause warnings in errlog The table lock compatibility matrix cannnot be updated to allow TL_READ_NO_INSERT when another connection 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 this test case for now suppresses the warnings generated by InnoDB.
[9 Sep 2009 10:42]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/82801 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 cannnot be updated to allow 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 for the "Found lock of type 6 that is write and read locked" warning.
[9 Sep 2009 13:09]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/82819 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.
[9 Sep 2009 13:19]
Jon Olav Hauglid
Pushed to mysql-next-bugfixing (5.4.4-alpha)
[15 Sep 2009 13:52]
Bugs System
Pushed into 5.4.4-alpha (revid:alik@sun.com-20090915134838-5nj3ycjfsqc2vr2f) (version source revid:jon.hauglid@sun.com-20090909130125-kq79wf5kjyav900i) (merge vers: 5.4.4-alpha) (pib:11)
[16 Sep 2009 11:36]
Jon Olav Hauglid
Setting this bug as "To be fixed later" as instructed by Kostja. (The pushed patch contains no code changes)
[9 Dec 2009 15:13]
Jon Olav Hauglid
Pushed to mysql-next-4284 (5.6.0-beta).
[16 Feb 2010 16:49]
Bugs System
Pushed into 6.0.14-alpha (revid:alik@sun.com-20100216101445-2ofzkh48aq2e0e8o) (version source revid:kostja@sun.com-20091211154405-c9yhiewr9o5d20rq) (merge vers: 6.0.14-alpha) (pib:16)
[16 Feb 2010 16:58]
Bugs System
Pushed into mysql-next-mr (revid:alik@sun.com-20100216101208-33qkfwdr0tep3pf2) (version source revid:kostja@sun.com-20091210084103-l4f8u62u4evoy3dc) (pib:16)
[2 Mar 2010 1:08]
Paul DuBois
Noted in 6.0.14 changelog. Concurrent execution of a LOCK TABLES ... READ statement and DML statements affecting the same InnoDB table for debug builds of MySQL server might lead to "Found lock of type 6 that is write and read locked" warnings in the error log. Setting report to Need Merge pending push of Celosia to release tree.
[6 Mar 2010 11:07]
Bugs System
Pushed into 5.5.3-m3 (revid:alik@sun.com-20100306103849-hha31z2enhh7jwt3) (version source revid:vvaintroub@mysql.com-20100216221947-luyhph0txl2c5tc8) (merge vers: 5.5.99-m3) (pib:16)
[7 Mar 2010 12:56]
Paul DuBois
Noted in 5.5.3 changelog.
[12 Apr 2010 8:23]
Jon Stephens
Per developer comments above and DLenev mail to Docs Team: No fix was ever pushed for this bug. Commented out changelog entry. Bug status unchanged.
[19 Jun 2014 22:23]
Paul DuBois
Noted in 5.7.5 changelog. For debug builds, concurrent execution of LOCK TABLES ... READ and a DML statement affecting the same InnoDB table might lead to "Found lock of type 6 that is write and read locked" warnings in the error log.
