oracle 当该行不存在时,“选择更新”是否会阻止其他连接插入

声明:本页面是StackOverFlow热门问题的中英对照翻译,遵循CC BY-SA 4.0协议,如果您需要使用它,必须同样遵循CC BY-SA许可,注明原文地址和作者信息,同时你必须将它归于原作者(不是我):StackOverFlow 原文地址: http://stackoverflow.com/questions/3601895/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me): StackOverFlow

提示:将鼠标放在中文语句上可以显示对应的英文。显示中英文
时间:2020-09-18 21:24:58  来源:igfitidea点击:

Does "select for update" prevent other connections inserting when the row is not present

sqlmysqlsql-serveroracleconcurrency

提问by Mike Q

I'm interested in whether a select for updatequery will lock a non-existent row.

我对select for update查询是否会锁定不存在的行感兴趣。

e.g.

例如

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

表 FooBar 有两列,foo 和 bar,foo 有唯一索引

  • Issue query select bar from FooBar where foo = ? for update
  • If query returns zero rows
    • Issue query insert into FooBar (foo, bar) values (?, ?)
  • 问题查询 select bar from FooBar where foo = ? for update
  • 如果查询返回零行
    • 问题查询 insert into FooBar (foo, bar) values (?, ?)

Now is it possible that the insert would cause an index violation or does the select for updateprevent that?

现在插入是否有可能导致索引违规或是否select for update阻止了这种情况?

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

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

采纳答案by DCookie

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).

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

回答by Marcus Adams

MySQL

MySQL

SELECT ... FOR UPDATE with UPDATE

SELECT ... FOR UPDATE 与 UPDATE

Using transactions with InnoDB (auto-commit turned off), a SELECT ... FOR UPDATEallows one session to temporarily lock down a particular record (or records) so that no other session can update it. Then, within the same transaction, the session can actually perform an UPDATEon the same record and commit or roll back the transaction. This would allow you to lock down the record so no other session could update it while perhaps you do some other business logic.

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

This is accomplished with locking. InnoDB utilizes indexes for locking records, so locking an existing record seems easy--simply lock the index for that record.

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

SELECT ... FOR UPDATE with INSERT

SELECT ... FOR UPDATE 与 INSERT

However, to use SELECT ... FOR UPDATEwith INSERT, how do you lock an index for a record that doesn't exist yet? If you are using the default isolation level of REPEATABLE READ, InnoDB will also utilize gaplocks. As long as you know the id(or even range of ids) to lock, then InnoDB can lock the gap so no other record can be inserted in that gap until we're done with it.

但是,要使用SELECT ... FOR UPDATEwith INSERT,您如何为尚不存在的记录锁定索引?如果您使用的是默认隔离级别REPEATABLE READ,InnoDB 也会使用间隙锁。只要您知道id要锁定的(甚至是 id 的范围),那么 InnoDB 就可以锁定间隙,因此在我们完成之前不能在该间隙中插入其他记录。

If your idcolumn were an auto-increment column, then SELECT ... FOR UPDATEwith INSERT INTOwould be problematic because you wouldn't know what the new idwas until you inserted it. However, since you know the idthat you wish to insert, SELECT ... FOR UPDATEwith INSERTwill work.

如果您的id列是自动增量列,那么SELECT ... FOR UPDATEwithINSERT INTO会出现问题,因为在id插入之前您不知道新的内容是什么。但是,既然您知道id要插入的 ,那么SELECT ... FOR UPDATEwithINSERT将起作用。

CAVEAT

警告

On the default isolation level, SELECT ... FOR UPDATEon a non-existent record does notblock other transactions. So, if two transactions both do a SELECT ... FOR UPDATEon the same non-existent index record, they'll both get the lock, and neither transaction will be able to update the record. In fact, if they try, a deadlock will be detected.

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

Therefore, if you don't want to deal with a deadlock, you might just do the following:

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

INSERT INTO ...

插入 ...

Start a transaction, and perform the INSERT. Do your business logic, and either commit or rollback the transaction. As soon as you do the INSERTon the non-existent record index on the first transaction, all other transactions will block if they attempt to INSERTa record with the same unique index. If the second transaction attempts to insert a record with the same index after the first transaction commits the insert, then it will get a "duplicate key" error. Handle accordingly.

启动一个事务,并执行INSERT. 执行您的业务逻辑,然后提交或回滚事务。一旦您INSERT在第一个事务上对不存在的记录索引执行操作,所有其他事务如果尝试INSERT使用相同唯一索引的记录将阻塞。如果第二个事务在第一个事务提交插入后尝试插入具有相同索引的记录,则会出现“重复键”错误。相应地处理。

SELECT ... LOCK IN SHARE MODE

选择...锁定共享模式

If you select with LOCK IN SHARE MODEbefore the INSERT, if a previous transaction has inserted that record but hasn't committed yet, the SELECT ... LOCK IN SHARE MODEwill block until the previous transaction has completed.

如果您选择 with LOCK IN SHARE MODEbefore the INSERT,如果前一个事务已插入该记录但尚未提交,SELECT ... LOCK IN SHARE MODE则将阻塞,直到前一个事务完成。

So to reduce the chance of duplicate key errors, especially if you hold the locks for awhile while performing business logic before committing them or rolling them back:

因此,为了减少重复键错误的机会,特别是如果您在提交或回滚它们之前执行业务逻辑时持有锁一段时间:

  1. SELECT bar FROM FooBar WHERE foo = ? LOCK FOR UPDATE
  2. If no records returned, then
  3. INSERT INTO FooBar (foo, bar) VALUES (?, ?)
  1. SELECT bar FROM FooBar WHERE foo = ? LOCK FOR UPDATE
  2. 如果没有返回记录,则
  3. INSERT INTO FooBar (foo, bar) VALUES (?, ?)

回答by jva

On Oracle:

在甲骨文上:

Session 1

第一节

create table t (id number);
alter table t add constraint pk primary key(id);

SELECT *
FROM t
WHERE id = 1
FOR UPDATE;
-- 0 rows returned
-- this creates row level lock on table, preventing others from locking table in exclusive mode

Session 2

第二节

SELECT *
FROM t 
FOR UPDATE;
-- 0 rows returned
-- there are no problems with locking here

rollback; -- releases lock


INSERT INTO t
VALUES (1);
-- 1 row inserted without problems

回答by A-K

I wrote a detailed analysis of this thing on SQL Server: Developing Modifications that Survive Concurrency

我在SQL Server上写了一篇关于这件事的详细分析:Developing Modifications that Survive Concurrency

Anyway, you need to use SERIALIZABLE isolation level, and you really need to stress test.

无论如何,您需要使用SERIALIZABLE隔离级别,并且您确实需要进行压力测试。

回答by bobs

SQL Server only has the FOR UPDATEas part of a cursor. And, it only applies to UPDATE statements that are associated with the current row in the cursor.

SQL Server 只有FOR UPDATE作为游标的一部分。而且,它仅适用于与游标中的当前行相关联的 UPDATE 语句。

So, the FOR UPDATEhas no relationship with INSERT. Therefore, I think your answer is that it's not applicable in SQL Server.

所以, 与FOR UPDATE没有关系INSERT。因此,我认为您的答案是它不适用于 SQL Server。

Now, it may be possible to simulate the FOR UPDATEbehavior with transactions and locking strategies. But, that may be more than what you're looking for.

现在,可以FOR UPDATE使用事务和锁定策略来模拟行为。但是,这可能比您想要的更多。