mysql - 如果不存在则插入行而不会出现死锁

Question

我有一个简单的表格

CREATE TABLE test (
  col INT,
  data TEXT,
  KEY (col)
);

和一个简单的交易

START TRANSACTION;

SELECT * FROM test WHERE col = 4 FOR UPDATE;

-- If no results, generate data and insert
INSERT INTO test SET col = 4, data = 'data';

COMMIT;

我试图确保同时运行此事务的两个副本不会导致重复行和死锁。我也不想承担多次为 col = 4 生成 data 的成本。

我试过:

1. SELECT ..(没有FOR UPDATE 或LOCK IN SHARE MODE):

   两个事务都看到没有带有 col = 4 的行(没有获取锁)并且都生成 data 并插入带有 的行的两个副本col = 4.

2. 选择 .. 锁定共享模式

   两个事务都在 col = 4 上获取共享锁，生成 data 并尝试插入一行 col = 4。两个事务都等待对方释放它们的共享锁，以便它可以 INSERT，导致 ERROR 1213 (40001): Deadlock found when trying to get lock；尝试重新启动事务。

3. 选择 .. 进行更新

   我期望一个事务的 SELECT 会成功并获得对 col = 4 和另一个事务的 SELECT 的独占锁 将阻塞等待第一个。

   相反，SELECT .. FOR UPDATE 查询都会成功，并且事务会继续死锁，就像 SELECT .. LOCK IN SHARE MODE 一样。 col = 4 上的独占锁似乎不起作用。

如何在不导致重复行和死锁的情况下编写此事务？

Answer 1

稍微调整您的模式:

CREATE TABLE test (
  col INT NOT NULL PRIMARY KEY,
  data TEXT
);

col 是主键，它不能被复制。

然后使用 ON DUPLICATE KEY特点:

INSERT INTO test (col, data) VALUES (4, ...)
  ON DUPLICATE KEY UPDATE data=VALUES(data)