mysql - 如何强制 MySQL 获取事务的表锁？

Question

我正在尝试使用 InnoDB 存储引擎对 MySQL 数据库表执行操作。此操作是一个 INSERT-or-UPDATE 类型的操作，其中我有一组传入的数据，并且表中可能已有一些数据必须更新。例如，我可能有这个表:

test_table
+-------+--------------+------+-----+---------+----------------+
| Field | Type         | Null | Key | Default | Extra          |
+-------+--------------+------+-----+---------+----------------+
| id    | int(11)      | NO   | PRI | NULL    | auto_increment |
| value | varchar(255) | NO   |     | NULL    |                |
+-------+--------------+------+-----+---------+----------------+

...以及一些示例数据:

+----+-------+
| id | value |
+----+-------+
|  1 | foo   |
|  2 | bar   |
|  3 | baz   |
+----+-------+

现在，我想“合并”以下值:

2, qux
4, corge

我的代码最终发出以下查询:

BEGIN;
SELECT id, value FROM test WHERE id=2 FOR UPDATE;
UPDATE test SET id=2, value='qux' WHERE id=2;
INSERT INTO test (id, value) VALUES (4, 'corge');
COMMIT;

(我不太确定 SELECT ... FOR UPDATE 和 UPDATE 会发生什么，因为我正在使用 MySQL 的 Connector/J Java 库，并且只是在 updateRow 上调用 ResultSet 方法。对于为了便于讨论，我们假设上面的查询实际上是向服务器发出的查询。)

注意:上表是一个简单的例子来说明我的问题。真实的表更复杂，我在执行SELECT ... FOR UPDATE时没有使用PK作为匹配的字段。因此，仅通过查看传入的数据并不清楚记录是否需要插入或更新。必须查阅数据库来确定是否使用 INSERT/UPDATE。

上述查询在大多数情况下都可以正常工作。然而，当有更多记录需要“合并”时，SELECT ... FOR UPDATE和INSERT行可以交错，我无法预测是否 SELECT ... FOR UPDATE或INSERT将发布以及按什么顺序发布。

结果是有时事务会死锁，因为一个线程锁定了UPDATE表的一部分。操作并正在等待表锁(对于 INSERT ，它需要主键索引上的锁)，而另一个线程已经获得了表锁主键(大概是因为它发出了 INSERT 查询)，现在正在等待第一个线程持有的行锁(或更可能是页级锁)。

这是代码中唯一更新此表的位置，并且当前没有获取显式锁定。 UPDATE 的排序与 INSERT似乎是问题的根源。

我可以想到几种可能性来“解决”这个问题。

1. 检测死锁(MySQL 抛出错误)并简单地重试。这是我当前的实现，因为问题很少见。这种情况每天会发生几次。
2. 使用LOCK TABLES在合并过程之前获取表锁和 UNLOCK TABLES之后。这显然不适用于 MariaDB Galera——我们 future 的产品可能会采用这种方式。
3. 将代码更改为始终发出 INSERT先查询。这将导致首先获取任何表级锁并避免死锁。

#3 的问题在于，它在已经相当复杂的方法中需要更复杂的代码(“合并”操作本质上是复杂的)。更复杂的代码还意味着查询数量大约增加一倍( SELECT 来确定行 ID 是否已经存在，然后再使用另一个 SELECT ... FOR UPDATE/UPDATE 来实际更新它)。该表存在一定程度的争用，因此我希望尽可能避免发出更多查询。

有没有办法强制MySQL获取表级锁而不使用LOCK TABLES ？也就是说，如果我们搬到加莱拉，这种方式会起作用吗？

Answer 1

我认为您可以通过获取一组行锁和间隙锁来完成您想要的操作:

START TRANSACTION;
SELECT id, value
FROM test
WHERE id in (2, 4) -- list all the IDs you need to UPSERT
FOR UPDATE;
UPDATE test SET value = 'qux' WHERE id = 2;
INSERT INTO test (id, value) VALUES (4, 'corge');
COMMIT;

SELECT 查询将锁定已存在的行，并为尚不存在的行创建间隙锁。间隙锁将阻止其他事务创建这些行。