问如何强制MySQL获得事务的表锁？

EN

我试图使用MySQL存储引擎对InnoDB数据库表执行操作。此操作是插入或更新类型操作，其中我有一组传入数据，并且表中可能已经有一些必须更新的数据。例如，我可能有一张桌子：

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，只需在ResultSet上调用updateRow方法。为了便于讨论，让我们假设上面的查询实际上是向服务器发出的。)

注:上表是一个简单的例子来说明我的问题。真正的表更复杂，在执行SELECT ... FOR UPDATE__时，我不使用PK作为匹配字段。因此，通过查看传入的数据，记录需要是INSERTed还是UPDATEd并不明显。必须查阅数据库，以确定是否使用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查询。这将导致首先获取任何表级别的锁并避免死锁。

第三种方法的问题是，它将需要一个已经相当复杂的方法中更复杂的代码(“合并”操作本质上是复杂的)。这个更复杂的代码还意味着查询的数量大约是查询数的两倍(SELECT用来确定行id是否已经存在，然后是另一个实际更新它的SELECT ... FOR UPDATE/UPDATE )。这个表有一个合理的争用量，所以如果可能的话，我想避免发出更多的查询。

是否有一种方法可以强迫MySQL在不使用LOCK TABLES的情况下获得表级锁？也就是说，如果我们搬到加雷拉的话就能工作了？