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
Does "select for update" prevent other connections inserting when the row is not present
提问by Mike Q
I'm interested in whether a select for update
query 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 (?, ?)
- Issue query
- 问题查询
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 update
prevent 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 UPDATE
allows 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 UPDATE
on 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 UPDATE
with 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 UPDATE
with INSERT
,您如何为尚不存在的记录锁定索引?如果您使用的是默认隔离级别REPEATABLE READ
,InnoDB 也会使用间隙锁。只要您知道id
要锁定的(甚至是 id 的范围),那么 InnoDB 就可以锁定间隙,因此在我们完成之前不能在该间隙中插入其他记录。
If your id
column were an auto-increment column, then SELECT ... FOR UPDATE
with INSERT INTO
would be problematic because you wouldn't know what the new id
was until you inserted it. However, since you know the id
that you wish to insert, SELECT ... FOR UPDATE
with INSERT
will work.
如果您的id
列是自动增量列,那么SELECT ... FOR UPDATE
withINSERT INTO
会出现问题,因为在id
插入之前您不知道新的内容是什么。但是,既然您知道id
要插入的 ,那么SELECT ... FOR UPDATE
withINSERT
将起作用。
CAVEAT
警告
On the default isolation level, SELECT ... FOR UPDATE
on a non-existent record does notblock other transactions. So, if two transactions both do a SELECT ... FOR UPDATE
on 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 INSERT
on the non-existent record index on the first transaction, all other transactions will block if they attempt to INSERT
a 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 MODE
before the INSERT
, if a previous transaction has inserted that record but hasn't committed yet, the SELECT ... LOCK IN SHARE MODE
will block until the previous transaction has completed.
如果您选择 with LOCK IN SHARE MODE
before 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:
因此,为了减少重复键错误的机会,特别是如果您在提交或回滚它们之前执行业务逻辑时持有锁一段时间:
SELECT bar FROM FooBar WHERE foo = ? LOCK FOR UPDATE
- If no records returned, then
INSERT INTO FooBar (foo, bar) VALUES (?, ?)
SELECT bar FROM FooBar WHERE foo = ? LOCK FOR UPDATE
- 如果没有返回记录,则
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 UPDATE
as 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 UPDATE
has 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 UPDATE
behavior with transactions and locking strategies. But, that may be more than what you're looking for.
现在,可以FOR UPDATE
使用事务和锁定策略来模拟行为。但是,这可能比您想要的更多。