What causes GAP lock in MySQL update statement?
In your first UPDATE statement, it will hold the gap locks before & after SOMEID. This will prevent insert into these gaps from other transactions. The two different transactions can hold gap lock on same gap, but each transaction will block other’s insert statement. That’s why the deadlock occurs.
How does gap lock deadlock on inserts-database work?
I’m not a MySQL expert, but by the look of your Deadlock logs, even though you are INSERTing different vehicle IDs per statement, those require the whole datapage (238326) of the VehicleID non-clustered index to be locked.
Why are there deadlocks on MySQL inserts database?
The fact you are occasionally getting deadlocks means that within 1 page you have multiple vehicle IDs, so there is a small chance that 2 different processes will need a lock for the same page. Best thing to advise is to keep your transactions as small as possible.
When to enable or disable gap locking in InnoDB?
Gap locking can be disabled explicitly. This occurs if you change the transaction isolation level to READ COMMITTED. Under these circumstances, gap locking is disabled for searches and index scans and is used only for foreign-key constraint checking and duplicate-key checking.
How are locks set in MySQL 5.7 reference manual?
Separate transactions that attempt to insert values of 5 and 6 each lock the gap between 4 and 7 with insert intention locks prior to obtaining the exclusive lock on the inserted row, but do not block each other because the rows are nonconflicting. If a duplicate-key error occurs, a shared lock on the duplicate index record is set.
How are locks set in MySQL transaction isolation?
FROM is a consistent read, reading a snapshot of the database and setting no locks unless the transaction isolation level is set to SERIALIZABLE. For SERIALIZABLE level, the search sets shared next-key locks on the index records it encounters.