| 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: | |
| Category: | MySQL Server: Locking | Severity: | S3 (Non-critical) |
| Version: | 5.0.67 | OS: | Windows (XP SP3) |
| Assigned to: | CPU Architecture: | Any | |
[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."
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..