插入行（如果不存在而没有死锁）

稍微调整架构：

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)

也许这个......

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;

注意：如果 是 ，这可能会“烧毁”一个 id。PRIMARY KEYAUTO_INCREMENT

请注意，InnoDB 有 2 种类型的独占锁：一种用于更新和删除，另一种用于插入。因此，要执行您的 SELECT FOR UPDATE 事务，InnoDB 必须首先在一个事务中获取更新锁，然后第二个事务将尝试获取相同的锁并阻止等待第一个事务（它不可能像您在问题中声称的那样成功），然后当第一个事务尝试执行 INSERT 时，它必须将其锁从 update 的锁更改为插入。InnoDB可以做到这一点的唯一方法是首先将锁定降级为共享锁定，然后将其升级回锁定以进行插入。当有另一笔交易等待获取独占锁时，它不能降级锁。这就是为什么在这种情况下会出现死锁错误的原因。

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

如果您的目标是只有一个会话插入缺失的行，而任何其他会话甚至不尝试插入 DATA 就不执行任何操作，那么您需要锁定整个表（这会降低您的并发性）或插入一个不完整的行并对其进行更新。

A.在列 COL 上创建主键

法典：

begin
  insert into test values (4,null);
  update test set data = ... where col = 4;
  commit;
exception
  when dup_val_on_index then
    null;
end;

尝试在列 4 上插入的第一个会话将成功并进入更新，您可以在其中对 DATA 进行昂贵的计算。尝试执行此操作的任何其他会话都将引发 PK 冲突（-00001 或 DUP_VAL_ON_INDEX），并转到捕获它并且不执行任何操作的异常处理程序 （NULL） 。它永远不会到达更新语句，因此不会做任何昂贵的事情来计算 DATA。

现在，这将导致另一个会话等待，而第一个会话计算 DATA 并执行更新。如果不想等待，可以使用 NOWAIT 使滞后会话在行被锁定时立即引发异常。如果该行不存在，也会引发异常，但会引发不同的异常。对普通代码分支使用异常处理不是很好，但是嘿，它应该可以工作。

declare
  var_junk number;
begin
  begin
    select col into var_junk from test where col = 4 for update nowait;
  exception
    when no_data_found then
      insert into test values (col,null);
      update test set data = ... where col = 4;
      commit;
    when others then
      null;
  end;
end;
  

然后使用：for update

• 将隔离改为，可避免间隙锁定READ COMMITED
• 或将隔离更改为SERIALIZABLE

这是现在使用的查询，即使在峰值负载下也能完美运行，

INSERT INTO users (users_email, users_name) SELECT * FROM (SELECT "[email protected]" as abc, "user" as abd) AS tmp WHERE NOT EXISTS ( SELECT users_id FROM users WHERE users_email = "[email protected]" ) LIMIT 1;