我正在尝试使用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
,现在正在等待由第一个线程持有的行锁(或者更可能是页级锁)。
这是代码中唯一更新此表的位置,并且当前没有获得显式锁。顺序 UPDATE
与 INSERT
似乎是问题的根源。
有几个可能性,我可以想到“修复”这一点。
检测死锁(mysql抛出一个错误)并简单地重试。这是我当前的实现,因为这个问题有点罕见。每天发生几次。
使用 LOCK TABLES
在合并进程和 UNLOCK TABLES
之后。这显然不适用于mariadb galera——这在我们未来的产品中很可能是这样。
将代码更改为“始终发布” INSERT
先查询。这将导致首先获取任何表级锁并避免死锁。
3的问题是,它需要在已经相当复杂的方法中使用更复杂的代码(“合并”操作本身就很复杂)。更复杂的代码也意味着查询的数量增加了一倍( SELECT
若要确定行id是否已存在,请稍后使用另一个 SELECT ... FOR UPDATE
/ UPDATE
实际更新)。此表处于合理的争用量之下,因此如果可能的话,我希望避免发出更多的查询。
有没有办法强迫mysql不使用 LOCK TABLES
? 也就是说,在某种程度上,如果我们搬到加莱拉,会奏效吗?
1条答案
按热度按时间daupos2t1#
我认为您可以通过获取一组行锁和间隙锁来实现自己的目的:
这个
SELECT
query将锁定已经存在的行,并为还不存在的行创建间隙锁。间隙锁将阻止其他事务创建这些行。