Databases
StackOverflow2013
Postsdbo
PODeadlock issue when transaction tries to accuire a lock it's already holding
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PODeadlock issue when transaction tries to accuire a lock it's already holding
    primarykey
    data
    text
    <p>I've found a very confusing deadlock situation that I need help to understand.</p> <p>There are two transactions going on:<br> (2) holds a lock for the query <code>delete from myTable where id = NAME_CONST('p_id',10000)</code>. This is a lock by PRIMARY KEY although not the full key but a range. It looks like this is a full write lock to me when it says <code>lock_mode X locks rec but not gap</code>.<br> (1) is waiting for this same lock, also for the query <code>delete from myTable where id = NAME_CONST('p_id',10000)</code>.<br> (2) is also trying go get this lock and MySQL detects a deadlock. </p> <p>What I can't understand is why (2) has to acquire the lock again as it already holds it and it's a write lock (lock_mode X) in all cases. </p> <p>It also looks like it's for the exact same query. </p> <p>Here is the table definition</p> <pre><code>create myTable ( id int unsigned not null, value1 char(8) not null, value2 int unsigned, primary key (id, value1) ); </code></pre> <p>and here is the information from <code>SHOW ENGINE INNODB STATUS\G</code></p> <pre><code>------------------------ LATEST DETECTED DEADLOCK ------------------------ 130313 14:46:28 *** (1) TRANSACTION: TRANSACTION 75ACB8A3, ACTIVE 0 sec, process no 6110, OS thread id 139973945382656 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 5154970, query id 5201313618 192.168.0.2 user updating delete from myTable where id = NAME_CONST('p_id',10000) *** (1) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 22371 page no 1598 n bits 104 index `PRIMARY` of table `db`.`myTable` trx id 75ACB8A3 lock_mode X waiting Record lock, heap no 32 PHYSICAL RECORD: n_fields 5; compact format; info bits 0 0: len 4; hex 0005af3a; asc :;; 1: len 8; hex 2020202020202020; asc ;; 2: len 6; hex 000075acb890; asc u ;; 3: len 7; hex ea0000020d011e; asc ;; 4: len 4; hex 00000065; asc e;; *** (2) TRANSACTION: TRANSACTION 75ACB890, ACTIVE 0 sec, process no 6110, OS thread id 139973957895936 starting index read mysql tables in use 1, locked 1 7 lock struct(s), hea p size 1248, 6 row lock(s), undo log entries 4 MySQL thread id 5155967, query id 5201313625 192.168.0.1 user updating delete from myTable where id = NAME_CONST('p_id',10000) *** (2) HOLDS THE LOCK(S): RECORD LOCKS space id 22371 page no 1598 n bits 104 index `PRIMARY` of table `db`.`myTable` trx id 75ACB890 lock_mode X locks rec but not gap Record lock, heap no 32 PHYSICAL RECORD: n_fields 5; compact format; info bits 0 0: len 4; hex 0005af3a; asc :;; 1: len 8; hex 2020202020202020; asc ;; 2: len 6; hex 000075acb890; asc u ;; 3: len 7; hex ea0000020d011e; asc ;; 4: len 4; hex 00000065; asc e;; *** (2) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 22371 page no 1598 n bits 104 index `PRIMARY` of table `db`.`myTable` trx id 75ACB890 lock_mode X waiting Record lock, heap no 32 PHYSICAL RECORD: n_fields 5; compact format; info bits 0 0: len 4; hex 0005af3a; asc :;; 1: len 8; hex 2020202020202020; asc ;; 2: len 6; hex 000075acb890; asc u ;; 3: len 7; hex ea0000020d011e; asc ;; 4: len 4; hex 00000065; asc e;; *** WE ROLL BACK TRANSACTION (1) </code></pre>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USMorteza Adi
    1. USAndreas Wederbrand
    plurals
    1. PL
      singulars
      1. LTLinked
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. PODeadlock issue when transaction tries to accuire a lock it's already holding
      1. USAndreas Wederbrand
      1. VTBountyStart
    2. VO
      singulars
      1. PODeadlock issue when transaction tries to accuire a lock it's already holding
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. PODeadlock issue when transaction tries to accuire a lock it's already holding
      1. This table or related slice is empty.
      1. VTUpMod
    1. CODid you tried to reproduce error? If "yes" could you show us scenario?
      singulars
      1. PODeadlock issue when transaction tries to accuire a lock it's already holding
      1. USravnur
    2. COI've tried to do this delete as fast as I can with `start transaction; delete...; rollback;` from two simulations threads as fast as bash can feed it to mysql but not once did I get a deadlock. I'm quite clueless as to how this can happen.
      singulars
      1. PODeadlock issue when transaction tries to accuire a lock it's already holding
      1. USAndreas Wederbrand
    3. COSee please this question (it describes how you can catch deadlock): http://stackoverflow.com/questions/2143873/how-to-explain-the-deadlock-better. Also there is quite nice article about detecting and recovery from deadlock: http://dwachira.hubpages.com/hub/Process-Deadlock-Definition-Prevention-Detection-Recovery-and-Avoidance. In general, deadlock is the issue of architecture and design. You should review your processes that updates data.
      singulars
      1. PODeadlock issue when transaction tries to accuire a lock it's already holding
      1. USravnur
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload