sql - 当行不存在时， "SELECT FOR UPDATE"是否会阻止其他连接插入？

Question

我感兴趣的是 SELECT FOR UPDATE 查询是否会锁定不存在的行。

例子

表 FooBar 有两列，foo 和 bar，foo 有一个唯一索引.

• 发出查询 SELECT bar FROM FooBar WHERE foo = ?更新
• 如果第一个查询返回零行，发出一个查询
  INSERT INTO FooBar (foo, bar) 值 (?, ?)

现在 INSERT 是否有可能导致索引违规，或者 SELECT FOR UPDATE 是否可以阻止这种情况？

对 SQLServer (2005/8)、Oracle 和 MySQL 上的行为感兴趣。

Answer 1

MySQL

SELECT ... FOR UPDATE with UPDATE

使用 InnoDB 事务(关闭自动提交)，SELECT ... FOR UPDATE 允许一个 session 临时锁定一个(或多个)特定记录，以便其他 session 无法更新它。然后，在同一事务中， session 实际上可以对同一记录执行 UPDATE 并提交或回滚事务。这将允许您锁定记录，以便在您执行其他一些业务逻辑时没有其他 session 可以更新它。

这是通过锁定来实现的。 InnoDB 利用索引来锁定记录，因此锁定现有记录似乎很容易——只需锁定该记录的索引即可。

SELECT ... FOR UPDATE with INSERT

但是，要将 SELECT ... FOR UPDATE 与 INSERT 一起使用，您如何为尚不存在的记录锁定索引？如果您使用的是 REPEATABLE READ 的默认隔离级别，InnoDB 也将使用 gap 锁。只要您知道要锁定的 id(或什至 id 范围)，InnoDB 就可以锁定间隙，这样在我们完成它之前，其他记录都不能插入该间隙。

如果您的 id 列是一个自动递增列，那么 SELECT ... FOR UPDATE 和 INSERT INTO 就会有问题，因为您在您插入之前不会知道新的 id 是什么。但是，由于您知道要插入的 id，因此使用 INSERT 的 SELECT ... FOR UPDATE 将起作用。

警告

在默认隔离级别上，SELECT ... FOR UPDATE 对不存在的记录不会阻止其他事务。因此，如果两个事务都对同一个不存在的索引记录执行 SELECT ... FOR UPDATE，它们都将获得锁，并且两个事务都无法更新记录。事实上，如果他们尝试，就会检测到死锁。

因此，如果您不想处理死锁，您可能只需执行以下操作:

插入...

开始交易，并执行INSERT。执行您的业务逻辑，然后提交或回滚事务。一旦您在第一个事务的不存在的记录索引上执行 INSERT，所有其他事务如果尝试 INSERT 具有相同唯一索引的记录，它们将被阻止.如果第二个事务在第一个事务提交插入后尝试插入具有相同索引的记录，那么它将得到“重复键”错误。相应地处理。

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

如果您在 INSERT 之前使用 LOCK IN SHARE MODE 选择，如果先前的事务已插入该记录但尚未提交，则 SELECT 。 .. LOCK IN SHARE MODE 将阻塞，直到上一个交易完成。

因此，为了减少重复键错误的可能性，特别是如果您在执行业务逻辑时持有锁一段时间，然后再提交或回滚它们:

1. SELECT bar FROM FooBar WHERE foo = ?锁定更新
2. 如果没有返回记录，则
3. INSERT INTO FooBar (foo, bar) VALUES (?, ?)