Bug #63122 "Searched SQL update" seems to release lock got by "select for update"
Submitted: 6 Nov 2011 22:51 Modified: 8 Nov 2011 9:47
Reporter: Phantom Within Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: InnoDB storage engine Severity:S3 (Non-critical)
Version:5.0+ OS:Any
Assigned to: CPU Architecture:Any
Tags: deadlock, innodb, locking, server

[6 Nov 2011 22:51] Phantom Within
Description:
There is a critical section in application logic which is controlled via "select for update" request, but there is "searched SQL update" within the section which is run for the same InnoDB table "select for update" was used for, and such "update" request lets another thread get in, i.e. get lock via "select for update" request. As a result server returns "ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction".

The issue doesn't take place if "searched SQL update" is not used.

I've got it with 5.0.83, 5.1.49, and 5.5.17. Other versions were not checked, it seems to be a common thing. And I'm not sure it's a bug, probably this is how it works, but I didn't get confirmation in docs and didn't find related issue report.

How to repeat:
Session0: create table t (id int primary key, name varchar(64));
Session0: insert into t (id,name) values (1,'one'),(2,'two'),(3,'three');
Session1: use test;
Session1: set autocommit=0;
Session2: use test;
Session2: set autocommit=0;
Session1: select * from t where id=1 for update;
+----+------+
| id | name |
+----+------+
|  1 | one  |
+----+------+
1 row in set (0.00 sec)
Session2: select * from t where id=1 for update;
Session1: update t set name=name where exists (select 1 from dual);
Query OK, 0 rows affected (0.01 sec)
Rows matched: 3  Changed: 0  Warnings: 0
Session2:
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

There is no "deadlock found" error if "update t set name=name where id=?;" is used instead.

5.1.49 and 5.5.17 seem to have the same behaviour, i.e. Session2 gets "deadlock found" error and Session1 keeps working. 5.0.83 returns "deadlock found" error to Session1, and Session2 gets lock and query result.
[8 Nov 2011 9:47] Valeriy Kravchuk
Verified just as described with 5.5.17. This is what we can see in SHOW ENGINE INNODB STATUS:

------------------------
LATEST DETECTED DEADLOCK
------------------------
111108 11:34:02
*** (1) TRANSACTION:
TRANSACTION 271B, ACTIVE 15 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 320, 1 row lock(s)
MySQL thread id 12, OS thread handle 0x26c4, query id 97 localhost 127.0.0.1 roo
t statistics
select * from t where id=1 for update
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 510 n bits 72 index `PRIMARY` of table `test`.`t
` trx id 271B lock_mode X locks rec but not gap waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 0: len 4; hex 80000001; asc     ;;
 1: len 6; hex 000000002719; asc     ' ;;
 2: len 7; hex 96000001760110; asc     v  ;;
 3: len 3; hex 6f6e65; asc one;;

*** (2) TRANSACTION:
TRANSACTION 271A, ACTIVE 25 sec starting index read, thread declared inside Inno
DB 500
mysql tables in use 1, locked 1
3 lock struct(s), heap size 320, 2 row lock(s)
MySQL thread id 11, OS thread handle 0x474, query id 98 localhost 127.0.0.1 root
 Updating
update t set name=name where exists (select 1 from dual)
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 0 page no 510 n bits 72 index `PRIMARY` of table `test`.`t
` trx id 271A lock_mode X locks rec but not gap
Record lock, heap no 2 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 0: len 4; hex 80000001; asc     ;;
 1: len 6; hex 000000002719; asc     ' ;;
 2: len 7; hex 96000001760110; asc     v  ;;
 3: len 3; hex 6f6e65; asc one;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 510 n bits 72 index `PRIMARY` of table `test`.`t
` trx id 271A lock_mode X waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 0: len 4; hex 80000001; asc     ;;
 1: len 6; hex 000000002719; asc     ' ;;
 2: len 7; hex 96000001760110; asc     v  ;;
 3: len 3; hex 6f6e65; asc one;;

