Menu
I have an issue with table locking in InnoDB on delete operation. I have a table queue with for example one column and a lot of transactions which can insert rows into this queue or delete them. There isn't any two transactions working with the same rows at the same time. So, all row locks must be distinct. But sometimes when delete operation deletes the most part of rows in the table, InnoDB prefers to use table lock instead of row lock and that causes deadlocks.
I can't reproduce this deadlock exactly, but I found that lock problem. i.e. I have table queue:id with values(1,3,4,5,6,7)
Transaction 1:
insert into queue value(2);
Transaction 2:
delete from queue where id in (1,3,4,5,6,7); -- here the lock comes
720
.. delete sets a write lock to the whole table ...
DELETE uses a row lock while executing, which means each row in the table is locked for deletion. Once DELETE is executed, a table can still contain empty data pages.
InnoDB supports multiple granularity locking which permits coexistence of row locks and table locks. For example, a statement such as LOCK TABLES ... WRITE takes an exclusive lock (an X lock) on the specified table. To make locking at multiple granularity levels practical, InnoDB uses intention locks.
The correct way to use LOCK TABLES and UNLOCK TABLES with transactional tables, such as InnoDB tables, is to begin a transaction with SET autocommit = 0 (not START TRANSACTION ) followed by LOCK TABLES , and to not call UNLOCK TABLES until you commit the transaction explicitly.
But sometimes when delete operation deletes the most part of rows in the table, InnoDB prefers to use table lock instead of row lock and that causes deadlocks. I can't reproduce this deadlock exactly, but I found that lock problem. i.e.
So, all row locks must be distinct. But sometimes when delete operation deletes the most part of rows in the table, InnoDB prefers to use table lock instead of row lock and that causes deadlocks. I can't reproduce this deadlock exactly, but I found that lock problem. i.e.
The DELETE gap lock blocks INSERT statements (which acquire "insert intention" locks), but do not block other DELETE X-locks. 'gap' locks in InnoDB are purely 'inhibitive': they block inserts to the locked gap. But they do not give the holder of the lock any right to insert. Several transactions can own X-lock on the same gap. The reason
A locking read, an UPDATE, or a DELETE generally set record locks on every index record that is scanned in the processing of the SQL statement. It does not matter whether there are WHERE conditions in the statement that would exclude the row. InnoDB does not remember the exact WHERE condition, but only knows which index ranges were scanned.
First of all assuming id is a primary key or at least indexed column.
Insert should not lock the table, so chances are any other update/delete query is executing at same time of deletion the records.
If it is not the case then it can be due to "gap locking" as mentioned @a_horse_with_no_name.
So at which time you get this issue again then you need to store all processes "show full processlist" at your end and also check "show engine innodb status" where it will show you processids related with deadlock, this will help you to get exact problem.
Further You can avoid this locking to delete all rows one by one based on primary key.
179
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
