Why does insert on duplicate-key error create a share lock?

Question

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?

Answer 1

I prefer to look at it from a practical point of view.

• The faster my transactions are, the less likely they are to encounter a locking problem.
• Not all deadlocks are avoidable, so..
• I should always check for errors (such as "deadlock") and be prepared to rerun my transaction.
• InnoDB is very good at discovering deadlocks and quickly rollback'ing one of the the transactions.
• Certain conflicts are "lock wait" conflicts (instead of deadlocks), and will be silently resolved by delaying one transaction until the other is finished.
• When I can't figure out what is happening, I chalk it up to InnoDB being cautious, even to the point of causing more locking than might be needed.

This last item is possibly what you hit -- a complex situation where InnoDB conservatively takes out a stronger lock than is perhaps needed.

Note also, InnoDB wants to be fast. So, even if a complex anti-deadlock algorithm were possible, InnoDB might go with a simpler, faster, but still "safe", algorithm.