Deadlock using SELECT ... FOR UPDATE in MySQL

Question

Let's say I have a table:

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

I populate the table with one 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 the e (X) clusive lock on the line and t2 wait until it can get the X lock (and t2 really locks, so far so good). Then I run the update in t1 (without the WHERE clause!):

 UPDATE t SET desc = 'Hello from t1'; 

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

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

Why am I getting this error? I think there is a lock that t2 receives so that the full UPDATE should continue, deadlocking, but I don’t understand how t2 to get the lock, given that it must wait for t1 to complete.

Answer 1

What works and what doesn't

A way to bypass both transactions without deadlock is to change the isolation level for the READER COMMITTEE (or READ UNCOMMITED) in both connections

 SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; 

(before start transaction ).

This is probably enough to set it to t2 , but just for example, install it in both.

Changing the level of transaction isolation leads to some side effects that should be reported in the manual before changing this in a production environment.

Deadlock Information

 ------------------------ 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) is executed and is waiting for the same lock, which does not make sense.