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:
None 
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
Description:
I'm using mysql-6.1-fk.
I start the server with mysqld --foreign-key-all-engines=1.

I start two connections.
I run a stored procedure which will cause some conflicts.
I see some warnings appear on the mysqld console screen.
For example:
"Warning at 'read lock with old write lock': Found lock of type 6 that is write and read locked

How to repeat:
Start up two client connections.

On one connection, say:
delimiter //
create database fk//
use fk
drop procedure if exists p//
drop table if exists t2//
drop table if exists t1//
create procedure p ()
begin
  declare v int default 0;
  declare continue handler for sqlexception begin end;
  create table t1 (s1 int not null unique) engine=falcon;
  create table t2 (s1 int references t1(s1) on update cascade) engine=falcon;
  while v < 10000 do
    if v mod 100 = 0 then select v; end if;
    insert into t1 values (v);
    insert into t2 values (v);
    update t1 set s1 = rand()*10000;
    update t2 set s1 = rand()*10000;
    set v = v + 1;
    end while;
  end//
call p()//

On another connection, say:
use fk
call p();

You'll see some warnings appear on the mysqld console screen:
"Warning at 'read lock with old write lock': Found lock of type 6 that is write and read locked
Warning at 'got wait_for_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 'read lock with old write lock': Found lock of type 6 that is write and read locked
Warning at 'got wait_for_lock': Found lock of type 6 that is write and read locked"

/* That's all. Kill the server any time to stop further messages. */
[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.