如何锁定选择，而不仅仅是插入/更新/删除

发布于 2024-12-10 20:02:46 字数 862 浏览 0 评论 0原文

可以说我有如下代码：

begin
  select ... from T where x = 42;         -- (1)
  .
  .
  .
  update T ... where x = 42;              -- (2)
  commit;
end;

我是否正确地说，到 (2) 执行时，在 (1) 中从 T 中选择的任何内容可能不再在 T 中，例如，如果在另一个会话中执行以下内容：

delete from T where x = 42;

如果是这样，我希望发生的情况是将 select 语句“锁定”T，因此无法对其进行修改。

我意识到我可以通过以下方式显式地做到这一点：

lock table T in exclusive mode;

但是如果 T 是一个视图呢？我是否必须查看 T 视图/子视图的定义才能找到它引用的所有表并单独锁定它们，或者我可以执行以下操作：

begin
  START_TRANSACTION;
  select ... from T where x = 42;         -- (1)
  .
  .
  .
  update T ... where x = 42;              -- (2)
  commit;
end;

其中 START_TRANSACTION 确保锁定所有 select 语句中引用的所有表，直到事务完成？

或者这个问题还有其他更好的解决方案吗？如果这很重要的话，我正在使用 Oracle 10g。

原文

Lets say I've got code like the following:

begin
  select ... from T where x = 42;         -- (1)
  .
  .
  .
  update T ... where x = 42;              -- (2)
  commit;
end;

Am I correct in saying that by the time (2) executes, whatever has been selected from T in (1) may no longer be in T, if, for example, in another session the following executed:

delete from T where x = 42;

If so, what I would like to happen is the select statement to 'lock' T, so it can not be modified.

I realise I can do this explicitly by doing:

lock table T in exclusive mode;

But what if T is a view? Do I have to look through the definition of Ts view/subviews to find all tables it references and lock them individually, or can I do something like this:

begin
  START_TRANSACTION;
  select ... from T where x = 42;         -- (1)
  .
  .
  .
  update T ... where x = 42;              -- (2)
  commit;
end;

Where START_TRANSACTION ensures locks all tables referred to in all select statements until the transaction is complete?

Or is there another nicer solution to this issue? I'm using Oracle 10g if that is important.

分享到QQ

分享到微博

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

发布评论

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

你的笑 2024-12-17 20:02:46

我是否正确地说，在 (2) 执行时，无论发生了什么
在(1)中从T中选择的可能不再在T中

。

因此，您可以通过执行以下操作来锁定行...

SELECT ...
[INTO   ...]
FROM   T
WHERE  x = 42
FOR    UPDATE [NOWAIT];

如果其他人已经锁定了该行，您可以选择使用 NOWAIT 使语句失败。如果没有NOWAIT，语句将暂停，直到它可以锁定该行。

Am I correct in saying that by the time (2) executes, whatever has
been selected from T in (1) may no longer be in T

Yes.

So you can lock the row by doing...

SELECT ...
[INTO   ...]
FROM   T
WHERE  x = 42
FOR    UPDATE [NOWAIT];

You can optionally use NOWAIT to make the statement fail if someone else already has the row locked. Without the NOWAIT the statement will pause until it can lock the row.

回复收藏 0 原文