Bug #41688 | Foreign keys: lock warnings | ||
---|---|---|---|
Submitted: | 23 Dec 2008 0:13 | Modified: | 24 Feb 2009 0:41 |
Reporter: | Peter Gulutzan | Email Updates: | |
Status: | Closed | Impact on me: | |
Category: | MySQL Server | Severity: | S3 (Non-critical) |
Version: | 6.1.0-alpha-debug | OS: | Linux (SUSE 10.0 / 32-bit) |
Assigned to: | Dmitry Lenev | CPU Architecture: | Any |
[23 Dec 2008 0:13]
Peter Gulutzan
[23 Dec 2008 14:59]
MySQL Verification Team
Thank you for the bug report. Verified as described: 081223 12:45:44 [Note] Event Scheduler: Loaded 0 events 081223 12:45:44 [Note] 6.1/libexec/mysqld: ready for connections. Version: '6.1.0-alpha-debug' socket: '/tmp/mysql.sock' port: 3306 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 Warning at 'after releasing lock': Found lock of type 6 that is write and read locked Warning at 'after waking up waiters': 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 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 Warning at 'after releasing lock': Found lock of type 6 that is write and read locked Warning at 'after waking up waiters': 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
[1 Jan 2009 21:36]
Dmitry Lenev
It looks like this problem is not specific to new implementation of foreign keys. I am getting the same warnings when running the following statements/routine against server in --foreign-key-all-engines=0 with binary logging turned on: delimiter // create database fk// use fk drop procedure if exists p// drop table if exists t2// drop table if exists t1// create table t1 (s1 int unique not null) engine=falcon// create table t1 (s2 int) engine=falcon// create procedure p () begin declare v int default 0; declare continue handler for sqlexception begin end; while v < 10000 do if v mod 100 = 0 then select v; end if; insert ignore into t1 values (rand()*10000); insert ignore into t1 select s1*rand() from t1 limit 1; insert into t2 values (rand()*10000); set v = v + 1; end while; end// call p()// And concurrently executing p() in the second connection... After preliminary analysis it seems that this problem stems from the fact that our locking implementation (wrongly) assumes that ANY read lock can be granted if we already have TL_WRITE_ALLOW_WRITE on table. Therefore we get warning when connection having TL_WRITE_ALLOW_WRITE gets TL_WRITE_ALLOW_READ even although another connection holds TL_WRITE_ALLOW_WRITE (Note that Falcon downgrades TL_WRITE to TL_WRITE_ALLOW_WRITE but does not downgrade TL_WRITE_ALLOW_READ).
[17 Jan 2009 22:16]
Dmitry Lenev
Actually additional investigation shows that MySQL will erroneously grant TL_READ_NO_INSERT lock if there is active TL_WRITE_ALLOW_WRITE belonging to any thread. It will also grant TL_WRITE_ALLOW_WRITE lock if there is active TL_WRITE_ALLOW_WRITE belonging to some thread (even if this thread holds also TL_READ_NO_INSERT).
[28 Jan 2009 7:28]
Dmitry Lenev
Patch fixing this and couple of other bugs can be found at: http://lists.mysql.com/commits/63523
[5 Feb 2009 11:43]
Dmitry Lenev
Fix for this bug was queued into mysql-6.0-runtime and will be merged eventually into the main and mysql-6.1-fk trees.
[5 Feb 2009 11:45]
Dmitry Lenev
BTW version of the patch that was queued can be found at: http://lists.mysql.com/commits/65297
[13 Feb 2009 7:25]
Bugs System
Pushed into 6.0.10-alpha (revid:alik@sun.com-20090211182317-uagkyj01fk30p1f8) (version source revid:alik@sun.com-20090211182317-uagkyj01fk30p1f8) (merge vers: 6.0.10-alpha) (pib:6)
[24 Feb 2009 0:41]
Paul DuBois
Noted in 6.0.10 changelog. For Falcon tables, concurrent execution of a statement which in the general case should acquire a TL_READ_NO_INSERT lock on the table (for example multiple-table update, DML with subqueries, or statements involving new foreign key checks) and a statement that modifies the table might lead to warnings in the error log or even to deadlocks.