oracle FOR UPDATE 语句的使用
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/11894670/
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
use of FOR UPDATE statement
提问by niceApp
I am using PL/SQL (Oracle 11g) to update the EMPLOYEEStable salary column.
我正在使用 PL/SQL (Oracle 11g) 来更新EMPLOYEES表工资列。
I have used two separate scripts to do the same thing i.e update the salary of employees.
我使用了两个单独的脚本来做同样的事情，即更新员工的工资。
One script uses FOR UPDATE OFstatement where as another script doesn't uses it. In both cases I found that oracle holds the row level locks until we execute the ROLLBACKor COMMITcommands.
一个脚本使用FOR UPDATE OF语句，而另一个脚本不使用它。在这两种情况下，我发现 oracle 持有行级锁，直到我们执行ROLLBACKorCOMMIT命令。
Then what is the difference in between two scripts?
那么两个脚本之间有什么区别呢？
Which one is better to use?
哪个更好用？
Here are the two scripts I am talking about:
这是我正在谈论的两个脚本：
-- Script 1: Uses FOR UPDATE OF
declare
cursor cur_emp
is
select employee_id,department_id from employees where department_id = 90 for update of salary;
begin
for rec in cur_emp
loop
update Employees
set salary = salary*10
where current of cur_emp;
end loop;
end;
--Script 2: Does the same thing like script 1 but FOR UPDATE OF is not used here
declare
cursor cur_emp
is
select employee_id,department_id from employees where department_id = 90;
begin
for rec in cur_emp
loop
update Employees
set salary = salary*10
where Employee_ID = rec.employee_id;
end loop;
end;
I found that Oracle acquired the row level locks on both cases. So, what is the benefit of using FOR UPDATE OFand Which is the better way of coding?
我发现 Oracle 在这两种情况下都获得了行级锁。那么，使用的好处FOR UPDATE OF是什么？哪种编码方式更好？
回答by Justin Cave
When you specify FOR UPDATE, the row is locked at the point that you SELECTthe data. Without the FOR UPDATE, the row is locked at the point you UPDATEthe row. In the second script, another session could potentially lock the row between the time that the SELECTwas executed and the point that you tried to UPDATEit.
当您指定 时FOR UPDATE，该行将锁定在您SELECT输入数据的位置。如果没有FOR UPDATE，该行将锁定在您所在UPDATE的位置。在第二个脚本中，另一个会话可能会锁定SELECT执行时间和您尝试执行该时间之间的行UPDATE。
If you are dealing with a SELECTstatement that returns relatively few rows and a tight inner loop, it is unlikely that there will be an appreciable difference between the two. Adding a FOR UPDATEon the SELECTalso gives you the opportunity to add a timeout clause if you don't want your script to block indefinitely if some other session happens to have one of the row you're trying to update locked.
如果您正在处理SELECT返回相对较少行和紧密内部循环的语句，则两者之间不太可能存在明显差异。如果您不希望脚本无限期阻塞，如果其他会话碰巧锁定了您尝试更新的行之一，则FOR UPDATE在 上添加SELECT也使您有机会添加超时子句。
