could a rows lock made with IN(,,,) generate dead locks?

Question

My goal is to avoid dead locks and so I centralized all locks in the same place ordering by table name and then by ID ascending:

SELECT * FROM table1 WHERE ID = 1 FOR UPDATE
SELECT * FROM table1 WHERE ID = 2 FOR UPDATE
SELECT * FROM table1 WHERE ID = 3 FOR UPDATE
SELECT * FROM table1 WHERE ID = 4 FOR UPDATE

SELECT * FROM table2 WHERE ID = 1 FOR UPDATE
SELECT * FROM table2 WHERE ID = 2 FOR UPDATE
SELECT * FROM table2 WHERE ID = 3 FOR UPDATE
SELECT * FROM table2 WHERE ID = 4 FOR UPDATE

but I wonder if I can do the same using IN() (which is probably a bit faster)

SELECT * FROM table1 WHERE ID IN(1,2,3,4) FOR UPDATE
SELECT * FROM table2 WHERE ID IN(1,2,3,4) FOR UPDATE

will the rows be locked in the exact order specified by the IN() operand or the lock will be applied using the "natural table ordering" instead?

ID is a primary auto_increment field in all tables and I don't "reuse" old deleted IDs (so in theory the natural ordering should always be ascending)

thanks in advance!

added the update:

UPDATE table1 SET t1="hello1" WHERE ID = 1;
UPDATE table1 SET t1="hello2" WHERE ID = 2;
UPDATE table1 SET t1="hello3" WHERE ID = 3;
UPDATE table1 SET t1="hello4" WHERE ID = 4;
UPDATE table2 SET t2="hello1" WHERE ID = 1;
UPDATE table2 SET t2="hello2" WHERE ID = 2;
UPDATE table2 SET t2="hello3" WHERE ID = 3;
UPDATE table2 SET t2="hello4" WHERE ID = 4;
...
COMMIT;

