sql - Oracle 使用 SELECT...FOR UPDATE OF 锁定

Question

我正在从表 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?>

似乎我需要指定单个列，但我希望 foo 的整个记录​​都被锁定。例如我希望我能做这样的事情:

cursor c_foobar is
select foo.*, bar.* from
foo, bar
where foo.id = bar.foo_id
for update of foo

我是否必须枚举 for update of 中 foo 的每一列？部分以便将它们全部锁定？或者我可以任意选择 foo 中的任何列，即使是那些不是它的主键的列，它会锁定整个记录？

Answer 1

来自 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:

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;