MySQL Bugs: #72439: Deadlock using SELECT … FOR UPDATE in MySQL

Bug #72439	Deadlock using SELECT … FOR UPDATE in MySQL
Submitted:	24 Apr 2014 13:42	Modified:	13 Nov 2014 8:00
Reporter:	Joaquin Cuenca Abela	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Locking	Severity:	S3 (Non-critical)
Version:	5.5.35	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	deadlock, locks

Description:
When a transaction t1 holds an X lock and a transaction t2 waits on that X lock, an UPDATE executed in t1 may deadlock.

How to repeat:
Create the table and populate it with 1 element:

CREATE TABLE t (id INTEGER AUTOINCREMENT NOT NULL, desc TEXT NOT NULL);
INSERT INTO TABLE t VALUES (1, 'Hello');

Run two transactions in MySQL. In t1 I run:

START TRANSACTION;
SELECT * FROM t WHERE id = 1 FOR UPDATE;

In t2 I run:

START TRANSACTION;
SELECT * FROM t WHERE id = 1 FOR UPDATE;

At this point t1 holds an e(X)clusive lock on the row, and t2 waits until it can get an X lock (and t2 gets indeed blocked, so far so good). Now run an update in t1 (without any WHERE clause!):

UPDATE t SET desc = 'Hello from t1';

At this point in t2 I get immediately (no need to COMMIT the transaction) the error:

ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

The status information regarding the deadlock is:

------------------------
LATEST DETECTED DEADLOCK
------------------------
140424  8:45:46
*** (1) TRANSACTION:
TRANSACTION B6F18A3, ACTIVE 5 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 376, 1 row lock(s)
MySQL thread id 13885, OS thread handle 0x7f8b1dbd2700, query id 901012
 localhost root statistics
SELECT * FROM t WHERE id = 1 FOR UPDATE
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 22921 n bits 72 index `PRIMARY` of table
 `test`.`t` trx id B6F18A3 lock_mode X locks rec but not gap waiting
Record lock, heap no 4 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 0: len 4; hex 80000001; asc     ;;
 1: len 6; hex 00000b6f1883; asc    o  ;;
 2: len 7; hex 06000059a211ea; asc    Y   ;;
 3: len 5; hex 48656c6c6f; asc Hello;;

*** (2) TRANSACTION:
TRANSACTION B6F18A2, ACTIVE 10 sec starting index read
mysql tables in use 1, locked 1
3 lock struct(s), heap size 376, 2 row lock(s)
MySQL thread id 13888, OS thread handle 0x7f8b1f64d700, query id 901068
 localhost root Updating
UPDATE t SET `desc` = 'Hello from t1'
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 0 page no 22921 n bits 72 index `PRIMARY` of table
 `test`.`t` trx id B6F18A2 lock_mode X locks rec but not gap
Record lock, heap no 4 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 0: len 4; hex 80000001; asc     ;;
 1: len 6; hex 00000b6f1883; asc    o  ;;
 2: len 7; hex 06000059a211ea; asc    Y   ;;
 3: len 5; hex 48656c6c6f; asc Hello;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 22921 n bits 72 index `PRIMARY` of table
 `test`.`t` trx id B6F18A2 lock_mode X waiting
Record lock, heap no 4 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 0: len 4; hex 80000001; asc     ;;
 1: len 6; hex 00000b6f1883; asc    o  ;;
 2: len 7; hex 06000059a211ea; asc    Y   ;;
 3: len 5; hex 48656c6c6f; asc Hello;;

*** WE ROLL BACK TRANSACTION (1)

Thank you for taking the time to write to us, imho this is a documented behavior. Please double-check the documentation available at http://dev.mysql.com/doc/refman/5.5/en/innodb-lock-modes.html

Thanks,
Umesh

IMHO the described behavior is a bug. Look at this series of statements:

CREATE TABLE t1 (c1 INT PRIMARY KEY, c2 INT) Engine InnoDB;
INSERT INTO t1 VALUES (1, 0);

t1: BEGIN;
t1: SELECT * FROM t1 WHERE c1=1 for UPDATE;

t2: BEGIN;
t2: SELECT * FROM t1 WHERE c1=1 for UPDATE;
(waits as expected for the X lock)

t1: UPDATE t1 SET c2=1 WHERE c1=1;
t1: COMMIT;

When t1 COMMITs, t2 finishes the SELECT with the new values in the requested row. Everything up to here works as expected.

Now lets change things to the original bug report. The only differenc is the absence of a WHERE clause in the update.

DROP TABLE t1;
CREATE TABLE t1 (c1 INT PRIMARY KEY, c2 INT) Engine InnoDB;
INSERT INTO t1 VALUES (1, 0);

t1: BEGIN;
t1: SELECT * FROM t1 WHERE c1=1 for UPDATE;

t2: BEGIN;
t2: SELECT * FROM t1 WHERE c1=1 for UPDATE;
(waits as expected for the X lock)

t1: UPDATE t1 SET c2=1;

Now t2 is stopped with a claimed deadlock. And this makes no sense IMHO. t2 is not holding any locks at this time, so how can there be a deadlock?

In any case: if that behavior is expected and documented, then the manual page referred to by Umesh does not explain it.