Re: Gap lock failure

[Henrique Garcia]

Hi Eve!

Gap locks prevent duplication of PRIMARY KEYs in MySQL/InnoDB.
The PRIMARY KEY is always protected by a unique index, and InnoDB applies a record lock to the key value during INSERT. If two transactions attempt to insert the same ID, one will succeed and the other will fail with a duplication error (1062).

Gap locks only serve to prevent phantom reads, locking ranges of values ​​in range conditions (<, >, BETWEEN).

They do not apply to exact equality (=) and do not control uniqueness.

If duplicates exist, it's not a gap lock failure, but rather:

. actual absence of a PRIMARY KEY
. old replication error
. or application logic ignoring errors

Can you provide the database version and the structure of the table that accepted the duplicate?