Why set a shared lock when duplicate-key error occurs during insert

Viewed 200

As Locks Set by Different SQL Statements in InnoDB, INSERT section said,

INSERT sets an exclusive lock on the inserted row.
Prior to inserting the row, a type of gap lock called an insert intention gap lock is set.

If a duplicate-key error occurs, a shared lock on the duplicate index record is set.

I wonder why set a shared lock, as the subsequent doc said, this might lead to a deadlock. Why not simply request an exclusive lock?

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.


The below example copied from the MySQL document which demonstrate how to trigger the deadlock.

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;
0 Answers
Related