Может ли блокировка строк, созданная с помощью IN(,,,), генерировать мертвые блокировки?

Question

Моя цель - избежать мертвых блокировок, поэтому я централизовал все блокировки в одном и том же месте, упорядочивая по имени таблицы, а затем по возрастанию идентификатора:

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

но мне интересно, могу ли я сделать то же самое с помощью IN() (что, вероятно, немного быстрее)

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

будут ли строки заблокированы в точном порядке, указанном операндом IN (), или вместо этого будет применена блокировка с использованием "естественного упорядочения таблиц"?

ID является основным полем auto_increment во всех таблицах, и я не "повторно" использую старые удаленные идентификаторы (поэтому в теории естественный порядок всегда должен быть возрастающим)

заранее спасибо!

---

добавил обновление:

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;

Answer 1

Хотя это немного неясно, но некоторая часть ответа на ваш вопрос изложена в документации MySQL:

expr IN (значение,...)

Возвращает 1, если expr равен любому из значений в списке IN, иначе возвращает 0. Если все значения являются константами, они оцениваются в соответствии с типом expr и сортируются. Поиск элемента затем выполняется с помощью бинарного поиска.

Вот что вы должны получить: если все значения в списке являются константами, они сравниваются, отсортированные с помощью бинарного поиска.

Таким образом, в конце концов, не имеет значения, отсортировали ли вы значения или нет, потому что MySQL отсортирует их, даже если это не так. Тем не менее это был не твой вопрос. Теперь вернемся к вашему вопросу.

Прежде всего, в MySQL абсолютно возможны взаимоблокировки, когда вы используете InnoDb, и они случаются постоянно (по крайней мере, для меня). Стратегия, которую вы выбрали для предотвращения взаимных блокировок, является действительной (получение блокировок в соответствии с определенным порядком). Но, к сожалению, я не думаю, что это сработает в MySQL. Вы видите, хотя в вашем запросе четко указано, какие записи вы хотите заблокировать, но правда в том, что они не единственные записи, которые будут заблокированы:

Блокировка чтения, UPDATE или DELETE обычно устанавливают блокировки записи для каждой индексной записи, которая сканируется при обработке оператора SQL. Неважно, есть ли в выражении WHERE условия, исключающие строку. InnoDB не помнит точное условие WHERE, а только знает, какие диапазоны индекса были отсканированы. Замки, как правило, являются замками следующего ключа, которые также блокируют вставки в "пробел" непосредственно перед записью. Тем не менее, блокировка пробела может быть отключена явно, что приводит к тому, что блокировка следующего ключа не будет использоваться.

Поэтому сложно сказать, какие записи на самом деле заблокированы. Теперь рассмотрим, что MySQL ищет в индексе первое значение в вашем списке. Как я только что сказал, в процессе сканирования индекса может быть заблокировано еще несколько записей. А поскольку сканирование индексов происходит не по порядку (или, по крайней мере, так я считаю), записи будут блокироваться независимо от их порядка. Это означает, что взаимоблокировки не предотвращены.

Последняя часть - это мое собственное понимание ситуации, и я никогда не читал этого раньше. Но в теории это звучит правильно. Тем не менее, я действительно хотел бы, чтобы кто-то доказал мою неправоту (просто чтобы я мог доверять MySQL еще больше).

Answer 2

Строки блокируются в порядке их чтения, поэтому порядок не гарантируется. Даже если вы добавите ORDER BY предложение, строки будут заблокированы, как они читаются, а не как они упорядочены. Вот еще один хороший вопрос с некоторыми отличными ответами

Answer 3

Итак, этот вопрос не совсем ясен относительно того, что вы хотите... так что, возможно, это не ответ на ваш вопрос... но я сделал несколько тестовых заданий, чтобы помочь вам визуализировать данные и как работает IN().. так Я надеюсь, что это по крайней мере полезно.

НАСТРОИТЬ:

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');

я сделал table1 иметь обратный идентификатор.. ака 4, 3, 2, 1, а затем table2 имеет обычный увеличенный идентификатор. 1, 2, 3, 4...

1. SELECT * FROM table1 

РЕЗУЛЬТАТ-OF-1

2. SELECT * FROM table2 

РЕЗУЛЬТАТ-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() сравнивает идентификатор из каждой строки в таблице с тем, что указано в операторе IN().. если он совпадает, он возвращает строку.. поэтому он возвращает данные в "естественном порядке таблицы" .. подробнее ВОТ

есть способ сделать опубликованное вами обновление с помощью оператора IN(), не выписывая каждое обновление.

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);

все, что вы здесь делаете, это объединяете строку 'hello' с ID и установите в нем свой столбец, поскольку именно это вы и опубликовали. Я надеюсь, что это помогает понять, как данные извлекаются.

вывод для двух обновлений: таблица1.... таблица2

для блокировки таблиц для обновления вы, вероятно, должны заблокировать их на WRITE и UNLOCK, чтобы предотвратить глубокую блокировку. см пост

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;