Deadlock using SELECT ... FOR UPDATE in MySQL
mysql
Solution
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.
Problem
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.