原子选择和更新

发布于 2024-10-31 22:07:06 字数 504 浏览 2 评论 0原文

如果我的数据如下所示：

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

...建议的方法是什么来选择状态为“新”的 10 个最旧的记录并将其状态设置为“正在处理”，同时确保任何其他并发进程不能对相同的记录执行相同的操作？

它是一个在 Windows Server 2003 下的 PHP/5.2.6 上运行的 Web 应用程序，通过 ODBC（Oracle 的驱动程序，而不是 Microsoft 的）连接到远程 Oracle 10g 服务器。

原文

If my data looks like this:

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

... what's the recommended way to pick the 10 oldest records with status "New" and set their status to "Processing" while ensuring that any other concurrent process cannot do the same with the same records?

It's a web application running on PHP/5.2.6 under Windows Server 2003 that connects to a remote Oracle 10g server through ODBC (Oracle's driver, not Microsoft's).

分享到QQ

分享到微博

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

发布评论

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

梦年海沫深 2024-11-07 22:07:06

这在 Oracle 10g 中很难做到。在 11g 中，SELECT FOR UPDATE ... SKIP LOCKED 语法使这一切变得简单。

一个简单的 UPDATE 语句将序列化。 SELECT FOR UPDATE 也是如此。当然，两个竞争进程永远不会获得相同的行；问题是它们充其量只能串行化，而最坏的情况则可能导致死锁。

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

SQL 可以工作，但如果第二个用户在某人锁定了该范围的情况下为相同的偏移量运行该 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;

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

This is hard to do in Oracle 10g. In 11g, the SELECT FOR UPDATE ... SKIP LOCKED syntax makes it easy.

A simple UPDATE statement will serialize. As will a SELECT FOR UPDATE. Sure, two competing processes won't ever get the same rows; the problem is that they will at best serialize, and at worst, they can deadlock.

The recommended way would be to use Oracle Advanced Queueing (or the queuing implementation of your choice) to enqueue to the IDs to be processed, and allow the queueing implementation to manage the contention for values.

The SQL will work, but will fail with an ORA-00054 if a second user runs it for the same offsets while someone has that range locked. That can be mitigated by wrapping the select in a loop, catching the ORA-00054 error and using that to increment the offsets.

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;

The sort expression needs to be deterministic (simply, include the primary key as the end of the sort expression) to prevent collisions.

回复收藏 0 原文

阿楠 2024-11-07 22:07:06

使用事务来做到这一点。使用隔离级别“可序列化”当您的事务正在处理这些行时，事务将阻止任何其他进程访问/修改这些行。

如果可序列化事务尝试执行 SQL 数据操作语句来修改已被未提交事务修改的任何表，则该语句将失败。

您可能想使用：

set transaction isolation level serializable;

Use a transaction to do that. Using the isolation level "serializable" for the transaction will prevent any other process to access/modify the rows while your transaction is working on them.

If a serializable transaction tries to execute a SQL data manipulation statement that modifies any table already modified by an uncommitted transaction, the statement fails.

You may want to use:

set transaction isolation level serializable;

回复收藏 0 原文

尤怨 2024-11-07 22:07:06

解决这个问题的一个严厉方法是锁定表，这样其他会话就不能更新它：

以独占模式锁定 your_table

不幸的是，在释放锁之前，其他会话将无法插入新行，因此这确实会降低应用程序的并发性。

回复收藏 0 原文

最偏执的依靠 2024-11-07 22:07:06

您可以创建一个新表并在其中放入一行。然后，您的程序可以通过更新锁定行或选择更新，然后再继续处理原始表。如果所有程序都使用相同的过程来标记表“正在处理”，则这将起作用。

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

插入一行：插入 Lock_Table (app_category) 值“APP1”并提交。这是一次性插入。

然后锁定其他会话：update Lock_Table set use_ts = current_timestamp where app_category = 'CAT1'

您不需要 use_ts 列，可以使用选择更新。

只要您在“选择 10 个最旧的”查询之前执行上述更新，您就能保证您的结果。我建议将所有内容放在一个过程中（或将一个过程放在一个包中），以便应用程序程序员能够轻松地“做正确的事情”。

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."

回复收藏 0 原文