Oracle SQL - 使事务原子化

发布于 2024-12-12 07:07:32 字数 728 浏览 0 评论 0原文

我的程序是这样的：

create or replace
PROCEDURE NEWJOBIDPROC (JOB_ID OUT NUMBER )
--++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++--
-- PROCEDURE TO RETRIEVE THE JOB ID
--++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++--
IS
BEGIN
  -- select the job_id
    SELECT VALUE+1 INTO JOB_ID FROM JOB_TABLE WHERE ID = 50;
  -- update table JOB_TABLE with the latest job id
    UPDATE JOB_TABLE SET VALUE = JOB_ID WHERE ID = 50;

END;

现在我的问题如下。

假设我同时多次调用此过程。在我们的示例中，让我们同时调用同一个过程。当它们都运行 select 语句时，它们会收到一些值 - 让它为 200。

现在，它们都将对 job_table 进行更新，具有相同的值 200 - 这不是我想要的。我不想要重复的。

那么，如何将整个代码标记为原子呢？我希望选择和更新同时运行并且线程安全。我希望两个语句一起被标记为原子。

原文

I have a procedure that goes like this:

create or replace
PROCEDURE NEWJOBIDPROC (JOB_ID OUT NUMBER )
--++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++--
-- PROCEDURE TO RETRIEVE THE JOB ID
--++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++--
IS
BEGIN
  -- select the job_id
    SELECT VALUE+1 INTO JOB_ID FROM JOB_TABLE WHERE ID = 50;
  -- update table JOB_TABLE with the latest job id
    UPDATE JOB_TABLE SET VALUE = JOB_ID WHERE ID = 50;

END;

Now my question is the following.

Let us say I have multiple calls to this procedure at the same time.
In our example let us make that two simultaneous calls to the same procedure.
When both of them run the select statement they receive some value - let it be 200.

Now, they will both make an update to the job_table, with the same value of 200 - which is not what I want. I don't want duplicates.

So, how do I mark the whole code as atomic? I want the select and the update to run at the same time and be thread safe. I want two statements together to be marked as atomic.

分享到QQ

分享到微博

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

发布评论

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

春风十里 2024-12-19 07:07:32

请参阅 @derobert 提供的链接以获取更多信息，但对于您的特定示例，您可以这样做：

UPDATE JOB_TABLE SET VALUE = VALUE +1 WHERE ID = 50
 RETURNING VALUE INTO JOB_ID;

但是 - 您没有考虑过使用序列吗？

See the link @derobert provided for more information, but for your particular example you could do this:

UPDATE JOB_TABLE SET VALUE = VALUE +1 WHERE ID = 50
 RETURNING VALUE INTO JOB_ID;

But - have you not considered using a Sequence instead?

回复收藏 0 原文

窝囊感情。 2024-12-19 07:07:32

如果您打算稍后更新该行，则应该锁定该行。第二个事务将被阻止并等待您完成事务（默认情况），或者如果它尝试锁定指定为 NOWAIT 的行，则会收到错误。

BEGIN
  -- select the job_id and LOCK the row so that noone else can modify it
    SELECT VALUE+1 INTO JOB_ID FROM JOB_TABLE WHERE ID = 50 FOR UPDATE NOWAIT;
  -- update table JOB_TABLE with the latest job id
    UPDATE JOB_TABLE SET VALUE = JOB_ID WHERE ID = 50;    
END;

在任何一种情况下，锁定行都会阻止 “丢失更新” 您所描述的行为。

You should lock the row if you intent to update it afterwards. The second transaction will either be blocked and wait for you to finish the transaction (default case) or will receive an error if it has tried to lock the row with NOWAIT specified.

BEGIN
  -- select the job_id and LOCK the row so that noone else can modify it
    SELECT VALUE+1 INTO JOB_ID FROM JOB_TABLE WHERE ID = 50 FOR UPDATE NOWAIT;
  -- update table JOB_TABLE with the latest job id
    UPDATE JOB_TABLE SET VALUE = JOB_ID WHERE ID = 50;    
END;

In either case locking the row prevents the "lost update" behaviour you've described.

回复收藏 0 原文