MySQL locking in Duplicate Key Error

Question

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?

Answer 1

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.

I think you are simplifying the terminology/process. After the query is parsed and before it is executed, it needs to acquire necessary locks. It is at this point that it is determined that:

• session 1 gets the exclusive lock, because it is inserting and there are no other locks
• session 2 and 3 get queued for the shared lock because the exclusive lock is already held by session 1, and session 2 and 3 are in a duplicate key error

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

Per above, sessions 2 and 3 get queued for shared locks because they are in duplicate key error. However, when session 1 deletes the key and releases the exclusive lock, now both session 2 and 3 get granted shared locks. At this point both try to acquire an exclusive lock to complete the insert. Neither one can, though, because the other is already holding the shared lock. So exclusive lock is not granted to either and they deadlock.