13

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
kajetons
  • 4,481
  • 4
  • 26
  • 37
Vadim Babaev
  • 490
  • 1
  • 3
  • 13

1 Answers1

9

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.

Zafar Malik
  • 6,734
  • 2
  • 19
  • 30
  • Looks like I got it. My problem was in the order of rows which I was trying to delete. I sorted it in ascending order and deadlocks were gone. So, it was gap locks that were locking whole table. – Vadim Babaev Apr 21 '14 at 13:32
  • @VadimBabaev what would an example of your initial problem looks like that did not have gap locking here? – rogerdpack Sep 05 '16 at 18:07