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: | |
Category: | MySQL Server: Locking | Severity: | S3 (Non-critical) |
Version: | 5.5.35 | OS: | Any |
Assigned to: | CPU Architecture: | Any | |
Tags: | deadlock, locks |
[24 Apr 2014 13:42]
Joaquin Cuenca Abela
[13 Nov 2014 8:00]
MySQL Verification Team
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
[29 Aug 2016 9:21]
Axel Schwenke
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.