8

According to The Good Word,

InnoDB uses automatic row-level locking. You can get deadlocks even in the case of transactions that just insert or delete a single row. That is because these operations are not really “atomic”; they automatically set locks on the (possibly several) index records of the row inserted or deleted.

How is it possible for two processes to deadlock on a single record?

KeatsKelleher
  • 10,015
  • 4
  • 45
  • 52
  • 1
    I wonder if one locks the row, the other the index row. – Andrew Dec 08 '15 at 20:06
  • Yeah, I was thinking the same thing... Could it be possible it has to acquire many locks? One for each index? And not serially? – KeatsKelleher Dec 08 '15 at 20:07
  • 1
    I found the answer! http://dev.mysql.com/doc/refman/5.7/en/innodb-lock-modes.html if someone wants to write it up feel free :) – KeatsKelleher Dec 08 '15 at 20:39
  • 2
    quick example. If more than 200 trx are waiting on a lock InnoDB considers it a deadlock. http://dev.mysql.com/doc/refman/5.7/en/innodb-lock-modes.html . Well, it's not a true deadlock though. – akuzminsky Dec 08 '15 at 21:10
  • Perhaps this is the link: http://doc.docs.sk/mysql-refman-5.5/innodb-lock-modes.html – Rick James Oct 04 '18 at 02:18
  • So I'm having the same problems with an Innodb table just doing inserts. Is the index taking too long to update? I find it very strange to have a deadlock on an insert. – Kevin Parker Aug 27 '19 at 22:19

2 Answers2

0

Because two processes hold in a transaction the exact same row (like a bank account, for example) and want to make changes to it.

So in the same bank account context, if one withdrawl needs to be made, another withdrawl cannot happen yet, because you may have a situation where the bank account may not have enough money for the second withdrawl.

The idea is with deadlocks, is to keep locking for the minimal length of time as possible.

Jonathan
  • 2,183
  • 4
  • 20
  • 25
0

Here's the example extracted from URL supplied in comments by Rick James:

CLIENT 1 (C1) CLIENT 2 (C2)
1 START TRANSACTION
2 SELECT * FROM t WHERE i = 1 LOCK IN SHARE MODE
3 START TRANSACTION
4 DELETE FROM t WHERE i = 1
5 DELETE FROM t WHERE i = 1

Explanation:

  • Step 2: C1 acquires a shared lock
  • Step 4: C2 requests an exclusive lock, but can't have it yet because of C1's shared lock
  • Step 5: now C1 wants an exclusive lock too, but C2 already waits for it

Neither C1 or C2 can get what they want, so there's a deadlock detected.

knarewski
  • 91
  • 7