What exactly do you mean by "deadlocks"? Also which problem exactly are you trying to solve by using `SELECT FOR UPDATE`? — Quassnoi, Jun 26 '14 at 17:14
hi, my knowledge and terminology is limited. i have two or more concurrent sessions updating table1 and table2 in the same transaction, i want to lock target rows in a canonical order for all sessions (so i can't have sessions blocking each others) i thought that that would be fine, or it isn't? — , Jun 26 '14 at 17:18
update is very simple, why do you need it? is there something that i'm missing? :P — , Jun 26 '14 at 17:25
I really can't see how "order of locking" (whatever that should mean) is linked to deadlocks. With `IN` or any other condition you'll just lock bunch of rows - not single row as you're doing it in first case. It has nothing to do with "order of locking". MySQL will just lock __all examined rows__ - thus, you'll get lock on group of rows - and you may treat it as atomic thing. The physical "order of locking" is out of the issue since all rows will be locked and released at first `COMMIT`/`ROLLBACK` statement — Alma Do, Jun 27 '14 at 12:25
if you lock a list of tables using a specific order and follow that convention everywhere you wont get deadlocks. read this for example http://stackoverflow.com/questions/10838517 because i'm not the only who tried to do the same http://i.technet.microsoft.com/dynimg/IC156427.gif — , Jun 27 '14 at 19:51
my question is mostly based on speculation since I just started with innodb. I thought that locking tables using always the same order would help to avoid dead locks. am I doing it wrong? — , Jun 28 '14 at 16:21

score 1 · Answer 1 · edited May 23 '17 at 10:32

Ok so this question is not really that clear as to what you want... so this may not be an answer to your question.. but I made some test stuff to help you visualize the data and how IN() works.. so I hope thats at least helpful.

SETUP:

CREATE TABLE table1
    (`id` int, `username` varchar(10), `t1` varchar(55));   
INSERT INTO table1
    (`id`, `username`, `t1`)
VALUES
(4, 'John', 'Hi1'),
(3, 'Ram ', 'Hi2'),
(2, 'Jack', 'Hi3'),
(1, 'Jill', 'Hi4');


CREATE TABLE table2
    (`id` int, `username` varchar(10), `t1` varchar(55));
INSERT INTO table2
    (`id`, `username`, `t1`)
VALUES
(1, 'Joe', 'Hey1'),
(2, 'Fes', 'Hey2'),
(3, 'Ned', 'Hey3'),
(4, 'Abe', 'Hey4');

I made table1 have a backwards ID.. aka 4, 3, 2, 1 and then table2 has the regular incremented id. 1, 2, 3, 4...

1. SELECT * FROM table1

RESULT-OF-1

2. SELECT * FROM table2

RESULT-OF-2

3. SELECT * FROM table1 WHERE id = 1 OR id = 2 OR id = 3 OR id = 4 same result as 1

4. SELECT * FROM table1 WHERE id IN(1, 2, 3, 4).. same result as 1.

5. SELECT * FROM table1 WHERE id IN(1, 4, 3, 2).. same result as 1.

IN() compares the id from each row in the table to what is specified inside the IN() statement.. if it matches it will return the row.. so it returns the data in the "natural table ordering" .. more info HERE

there is a way to do the update you posted with an IN() statement.. without writing out each update.

UPDATE table1 SET t1= CONCAT('hello', ID) WHERE ID IN(1,2,3,4);
UPDATE table2 SET t2= CONCAT('hello', ID) WHERE ID IN(1,2,3,4);

all you do here is combine the 'hello' string with the ID and set your column to it since that is what you posted. I hope that helps understand how the data gets pulled out.

output for the two updates : table1.... table2

for locking tables to update you should probably lock them to WRITE and UNLOCK to prevent a deeplock. see post

LOCK TABLES table1 WRITE, table2 WRITE;
UPDATE table1 SET t1= CONCAT('hello', ID) WHERE ID IN(1,2,3,4);
UPDATE table2 SET t1= CONCAT('hello', ID) WHERE ID IN(1,2,3,4);
UNLOCK TABLES;

score 1 · Accepted Answer · answered Jun 29 '14 at 19:03

Even though it's a little unclear but some part of the answer to your question is stated in the MySQL's documentation:

expr IN (value,...)

Returns 1 if expr is equal to any of the values in the IN list, else returns 0. If all values are constants, they are evaluated according to the type of expr and sorted. The search for the item then is done using a binary search.

Here's what you should get of it: If all values in the list are constants, they are compared sorted using binary search.

So in the end, it doesn't matter if you have sorted the values or not, because MySQL will sort them even if they are not. Nevertheless this wasn't your question. Now let's get back to your question.

First of all, deadlocks are absolutely possible in MySQL when your are using InnoDb and they happen all the time (at least to me). The strategy you've chosen to prevent deadlocks is a valid one (acquiring locks according to some order). But unfortunately I don't think it's going to work in MySQL. You see, even though in your query it is clearly stated which records you want to be locked, but the truth is that they are not the only records that will be locked:

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. The locks are normally next-key locks that also block inserts into the “gap” immediately before the record. However, gap locking can be disabled explicitly, which causes next-key locking not to be used.

So it's hard to say which records are actually locked. Now consider MySQL is searching the index for the first value in your list. As I've just said few more records might be locked along the way while MySQL is scanning the index. And since scanning indices does not happen in order (or at least that's what I believe), records would get locked regardless of their order. Which means that deadlocks are not prevented.

The last part is my own understanding of the situation and I've actually never read that anywhere before. But in theory it sounds right. Yet I really would like someone to prove me wrong (just so I can trust MySQL even more).

score 1 · Answer 3 · edited May 23 '17 at 12:18

1

Rows are locked in the order they are read, so no order is guaranteed. Even if you add an ORDER BY clause, the rows will be locked as they are read, not as they are ordered. Here is another good question with some great answers

edited May 23 '17 at 12:18

Community

1
1

answered Jun 29 '14 at 19:25

Maxim Krizhanovsky

26,265
5
59
89

could a rows lock made with IN(,,,) generate dead locks?

3 Answers3