Contents
What are gap locks?
Gap Locks. A gap lock is a lock on a gap between index records, or a lock on the gap before the first or after the last index record. For example, SELECT c1 FROM t WHERE c1 BETWEEN 10 and 20 FOR UPDATE; prevents other transactions from inserting a value of 15 into column t.
What is row level locking in MySQL?
If the tables use InnoDB, MySQL automatically uses row level locking so that multiple transactions can use same table simultaneously for read and write, without making each other wait. Row level locking also can be obtained by using SELECT FOR UPDATE statement for each rows expected to be modified.
Does a transaction lock tables?
A transaction acquires a table lock when a table is modified in the following DML statements: INSERT , UPDATE , DELETE , SELECT with the FOR UPDATE clause, and LOCK TABLE .
What is the meaning of locks Rec but not gap waiting?
About the meaning of locks rec but not gap waiting in TRANSACTION (1), which one is correct? Already granted gap lock, waiting for clustered index X lock? Already granted clustered index X lock, waiting for gap lock? There are 31 rows in Transaction (1). What is the meaning of those rows? Does this represent a gap lock?
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.
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.
What does record and gap mean in MySQL?
You can read more about record and gap locks here: https://dev.mysql.com/doc/refman/5.7/en/innodb-locking.html#innodb-record-locks The exclusive lock request is coming from the “for update” clause added to the SELECT statement.