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?
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.