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

发布于 2024-09-06 07:56:51 字数 494 浏览 2 评论 0原文

我从表 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 中的任何列，甚至那些不是其主键的列，并且它会锁定整个记录？

原文

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?>

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

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?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

浴红衣 2024-09-13 07:56:51

来自10G PL/SQL 文档：

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

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:

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;

回复收藏 0 原文

~没有更多了~