使用SELECT ...进行更新的Oracle锁定 [英] Oracle locking with SELECT...FOR UPDATE OF

问题描述

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

推荐答案

来自

查询多个表时，可以 使用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;

这篇关于使用SELECT ...进行更新的Oracle锁定的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！