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
Triage: Needs Triage: D3 (Medium)

[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?