Atomic SELECT and UPDATE

Question

Atomic SELECT and UPDATE

5

如果我的数据长这样：

ID STATUS     DATE_ADDED
== ========== ==========
 1 Processing 2011-04-01
 2 New        2011-04-02 
 3 New        2011-04-03
 4 Processing 2011-04-03
 5 Done       2011-04-06
 6 New        2011-04-06
 7 New        2011-04-14
 8 Done       2011-04-14
 ...

在确保不会有其他并发进程处理相同记录的前提下，如何选择状态为“New”的最早10条记录并将它们的状态设置为“Processing”？

这是一个运行在 Windows Server 2003 上，连接远程 Oracle 10g 服务器（使用 Oracle 的驱动程序，而非 Microsoft 的）的 PHP/5.2.6 网络应用程序。

- Álvaro González

1

为什么只有你的进程更新10个最早的“新”行到“处理”是很重要的？ - Mike Sherrill 'Cat Recall'

使用高级队列（AQ）而不是自己编写？ - Shannon Severance

@Catcall - 因为记录需要被处理一次且仅一次。 - Álvaro González

@Shannon Severance - 我已经查看了Oracle文档中的高级队列。如果我错了，请纠正我，但它看起来是一项令人印象深刻的技术，不可能在几个小时内从零开始学习；我真希望我有时间 :( - Álvaro González

@Álvaro G. Vicario：我不知道足够的信息来回答你的问题，但我知道如果我接到这样的需求，我会花费相当长的时间去探索AQ，而不是自己开发。然而，我不在你的位置上，你已经了解了AQ，并决定不使用它。我的评论只是为了提示你去看一看，而你已经做到了。 - Shannon Severance

请查看https://dev59.com/gUvSa4cB1Zd3GeqPcjaK 上的原子UPDATE ... RETURNING功能。 - Vadzim

5个回答

2

使用事务来完成这个操作。在事务中使用隔离级别 "可串行化" 将防止其他进程在你的事务正在处理这些行时访问/修改它们。

如果一个可串行化的事务试图执行一个SQL数据操作语句，而这个语句修改了任何已被未提交事务修改过的表，则该语句将失败。

您可能想要使用：

set transaction isolation level serializable;

- halfdan

SERIALIZABLE：如果一个可序列化的事务试图执行修改任何已被未提交事务修改过的表的SQL数据操作语句，则该语句将失败。OP并不想知道如何使他自己的语句失败。 - Mike Sherrill 'Cat Recall'

谢谢你的指点。我之前不知道事务可以防止访问受影响的行，我会去了解一下。但我想我需要先执行UPDATE，然后找到一种方法来检索我已经更新的精确行。 - Álvaro González

1

You can create a new table and put one row in it. Your program can then lock the row with an update or select for update before proceding to the original table. This will work if all programs use the same procedure to mark the table "Processing."

create table Lock_Table (
  app_catagory  varchar2(20) primary key,
  usage_ts      timestamp(6)
 );

Insert one row: insert into Lock_Table (app_category) values 'APP1' and commit. This is a one time insert.

Then to lock out other sessions: update Lock_Table set usage_ts = current_timestamp where app_category = 'CAT1'

You don't need the usage_ts column, you can use a select for update.

As long as you do the above update before the "select the 10 oldest" query you'll guarantee your result. I recommend putting everything in one procedure (or one procedure in a package) to make it easy for application programmers to "do the right thing."

- redcayuga

抱歉，我不理解。也许您可以给新表和列命名，这样我就能明白了... - Álvaro González

我添加了一个“创建表”和说明。 - redcayuga

谢谢更新，现在我明白了。实际上我也有一个类似的“穷人队列”想法，它涉及到一个长随机字符串和一个“OWNER_PROCESS_RANDOM_STRING”列 :) - Álvaro González

实际上，你甚至可以在没有提交的情况下阻塞行。如果两个并发会话都插入具有相同主键值的行，那么“较慢”的会话仍然会通过enq: TX - row lock contention事件进行阻塞。 - Lukas Eder

1

解决这个问题的一种笨拙方法是锁定表，以便其他会话无法更新它：

lock your_table in exclusive mode

不幸的是，直到锁被释放之前，其他会话将无法插入新行，因此这可能会大大降低应用程序的并发性。

- redcayuga

我刚刚给你的答案点了赞。大多数其他答案提供了优秀可靠和细致的机制来完成任务。我甚至在我的开发环境中编写并测试了一个完整的工作原型，它的运行效果非常好。但是，无论这段代码在我的本地PC上运行得多么顺畅，在上传到生产服务器时都无法正常工作...除了粗暴的 LOCK TABLE table_name IN EXCLUSIVE MODE。虽然我显然需要进一步调试，但也应该承认这一点 :) - Álvaro González

-1

仅供参考

此外，可以应用乐观锁定策略来解决这个问题

- andrii

在这种特定情况下，那是一个不好的想法。OP 应该采用悲观锁，并确保所有其他进程都被锁定，无法获取 'New' 行上的“互斥锁”。 - Lukas Eder

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Adam Musch · Accepted Answer

在Oracle 10g中，这个任务很难完成。在11g中，使用“SELECT FOR UPDATE ... SKIP LOCKED”语法可以轻松实现。

简单的“UPDATE”语句将会串行化。同样，“SELECT FOR UPDATE”也会。当然，两个竞争进程永远不会得到相同的行；问题在于它们最多只能串行化，而在最坏的情况下，它们可能会死锁。

推荐的方法是使用Oracle高级队列（或您选择的队列实现）将要处理的ID入队，并允许队列实现管理对值的争用。

SQL将起作用，但如果第二个用户在某人锁定该范围时运行它以获取相同的偏移量，则会失败并出现ORA-00054。可以通过将选择包装在循环中、捕获ORA-00054错误并使用它来递增偏移量来缓解此问题。

select * from my_table
 where rowid in 
       (select row_id 
          from (select rowid as row_id, rownum as rn 
                  from mytable where some_condition 
                 order by deterministic_sort_order)
         where rn between :low_rn and :hi_rn
       )
 for update nowait;

为了防止冲突，排序表达式需要是确定性的（简单地说，将主键包含在排序表达式的末尾）。