Deadlock using SELECT ... FOR UPDATE in MySQL

Question

Let's say I have the table:

CREATE TABLE t (id INTEGER AUTOINCREMENT NOT NULL, desc TEXT NOT NULL)

I populate the table with 1 element:

INSERT INTO TABLE t VALUES (1, 'Hello')

And I 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 I expect t1 to hold an e(X)clusive lock on the row, and t2 to wait until it can get an X lock (and t2 gets indeed blocked, so far so good). I then 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

Why am I getting this error? I guess there is a lock that t2 is obtaining that the full UPDATE needs to proceed, making a deadlock, but I don't understand how can t2 obtain a lock given that it should be waiting for t1 to finish.

Answer 1

What works and what does not

A way to make both transactions run through without a deadlock is to change the isolation level to READ COMMITED (or READ UNCOMMITED) in both connections:

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

(before start transaction).

Likely it would be enough to set it in t2, but just to be sure for the example, set it in both.

Changing the isolation level of transactions does introduce some side-effects, which one should inform about in the manual before changing this in a production environment.

Status information concerning deadlock

------------------------
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 `descc` = '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)

Explanation

As a_horse_with_no_name mentioned, this seems like a bug in MySQL. Transaction (2) wants to obtain a gap lock on the same row it already holds an X lock. Transaction (1) waits for a non-gap X lock on this row. It is not clear to me why this requests should conflict. Setting the isolation level to READ COMMITTED disables gap locking. Since the example works then, this is a hint that gap locking is indeed the problem here.