插入行，如果不存在，则不存在死锁

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生成一次以上的col = 4。

我试过：

1. SELECT .. (没有FOR UPDATE或LOCK IN SHARE MODE)： 这两个事务都看到没有带有col = 4的行(没有获得锁)，并且都生成data并使用col = 4插入行的两个副本。
2. SELECT .. LOCK IN SHARE MODE 这两个事务都获取col = 4上的共享锁，生成data，并尝试插入带有col = 4的行。这两个事务都在等待另一个事务释放它们的共享锁，以便它能够INSERT，从而导致ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction。
3. SELECT .. FOR UPDATE 我希望一个事务的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)

Answer 2

也许这个..。

START TRANSACTION;
INSERT IGNORE INTO test (col, data) VALUES (4, NULL);  -- or ''
-- if Rows_affected() == 0, generate data and replace `data`
    UPDATE test SET data = 'data' WHERE col = 4;
COMMIT;

警告:如果PRIMARY KEY是一个AUTO_INCREMENT，这可能会“烧掉”一个id。

Answer 3

注意，InnoDB有两种类型的独占锁:一种用于更新和删除，另一种用于插入。因此，要执行SELECT FOR UPDATE事务，InnoDB首先必须在一个事务中获取用于更新的锁，然后第二个事务将尝试使用相同的锁，并阻止等待第一个事务(它不可能如您在问题中所说的那样成功)，然后当第一个事务尝试执行insert时，它将不得不将其锁从用于更新的锁更改为INSERT锁。InnoDB能够做到的唯一方法是首先将锁降级为共享锁，然后将其升级回锁定以供插入。而且，当另一个事务也在等待获取独占锁时，它也不能降低锁的级别。这就是为什么在这种情况下会出现死锁错误。

正确执行此操作的唯一方法是在col上具有唯一的索引，尝试使用col =4插入行(如果您不想在插入之前生成虚拟数据)，然后在重复键错误回滚的情况下，如果INSERT成功，您可以用正确的数据更新行。但是请注意，如果您不想不必要地生成数据，这可能意味着生成它需要很长时间，而所有这些时间，您都将持有一个打开的事务，该事务插入的行将包含所有试图插入同一行挂起的其他进程。我不确定这是否比先生成数据然后插入数据要好得多。