mysql - 如果我们依赖语句中的索引扫描顺序，两个 InnoDB UPDATE 语句是否可以防止 PK 索引死锁？

Question

如果给我们一张 table :

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)

当发出在 WHERE 子句中使用主键的 UPDATE 语句时，InnoDB 使用索引记录锁，如 https://dev.mysql.com/doc/refman/8.0/en/innodb-locking.html#innodb-gap-locks 中所述。 。也就是说，它单独锁定每一行。

基于此，我们可以通过以相反的顺序为主键 1 和 2 发出 UPDATE 来说明两个事务之间的简单死锁:

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

最后是问题。如果我们使用 IN 作为主键值列表将这些 UPDATE 语句写为单个语句，那么不同事务中的这两个 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 (...) 必须自动处理所有有问题的 id。这与第一个示例不同，在第一个示例中，很明显，一次锁定一个行。

过去，最好对 IN 列表进行排序。但我认为 MySQL 现在可以对它们进行排序。

可能存在一个阈值，超过该阈值它就会进行扫描，而不是单独到达每个 id。据称，这导致锁定 IN 列表中未提及的行。