Bug #41667 Lock wait timeout exceeded when updating a row in a reference table
Submitted: 22 Dec 2008 1:54 Modified: 22 Dec 2008 5:09
Reporter: Edrick Duero Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server: Locking Severity:S3 (Non-critical)
Version:5.0.67 OS:Microsoft Windows (XP SP3)
Assigned to: CPU Architecture:Any

[22 Dec 2008 1:54] Edrick Duero
Description:
Lock wait timeout exceeded when updating a row in a reference table while the depended table has a pending insert transaction on another session.

How to repeat:
C:\Documents and Settings\Edrick>mysql -u root -p test

mysql> create table t1(
    -> f1 integer not null primary key,
    -> next_id integer);
Query OK, 0 rows affected (0.84 sec)

mysql> insert into t1 values(1, 0),(2, 0);
Query OK, 2 rows affected (0.07 sec)
Records: 2  Duplicates: 0  Warnings: 0

=========================
****** new session ******
=========================

C:\Documents and Settings\Edrick>mysql -u root -p test

mysql> set autocommit=0;
mysql> insert into t2 values(1,'row 1', 1);
Query OK, 1 row affected (0.00 sec)

**** do not commit transaction ****

=======================================
****** back to the first session ******
=======================================

mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)

mysql> update
    ->   t1
    -> set
    ->   next_id=next_id+1
    -> where f1=1;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql>

Suggested fix:
remove the foreign key constraint..
[22 Dec 2008 2:32] Edrick Duero
btw, this is the show create table output..

mysql> show create table t1\G;
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `f1` int(11) NOT NULL,
  `next_id` int(10) unsigned default NULL,
  PRIMARY KEY  (`f1`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

ERROR:
No query specified

mysql> show create table t2\G;
*************************** 1. row ***************************
       Table: t2
Create Table: CREATE TABLE `t2` (
  `f1` int(11) NOT NULL,
  `f2` varchar(50) default NULL,
  `t1_ref` int(11) default NULL,
  PRIMARY KEY  (`f1`),
  KEY `fk_t2_t1_ref` (`t1_ref`),
  CONSTRAINT `fk_t2_t1_ref` FOREIGN KEY (`t1_ref`) REFERENCES `t1` (`f1`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.20 sec)

ERROR:
No query specified

mysql>
[22 Dec 2008 5:09] Valeriy Kravchuk
This is intended and documented behaviour. Shared locks are set on table rows while checking foreign key constraint. Please, read the manual, http://dev.mysql.com/doc/refman/5.0/en/innodb-locks-set.html:

"If a FOREIGN KEY constraint is defined on a table, any insert, update, or delete that requires the constraint condition to be checked sets shared record-level locks on the records that it looks at to check the constraint. InnoDB also sets these locks in the case where the constraint fails."