如何强制mysql为事务获取表锁?

mi7gmzs6  于 2021-06-19  发布在  Mysql
关注(0)|答案(1)|浏览(391)

我正在尝试使用innodb存储引擎对mysql数据库表执行一个操作。此操作是插入或更新类型的操作,其中我有一组传入的数据,并且表中可能已经有一些必须更新的数据。例如,我可能有以下表格:

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库for java,只需调用 updateRow 方法 ResultSet . 为了便于讨论,让我们假设上面的查询实际上是向服务器发出的查询。)
注:上表只是一个简单的例子来说明我的问题。真正的表更复杂,我不使用pk作为执行时要匹配的字段 SELECT ... FOR UPDATE . 因此,仅仅通过查看传入的数据就不清楚是否需要插入或更新记录。必须查阅数据库以确定是否使用插入/更新。
以上查询在大多数情况下都可以正常工作。但是,当有更多的记录要“合并”时 SELECT ... FOR UPDATE 以及 INSERT 行可以交错,我无法预测 SELECT ... FOR UPDATE 或者 INSERT 将按什么顺序发布。
结果是,有时事务会死锁,因为一个线程已为该线程锁定了表的一部分 UPDATE 正在等待表锁(对于 INSERT ,而另一个线程已经获得了主键的表锁(可能是因为它发出了 INSERT ,现在正在等待由第一个线程持有的行锁(或者更可能是页级锁)。
这是代码中唯一更新此表的位置,并且当前没有获得显式锁。顺序 UPDATEINSERT 似乎是问题的根源。
有几个可能性,我可以想到“修复”这一点。
检测死锁(mysql抛出一个错误)并简单地重试。这是我当前的实现,因为这个问题有点罕见。每天发生几次。
使用 LOCK TABLES 在合并进程和 UNLOCK TABLES 之后。这显然不适用于mariadb galera——这在我们未来的产品中很可能是这样。
将代码更改为“始终发布” INSERT 先查询。这将导致首先获取任何表级锁并避免死锁。

3的问题是,它需要在已经相当复杂的方法中使用更复杂的代码(“合并”操作本身就很复杂)。更复杂的代码也意味着查询的数量增加了一倍( SELECT 若要确定行id是否已存在,请稍后使用另一个 SELECT ... FOR UPDATE / UPDATE 实际更新)。此表处于合理的争用量之下,因此如果可能的话,我希望避免发出更多的查询。

有没有办法强迫mysql不使用 LOCK TABLES ? 也就是说,在某种程度上,如果我们搬到加莱拉,会奏效吗?

daupos2t

daupos2t1#

我认为您可以通过获取一组行锁和间隙锁来实现自己的目的:

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

相关问题