Oracle 存储过程线程安全吗？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6194612/
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
Oracle stored procedure thread-safe?
提问by Ran Tang
Pseudo code:
伪代码：
begin stored procedure
commit
check the value column 1 of row a //step 1
update column 1 of row a //step 2
commit
end stored procedure
开始存储过程
犯罪
检查第 a 行的第 1 列的值 // 步骤 1
更新第 a 行的第 1 列 //第 2 步
犯罪
结束存储过程
Is this sp thread safe?
这个 sp 线程安全吗？
edit:
编辑：
Declare
tag_rec prep_tag%ROWTYPE;
begin
COMMIT;
SELECT * INTO tag_rec
FROM PREP_TAG
WHERE project = 'a' and categoryId = 'b';
if tag_rec.locked = 'No' then
UPDATE prep_tag
SET locked = 'Yes'
WHERE TAG_NUMBER = tag_rec.TAG_NUMBER;
end if;
COMMIT;
end;
Is this sp thread safe? Is it possible that thread A checked tag_rec.locked = 'No' then it's about to update it. But before it does that, thread B sneaks in and also see tag_rec.locked = 'No'?
这个 sp 线程安全吗？线程 A 是否有可能检查 tag_rec.locked = 'No' 那么它即将更新它。但在此之前，线程 B 潜入并看到 tag_rec.locked = 'No'？
回答by eaolson
The short answer to your question is no, this is not thread safe. Some other session could come in and update prep_tag in between your SELECT and your UPDATE statements.
对您问题的简短回答是否定的，这不是线程安全的。其他一些会话可能会进入并在您的 SELECT 和您的 UPDATE 语句之间更新 prep_tag。
The long answer is that you're doing this the wrong way. It sounds like you want to update the locked field for those records. You might as well just do:
长的答案是你这样做是错误的。听起来您想更新这些记录的锁定字段。你也可以这样做：
UPDATE prep_tag SET locked = 'Yes'
WHERE project = 'a'
AND categoryId = 'b'
AND locked = 'No'
One statement, and it's guaranteed to be atomic.
一个声明，它保证是原子的。
Also, I'd suggest not committing inside your block, assuming there's more to this transaction.
另外，我建议不要在你的区块内提交，假设这个交易还有更多。
回答by Gary Myers
Oracle does not lock on SELECT (unless there is a FOR UPDATE clause), so there is a chance that the row will be updated between the SELECT and UPDATE.
Oracle 不会锁定 SELECT（除非有 FOR UPDATE 子句），因此有可能在 SELECT 和 UPDATE 之间更新该行。
But the more likely scenario is that the row would have been updated PRIOR to the select and not committed. The SELECT would show the current committed state. Then the UPDATE would come along and wait on the lock.
但更有可能的情况是该行将在选择之前更新而不是提交。SELECT 将显示当前提交的状态。然后 UPDATE 会出现并等待锁定。
Assuming you don't want to block the session, look at SELECT...FOR UPDATE NOWAIT, and deal with the exception. An alternative is SERIALIZABLEisolation level. That will basically throw an "ORA-08177 can't serialize access for this transaction" error if an attempt is made to change data that was not current at the start of the transaction.
假设您不想阻止会话，请查看 SELECT...FOR UPDATE NOWAIT，并处理异常。另一种选择是SERIALIZABLE隔离级别。如果尝试更改在事务开始时不是最新的数据，这基本上会引发“ORA-08177 无法序列化此事务的访问”错误。
回答by APC
You don't need to roll your own checking mechanism. Oracle already provides this functionality with its SELECT ... FOR UPDATE syntax. The WAIT|NOWAIT controls the behaviour if the selected row is locked (wait or fail immediately). Find out more. (In 11g Oracle exposed the SKIP ROWS clausewhich allows us to implement our own queuing mechanism.)
您不需要推出自己的检查机制。Oracle 已经通过其 SELECT ... FOR UPDATE 语法提供了此功能。WAIT|NOWAIT 控制选定行被锁定时的行为（立即等待或失败）。 了解更多。（在11g 中，Oracle 公开了 SKIP ROWS 子句，它允许我们实现自己的排队机制。）
If you want need a more complicated locking process Oracle allows us to build our own with the DBMS_LOCK package. However, privileges on this package are not granted to anybody by default; this is because building custom locking routines is tricky.
如果您需要更复杂的锁定过程，Oracle 允许我们使用DBMS_LOCK 包构建我们自己的锁定过程。但是，默认情况下不会向任何人授予此包的权限；这是因为构建自定义锁定例程很棘手。
