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 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?
我是否必须在更新部分中枚举foo的每一列以便将它们全部锁定?或者我可以随意选择foo中的任何列,即使那些不是它的主键,它会锁定整个记录吗?
1 个解决方案
#1
12
From the 10G PL/SQL documentation:
从10G PL / SQL文档:
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子句引用该表中的列时,才会锁定表中的行。例如,以下查询锁定employees表中的行,但不锁定departments表中的行:
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;
#1
12
From the 10G PL/SQL documentation:
从10G PL / SQL文档:
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子句引用该表中的列时,才会锁定表中的行。例如,以下查询锁定employees表中的行,但不锁定departments表中的行:
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;