MySQL Bugs: #57118: Undetected deadlock with select for update on a InnoDB + MyISAM join

Bug #57118	Undetected deadlock with select for update on a InnoDB + MyISAM join
Submitted:	30 Sep 2010 0:51	Modified:	30 Sep 2010 6:41
Reporter:	Andrew Pimlott	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Locking	Severity:	S3 (Non-critical)
Version:	5.1.49, 5.1.52-bzr	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	deadlock

Description:
Running "select for update" on a join between an InnoDB table and a MyISAM table can cause a undetected deadlock that hangs participants until innodb_lock_wait_timeout expires. The deadlock in unreasonable because one transaction hangs on a select it has aready executed! The documentation implies that it should not even be possible.

How to repeat:
Setup:

create table isam (id int primary key) engine MyISAM;
create table inno (id int primary key) engine InnoDB;
insert into isam values (1);
insert into inno values (1);

To deadlock, run two transactions:
Txn 1: begin work;
Txn 2: begin work;
Txn 1: select * from isam join inno on isam.id = inno.id where isam.id = 1 for update;
Txn 2: select * from isam join inno on isam.id = inno.id where isam.id = 1 for update;
Txn 1: select * from isam join inno on isam.id = inno.id where isam.id = 1 for update;

The last statement from each transaction hangs.

I have also reproduced this on MySQL 5.0.51.

Suggested fix:
The documentation for "select for update" implies that it has no effect for MyISAM. (http://dev.mysql.com/doc/refman/5.1/en/select.html says, "If you use FOR UPDATE with a storage engine that uses page or row locks, ..." and doesn't say anything about MyISAM.) But this obviously isn't the case, because executing the same sequence on two InnoDB tables doesn't deadlock. (The second query from txn 1 succeeds, because that txn already has all the locks it needs.)

I would guess that "select for update" is taking a table lock on the MyISAM table. then releasing it when the query finishes. So when txn 2 blocks on the inno row lock, it is holding the isam table lock, so txn 1 blocks on that. However, this raises the question of why the deadlock isn't detected. http://dev.mysql.com/doc/refman/4.1/en/innodb-deadlock-detection.html says, "InnoDB is aware of table locks if innodb_table_locks = 1 (the default) and autocommit = 0, and the MySQL layer above InnoDB knows about row-level locks.". I checked that innodb_table_locks is on in my database. This implies that InnoDB should have detected the deadlock if it was due to a table lock.

The deadlock should be fixed, or if not it should be detected, or if not it should be documented.

Verified as described with current mysql-5.1 from bzr on Ubuntu. 

This is what we can see in "mysqladmin debug" output:

Thread database.table_name          Locked/Waiting        Lock_type

1       test.isam                   Waiting - write       High priority write lock
2       test.inno                   Locked - write        High priority write lock
2       test.isam                   Locked - write        High priority write lock

INNODB STATUS from the 3rd session shows the following:

...
---TRANSACTION 0 3843, ACTIVE 19 sec, process no 1589, OS thread id 3023719280 starting index read
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 320, 1 row lock(s)
MySQL thread id 2, query id 42 localhost root statistics
select * from isam join inno on isam.id = inno.id where isam.id = 1 for update
------- TRX HAS BEEN WAITING 19 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 1730 n bits 72 index `PRIMARY` of table `test`.`inno` trx id 0 3843 lock_mode X locks rec but not gap waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 4; hex 80000001; asc     ;; 1: len 6; hex 000000000f01; asc       ;; 2: len 7; hex 800000002d0110; asc     -  ;;

------------------
---TRANSACTION 0 3842, ACTIVE 26 sec, process no 1589, OS thread id 3023919984
mysql tables in use 1, locked 1
2 lock struct(s), heap size 320, 1 row lock(s)
MySQL thread id 1, query id 43 localhost root Table lock
select * from isam join inno on isam.id = inno.id where isam.id = 1 for update
...

and indeed, with 2 InnoDB tables second session waits, but the first one easily executes the same query again. So it is all about table level locks on MyISAM table used in join.

A fix: do not take TL_WRITE lock when executing SELECT FOR UPDATE and the underlying engine is MyISAM.
We have always been doing that, but we donot document this fact.