使用 SELECT...FOR UPDATE OF 的 Oracle 锁定

2021-12-24 00:00:00 sql locking oracle plsql

我正在从表 FOO 和 BAR 中进行选择.我想锁定正在返回的FOO的记录，但我不想锁定BAR的记录.

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.

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 的整个记录​​.例如我希望我能做这样的事情:

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:

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中的任何列，即使是那些不是它的主键的列，它会锁定整个记录?

Do I have to enumerate every column of foo in the for update of section 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?

推荐答案

来自 10G PL/SQL 文档:

查询多个表时，可以使用 FOR UPDATE 子句来限制行锁定到特定表.行只有在 FOR 时才锁定表中的UPDATE OF 子句引用一列在那张桌子上.例如，以下查询锁定行员工表但不在部门表:

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;

相关文章