SQL Oracle 使用 SELECT...FOR UPDATE OF 锁定
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3070410/
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 locking with SELECT...FOR UPDATE OF
提问by aw crud
I'm selecting from tables FOO and BAR. I'd like to lock the records of FOO which are being returned, but I don't want the records of BAR to be locked.
我正在从表 FOO 和 BAR 中进行选择。我想锁定正在返回的 FOO 的记录，但我不想锁定 BAR 的记录。
cursor c_foobar is
select foo.*, bar.* from
foo, bar
where foo.id = bar.foo_id
for update of <what should I put here?>
It seems like I need to specify individual columns, but I want the entire record of foo to be locked. e.g. I wish I could do something like:
似乎我需要指定单个列，但我希望 foo 的整个记录都被锁定。例如，我希望我能做这样的事情：
cursor c_foobar is
select foo.*, bar.* from
foo, bar
where foo.id = bar.foo_id
for update of foo
Do I have to enumerate every column of foo in the for update ofsection in order to lock them all? Or can I arbitrarily choose any column in foo, even those which are not its primary key, and it will lock the entire record?
我是否必须枚举该for update of部分中的每一列 foo才能将它们全部锁定？或者我可以任意选择 foo 中的任何列，即使是那些不是它的主键的列，它会锁定整个记录？
回答by Tony Andrews
From the 10G PL/SQL documentation:
When querying multiple tables, you can use the FOR UPDATE clause to confine row locking to particular tables. Rows in a table are locked only if the FOR UPDATE OF clause refers to a column in that table. For example, the following query locks rows in the employees table but not in the departments table:
查询多个表时，可以使用 FOR UPDATE 子句将行锁定限制在特定表上。仅当 FOR UPDATE OF 子句引用该表中的列时，表中的行才会被锁定。例如，以下查询锁定员工表中的行，但不锁定部门表中的行：
DECLARE
CURSOR c1 IS SELECT last_name, department_name FROM employees, departments
WHERE employees.department_id = departments.department_id
AND job_id = 'SA_MAN'
FOR UPDATE OF salary;
