时间戳

首页 > 解决方案 > 如何强制 MySQL 为事务获取表锁？

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

问题描述

我正在尝试使用 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 的用于 Java 的 Connector/J 库，并且只是updateRow在 a 上调​​用该方法ResultSet。为了争论，让我们假设上面的查询实际上是什么发给服务器。）

注意：上表是一个简单的例子来说明我的问题。真正的表比较复杂，我在执行时没有使用PK作为匹配的字段SELECT ... FOR UPDATE。因此，仅通过查看传入数据就不清楚是否需要插入或更新记录。必须咨询数据库以确定是否使用 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？也就是说，如果我们搬到加莱拉，某种方式会奏效吗？

标签： mysqllockingrowlocking

解决方案

我认为您可以通过获取一组行锁和间隙锁来做您想做的事情：

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查询将锁定已存在的行，并为尚不存在的行创建间隙锁。间隙锁将阻止其他事务创建这些行。

推荐阅读