Могут ли два оператора InnoDB UPDATE против взаимоблокировки индекса PK, если мы будем полагаться на порядок сканирования индексов в операторе?

Question

Если нам дают таблицу:

MariaDB [test]> create table foo (
    -> id integer primary key,
    -> version_id integer);
Query OK, 0 rows affected (0.05 sec)

и две строки с первичным ключом 1 и 2:

MariaDB [test]> insert into foo (id, version_id) values(1, 1);
Query OK, 1 row affected (0.01 sec)

MariaDB [test]> insert into foo (id, version_id) values(2, 1);
Query OK, 1 row affected (0.00 sec)

При отправке оператора UPDATE, который использует первичный ключ в предложении WHERE, InnoDB использует блокировку записи индекса, как описано в https://dev.mysql.com/doc/refman/8.0/en/innodb-locking.html. То есть он блокирует каждый ряд индивидуально.

Исходя из этого, мы можем проиллюстрировать простой тупик между двумя транзакциями, выдав UPDATE для первичных ключей 1 и 2 в обратном порядке:

transaction 1 # MariaDB [test]> begin;
transaction 1 # Query OK, 0 rows affected (0.00 sec)

transaction 2 # MariaDB [test]> begin;
transaction 2 # Query OK, 0 rows affected (0.00 sec)

transaction 1 # MariaDB [test]> update foo set 
                    -> version_id=version_id+1 where id=1;
transaction 1 # Query OK, 1 row affected (0.01 sec)
transaction 1 # Rows matched: 1  Changed: 1  Warnings: 0

transaction 2 # MariaDB [test]> update foo set 
                    -> version_id=version_id+1 where id=2;
transaction 2 # Query OK, 1 row affected (0.01 sec)
transaction 2 # Rows matched: 1  Changed: 1  Warnings: 0

transaction 1 # MariaDB [test]> update foo set 
                    -> version_id=version_id+1 where id=2;
<blocks on index lock created by transaction 2 on id=2>

transaction 2 # MariaDB [test]> update foo set 
                    -> version_id=version_id+1 where id=1;
transaction 2 # ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

<wakes up>
transaction 1 # Query OK, 1 row affected (22.24 sec)
transaction 1 # Rows matched: 1  Changed: 1  Warnings: 0

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

transaction 1 # MariaDB [test]> update foo set 
                    -> version_id=version_id+1 
                    -> where id in (1, 2);
transaction 1 # Query OK, 2 rows affected (0.00 sec)
transaction 1 # Rows matched: 2  Changed: 2  Warnings: 0


transaction 2 # MariaDB [test]> update foo set 
                    -> version_id=version_id+1 
                    -> where id in (2, 1);
# note it blocked until the other transaction was done
transaction 2 # Query OK, 2 rows affected (6.28 sec) 
transaction 2 # Rows matched: 2  Changed: 2  Warnings: 0

Answer 1

Ваш первый пример - классический пример тупика.

Ваш второй пример (с IN) это начало демонстрации innodb_lock_wait_timeout, в этом случае одно соединение может подождать и не должно блокироваться.

WHERE id IN (...) должен атомарно обрабатывать все идентификаторы. Это не похоже на первый пример, когда ясно, что строки блокируются по одной за раз.

Раньше было так, что было лучше сортировать IN списки. Но я думаю, что MySQL сортирует их сейчас.

Там может быть порог, выше которого он выполняет сканирование вместо достижения для каждого идентификатора в отдельности. Это якобы привело к блокировке строк, которые не упоминаются в IN список.