*** WE ROLL BACK TRANSACTION (1)
------------
TRANSACTIONS
------------
Trx id counter 271C
Purge done for trx's n:o < 2711 undo n:o < 0
History list length 38
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 271B, not started
MySQL thread id 12, OS thread handle 0x26c4, query id 97 localhost 127.0.0.1 roo
t
---TRANSACTION 271A, ACTIVE 154 sec
3 lock struct(s), heap size 320, 5 row lock(s)
MySQL thread id 11, OS thread handle 0x474, query id 99 localhost 127.0.0.1 root

show engine innodb status
TABLE LOCK table `test`.`t` trx id 271A lock mode IX
RECORD LOCKS space id 0 page no 510 n bits 72 index `PRIMARY` of table `test`.`t
` trx id 271A lock_mode X locks rec but not gap
Record lock, heap no 2 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 0: len 4; hex 80000001; asc     ;;
 1: len 6; hex 000000002719; asc     ' ;;
 2: len 7; hex 96000001760110; asc     v  ;;
 3: len 3; hex 6f6e65; asc one;;

RECORD LOCKS space id 0 page no 510 n bits 72 index `PRIMARY` of table `test`.`t
` trx id 271A lock_mode X
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;

Record lock, heap no 2 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 0: len 4; hex 80000001; asc     ;;
 1: len 6; hex 000000002719; asc     ' ;;
 2: len 7; hex 96000001760110; asc     v  ;;
 3: len 3; hex 6f6e65; asc one;;

Record lock, heap no 3 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 0: len 4; hex 80000002; asc     ;;
 1: len 6; hex 000000002719; asc     ' ;;
 2: len 7; hex 9600000176011c; asc     v  ;;
 3: len 3; hex 74776f; asc two;;

Record lock, heap no 4 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 0: len 4; hex 80000003; asc     ;;
 1: len 6; hex 000000002719; asc     ' ;;
 2: len 7; hex 96000001760128; asc     v (;;
 3: len 5; hex 7468726565; asc three;;

I leave interpretation of the above to InnoDB developers, but deadlock in this case looks artificial to me. 

Why this wait "lock_mode X locks rec but not gap waiting" prevents first session from getting "lock_mode X" lock on the same record when it already has "lock_mode X locks rec but not gap" on it?
[18 Jun 2020 9:14] Jakub Lopuszanski
Posted by developer:
 
I believe this issue was fixed in 8.0.18 when fixing Bug #23755664 DEADLOCK WITH 3 CONCURRENT DELETES BY UNIQUE KEY, at least I can no longer reproduce the deadlock using the original scenario innodb_bug13418647.test from the comment titled "[19 Nov 2012 3:36] Annamalai Gurusami (AGURUSAM)" on latest trunk:
mysqltest: At line 34: Query 'reap' succeeded, should have failed with error '1213'

This makes sense, because I believe the underlying root cause was the same: 

We have a trx which already held an exclusive lock for the record only, but not for the gap, which requests an exclusive lock for both the lock and the gap. 

In the old code these would mean it now requests a different kind of the lock and thus has to check for conflicts. To avoid starving others, it has to give priority to trxs already waiting in the queue with conflicting lock requests. In this scenario we indeed have another transaction which waits for an exclusive lock on the record (but not the gap) already in the queue and the two lock types ('exclusive record only', and 'exclusive record+gap') conflict with each other, so we conclude our trx has to wait, hence closing the deadlock cycle.

In 8.0.18 we are smarter: we realize that a trx requesting gap+rec lock is conceptually requesting two locks: on a gap and on a record. If it already has one of the two pieces, it only requests the missing one. In this case this means the trx only needs to request an exclusive lock on the gap itself. And requests for locks on a gaps doe not conflict with requests for locks on records. In fact a request for a lock on the gap never conflicts with anything due to our locking rules. This means this whole class of deadlocks is avoided.