# Deadlock situation, why??

**URL:** <https://forums.percona.com/t/deadlock-situation-why/1136>\
**Category:** Other MySQL® Questions\
**Created:** [April 2, 2009, 9:12am UTC](https://forums.percona.com/t/deadlock-situation-why/1136 "2009-04-02T09:12:35Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![esudnik](https://avatars.discourse-cdn.com/v4/letter/e/96bed5/32.png) [@esudnik](https://forums.percona.com/u/esudnik)\
**Post date:** [April 2, 2009, 9:12am UTC](https://forums.percona.com/t/deadlock-situation-why/1136/1 "2009-04-02T09:12:35Z")

</div>

Why is following deadlock situation possible?

As you can see from SHOW INNODB STATUS the update selects two different request-rows by its ID.

## LATEST DETECTED DEADLOCK

090402 16:06:16  
\*\*\* (1) TRANSACTION:  
TRANSACTION 0 183582, ACTIVE 2 sec, OS thread id 204 starting index read  
mysql tables in use 1, locked 1  
LOCK WAIT 7 lock struct(s), heap size 1024, undo log entries 192  
MySQL thread id 9, query id 131757 localhost 127.0.0.1 root Updating  
UPDATE ERA\_CASE.REQUEST SET timestampForLVS:=IF(state=‘confirmed’,IFNULL(timestampForLVS ,‘2009-04-02 16:06:16.33’),timestampForLVS),state:=‘transferred’ WHERE id=257698049538  
\*\*\* (1) WAITING FOR THIS LOCK TO BE GRANTED:  
RECORD LOCKS space id 0 page no 920 n bits 208 index `PRIMARY` of table `era_case/request` trx id 0 183582 lock\_mode X locks rec but not gap waiting  
Record lock, heap no 95 PHYSICAL RECORD: n\_fields 16; compact format; info bits 0  
0: len 8; hex 8000003c00002e02; asc \< . ;; 1: len 6; hex 00000002cd1c; asc Í ;; 2: len 7; hex 00000008ca3de2; asc Ê=â;; 3: len 3; hex 8147bd; asc G½;; 4: len 8; hex 800000340000198a; asc 4 ?;; 5: len 1; hex 01; asc ;; 6: len 2; hex 3420; asc 4 ;; 7: len 3; hex 8fb27a; asc ²z;; 8: SQL NULL; 9: SQL NULL; 10: len 4; hex 86123a57; asc ? :W;; 11: len 0; hex ; asc ;; 12: len 1; hex 0b; asc ;; 13: SQL NULL; 14: len 1; hex 04; asc ;; 15: len 4; hex 49d4c658; asc IÔÆX;;

\*\*\* (2) TRANSACTION:  
TRANSACTION 0 183580, ACTIVE 2 sec, OS thread id 2620 starting index read, thread declared inside InnoDB 500  
mysql tables in use 1, locked 1  
5 lock struct(s), heap size 1024, undo log entries 197  
MySQL thread id 11, query id 131761 localhost 127.0.0.1 root Updating  
UPDATE ERA\_CASE.REQUEST SET timestampForLVS:=IF(state=‘confirmed’,IFNULL(timestampForLVS ,‘2009-04-02 16:06:16.33’),timestampForLVS),state:=‘transferred’ WHERE id=257698049537  
\*\*\* (2) HOLDS THE LOCK(S):  
RECORD LOCKS space id 0 page no 920 n bits 208 index `PRIMARY` of table `era_case/request` trx id 0 183580 lock\_mode X locks rec but not gap  
Record lock, heap no 95 PHYSICAL RECORD: n\_fields 16; compact format; info bits 0  
0: len 8; hex 8000003c00002e02; asc \< . ;; 1: len 6; hex 00000002cd1c; asc Í ;; 2: len 7; hex 00000008ca3de2; asc Ê=â;; 3: len 3; hex 8147bd; asc G½;; 4: len 8; hex 800000340000198a; asc 4 ?;; 5: len 1; hex 01; asc ;; 6: len 2; hex 3420; asc 4 ;; 7: len 3; hex 8fb27a; asc ²z;; 8: SQL NULL; 9: SQL NULL; 10: len 4; hex 86123a57; asc ? :W;; 11: len 0; hex ; asc ;; 12: len 1; hex 0b; asc ;; 13: SQL NULL; 14: len 1; hex 04; asc ;; 15: len 4; hex 49d4c658; asc IÔÆX;;

---

<div class="post-metadata">

**Author:** ![aftabakhan](https://avatars.discourse-cdn.com/v4/letter/a/71c47a/32.png) [@aftabakhan](https://forums.percona.com/u/aftabakhan)\
**Post date:** [April 3, 2009, 3:17am UTC](https://forums.percona.com/t/deadlock-situation-why/1136/2 "2009-04-03T03:17:27Z")

</div>

An UPDATE, or a DELETE generally set record locks on every index record that is scanned in the processing of the SQL statement. It does not matter whether there are WHERE conditions in the statement that would exclude the row. InnoDB does not remember the exact WHERE condition, but only knows which index ranges were scanned. If the locks to be set are exclusive, InnoDB also retrieves the clustered index ( in your case it’s id) record and sets a lock on it.

---

<div class="post-metadata">

**Author:** ![MarkRose](https://avatars.discourse-cdn.com/v4/letter/m/85f322/32.png) [@MarkRose](https://forums.percona.com/u/MarkRose)\
**Post date:** [April 4, 2009, 2:04pm UTC](https://forums.percona.com/t/deadlock-situation-why/1136/3 "2009-04-04T14:04:03Z")

</div>

You might investigate MySQL’s SELECT … FOR UPDATE syntax and use it before your updates.