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

Question

我正在尝试使用InnoDB存储引擎对MySQL数据库表执行操作。此操作是INSERT或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库，只是在ResultSet上调用updateRow方法。为了便于讨论，我们假设上面的查询实际上是向服务器发出的查询。(

注意：上表是一个简单的例子来说明我的问题。实际的表更复杂，并且在执行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——这很可能是我们未来的产品
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查询将锁定已经存在的行，并为尚未存在的行创建间隙锁。间隙锁将阻止其他事务创建这些行。