0

I faced an issue in MySql 5.7 that when I select 14 or more rows with FOR UPDATE a whole table is locked. Even though I select them by primary key (so, it's indexed and unique). Literally it looks like this. When I run this query:

select * from mytable
where id in (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13)
for update;

Everything works fine and only rows selected are locked. But this query:

select * from mytable
where id in (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14)
for update;

locks whole table. I can't update any other entry nor I can insert a new row. The table is locked. Ids can be different what matters is their number (14 or more). I tried to google for it but didn't find anything.

Is it some strict MySql (we use version 5.7) limitation for row lock (13 rows) and if you select more then a table lock is applied? Can it be changed somehow? Or is it only our specific problem and we need to dig deeper?

mykola
  • 1,736
  • 5
  • 25
  • 37
  • 1
    And if you try gap locking like : `select * from mytable where id between 1 and 20 for update` ? Does it lock all the table ? Or removing the `*` `select id from mytable where id between 1 and 20 for update` – Ergest Basha Sep 01 '21 at 13:59
  • @basha04 hmmm... I tried and got interesting results. If I use BETWEEN then it locks only selected rows at least for up to 60 results (didn't try more) which is promising but doesn't solve the problem as I need arbitrary IDs. As for `select id` instead of `select *` it works without locking only for up to 11 rows, on 12-th it locks the table. So, it even less then when using `*` – mykola Sep 01 '21 at 14:50

1 Answers1

0

Looks like nobody knows actual reason. Well, then I can post a solution we eventually came to. We used temporary table which consisted only from one column id where we inserted all required ids and then joined it with our table instead of IN clause:

CREATE TEMPORARY TABLE if not exists tmp (id int primary key);
truncate tmp;
INSERT INTO tmp VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10), (11), (12), (13), (14), (15);
select t.* from mytable t
join tmp on tmp.id = t.id
for update;

This way everything works smoothly and only specified rows are locked no matter how many ids we pass. There are some issues though.

First if we used engine=memory this trick didn't work for some reason. Also if we inserted values into temporary table using select instead of values a further select for update still locked whole table at certain number of ids. But in latter case a problem could be solved by calling optimize table tmp; right after insert (when we used engine=memory it still didn't help).

So, there it is. Hope this will help somebody.

mykola
  • 1,736
  • 5
  • 25
  • 37