In the mysql docs is the following example for locking on insert:
INSERT sets an exclusive lock on the inserted row. [...] 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 is proceeded with the following example:
CREATE TABLE t1 (i INT, PRIMARY KEY (i)) ENGINE = InnoDB;
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 following would happen:
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.
This makes me wonder, why does one first require a share lock just to proceed with an exclusive lock? Would it not make sense to change the behavior to:
INSERT sets an exclusive lock on the inserted row. [...] If a duplicate-key error occurs, an exclusive lock on the duplicate index record is set.
If one would handle it like this, then in the above example, there would be no deadlock and either session 2 or session 3 (depending who gets the exclusive lock first) would try to proceed. Assume session 2 would lock first and then fail and rollbacks aswell, then the session 3 would try to insert. Would that not make much more sense?