是否“SELECT FOR UPDATE"当该行不存在时阻止其他连接插入? [英] Does "SELECT FOR UPDATE" prevent other connections inserting when the row is not present?

问题描述

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

I'm interested in whether a SELECT FOR UPDATE query will lock a non-existent row.

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

Table FooBar with two columns, foo and bar, foo has a unique index.

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

• Issue query SELECT bar FROM FooBar WHERE foo = ? FOR UPDATE
• If the first query returns zero rows, issue a query
  INSERT INTO FooBar (foo, bar) values (?, ?)

现在有可能 INSERT 会导致 索引违规 还是 SELECT FOR UPDATE 阻止这种情况发生?

Now is it possible that the INSERT would cause an index violation or does the SELECT FOR UPDATE prevent that?

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

Interested in behavior on SQLServer (2005/8), Oracle and MySQL.

推荐答案

在 Oracle 中，SELECT ... FOR UPDATE 对不存在的行没有影响(该语句只是引发 No Data Found 异常).INSERT 语句将防止唯一/主键值的重复.任何其他尝试插入相同键值的事务都将阻塞，直到第一个事务提交(此时阻塞的事务将出现重复键错误)或回滚(此时阻塞的事务继续).

In Oracle, the SELECT ... FOR UPDATE has no effect on a non-existent row (the statement simply raises a No Data Found exception). The INSERT statement will prevent a duplicates of unique/primary key values. Any other transactions attempting to insert the same key values will block until the first transaction commits (at which time the blocked transaction will get a duplicate key error) or rolls back (at which time the blocked transaction continues).

这篇关于是否“SELECT FOR UPDATE"当该行不存在时阻止其他连接插入?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！