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: | |
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
[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.