5

From the docs:

If a duplicate-key error occurs, a shared lock on the duplicate index record is set. This use of a shared lock can result in deadlock should there be multiple sessions trying to insert the same row if another session already has an exclusive lock. This can occur if another session deletes the row.

Going with the example in the docs,

Suppose that an InnoDB table t1 has the following structure:

CREATE TABLE t1 (i INT, PRIMARY KEY (i)) ENGINE = InnoDB;

Now suppose that three sessions perform the following operations in order:

Session 1:

START TRANSACTION;
INSERT INTO t1 VALUES(1);

Session 2:

START TRANSACTION;
INSERT INTO t1 VALUES(1);

Session 3:

START TRANSACTION;
INSERT INTO t1 VALUES(1);

Session 1:

ROLLBACK;

The first operation by session 1 acquires an exclusive lock for the row. The operations by sessions 2 and 3 both result in a duplicate-key error and they both request a shared lock for the row. When session 1 rolls back, it releases its exclusive lock on the row and the queued shared lock requests for sessions 2 and 3 are granted. At this point, sessions 2 and 3 deadlock: Neither can acquire an exclusive lock for the row because of the shared lock held by the other.

I have some questions :

1) The insert query takes an exclusive lock on the row it is inserting. So, suppose T1 is inserting on row 1, it will lock row 1. Now when T2 comes to write, will INNODB evaluate the query before executing it and find out that it is going to write the same PK (row with i = 1) and make T2 wait? Or will it start execution of T2 and find that it gives duplicate key error or PK violation.

2) Why are T2 and T3 taking shared locks? How do shared locks come into picture during insert?

2 Answers 2

1

I did a small simulation of that situation, in a simple bash script that does that:

➜  rsandbox_5_6_30 cat test.sh 
./m -e "START TRANSACTION; INSERT INTO test1 VALUES(6); select  sleep(3); rollback;" test 2> tx1 &
./m -e "START TRANSACTION; INSERT INTO test1 VALUES(6); select sleep(5); commit;" test 2> tx2 &
./m -e "START TRANSACTION; INSERT INTO test1 VALUES(6); select sleep(5); commit;" test 2> tx3 &

./m -e "SHOW ENGINE INNODB STATUS\G" test > istatus

Result:

➜  rsandbox_5_6_30 cat tx1 
ERROR 1062 (23000) at line 1: Duplicate entry '6' for key 'PRIMARY'
➜  rsandbox_5_6_30 cat tx2
ERROR 1062 (23000) at line 1: Duplicate entry '6' for key 'PRIMARY'
➜  rsandbox_5_6_30 cat tx3

Transaction status:

---TRANSACTION 265035, not started
MySQL thread id 4, OS thread handle 0x700000b0b000, query id 204 localhost msandbox cleaning up
---TRANSACTION 265017, not started
MySQL thread id 3, OS thread handle 0x700000ac7000, query id 171 localhost msandbox cleaning up
---TRANSACTION 265041, ACTIVE 0 sec
1 lock struct(s), heap size 360, 0 row lock(s), undo log entries 1
MySQL thread id 31, OS thread handle 0x700000ca3000, query id 210 localhost msandbox User sleep
select sleep(5)

If you execute the first transaction within certain delay, you can incur on a frame where one of the subsequent transactions is killed by deadlock detection, making the other transaction to insert the value.

The status of the alive transaction reflects that still needs an explicit commit (undo log entry 1).

-- Edited 2nd

"Neither can acquire an exclusive lock for the row because of the shared lock held by the other." Actually the deadlock kills only 1 transaction, not both. So the EL is acquired by the 2nd session.

Due to how gap locking works we see the difference between executions. The deadlock happens as an intention of the 3rd transaction to acquire the shared lock when a duplicate key error is detected. When this happens, the 2nd transactions has the shared lock acquired, producing a deadlock and killing the 3rd transaction. This is a limitation in the amount of concurrent writes over the same record.

5
  • Exactly. That's what I was saying in my question. My question is to why it goes to deadlock in the first place Jun 27, 2016 at 15:53
  • Added some comments, the response was too long for the answer's field. @PriyanshGoel
    – 3manuek
    Jun 27, 2016 at 21:06
  • 1
    Deadlock detection should NOT happen wit a delay, it is done by analyzing the waiting graph. Deadlock always kills one of the transaction, that way the offending lock is freed and the second one can continue, but until that happens, neither can get the X lock - that is the definition of deadlock.
    – jkavalik
    Jun 28, 2016 at 16:06
  • 1
    Yes, I didn't want to mean what it should be. It is how it is working now in the tests, meaning that if we execute with a different timing the 3 of the transactions, we get different results (all together have Duplicate Key Error, and deadlock triggering when executing the 3 transactions with n delay in between) . The current approach is that when a transaction has a duplicate key error, they try to acquire the shared lock (and yes, that's when deadlock happens). I will edit the response to do the clarification, thanks @jkavalik!
    – 3manuek
    Jun 28, 2016 at 16:32
  • @3manuek : Where does shared lock come into picture? Shouldn't it be IX lock? Jul 2, 2016 at 14:51
0

Question 2:

2) Why are T2 and T3 taking shared locks? How do shared locks come into picture during insert?


It requires a lock on the existing entry so that subsequent attempts to insert a duplicate record fail consistently。

1
  • I think the question is not just about the "why" but also (or perhaps even principally) about "why shared locks" (and not any other kind of locks).
    – Andriy M
    Aug 26, 2016 at 5:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.