'How is it possible for InnoDB to deadlock on a single row?
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?
Solution 1:[1]
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.
Solution 2:[2]
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.
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|---|
| Solution 1 | Jonathan |
| Solution 2 |