Oracle 了解对同一行的高并发更新的事务隔离级别

在查看了有关隔离级别的Oracle文档之后，我试图确保我正确理解了DML的工作方式。基本用例是，我正在开发一个作业管理服务，该服务触发由其他微服务实现的作业。它通过通过消息代理向它们发送消息来触发这些作业。集中式作业管理服务有多个实例，该实例具有每秒触发一次的计时器。在每1秒运行期间，使用Spring的 @ transaction注释在Java端启动一个事务。然后，Java应用程序调用一个存储过程 (存储的proc是我们公司的安全要求，我们不能直接从我们的Java代码中使用SQL)，它将查找处于等待状态且触发时间已准备就绪的所有作业。存储过程将状态从等待状态翻转为运行状态，并返回带有准备运行的作业的resultset。然后，Java端将从resultset创建消息，并将它们推送到代理上。如果推送消息失败，则事务将回滚，这将使受影响的作业回到等待状态。

我已经评估了存储的proc中有几个选项，以获取准备运行的作业并将其更新为运行状态。我的倾向是:

  TYPE pk_list IS TABLE OF NUMBER INDEX BY PLS_INTEGER;
  pk pk_list;
  ...
  UPDATE jobs 
     SET state = 'RUNNING'
   WHERE state = 'WAITING'
     AND SYSTIMESTAMP >= trigger_time
   RETURNING job_id BULK COLLECT INTO pk;
  ...
  -- Use pk to SELECT * FROM jobs returning as SYS_REFCURSOR
复制

由于将有多个 (大约6个) 作业管理服务的实例，每个调用此存储的proc，我担心竞争条件和两个实例更新相同的行。更新实际上不是一个大问题，因为它们将被更新到相同的状态。更大的问题是归还这些工作并双重触发它们。

这是我认为这是如何工作的。如果我使用READ _ COMMITTED隔离，则每个实例在启动每个语句时都会获得数据的一致视图 (在这种情况下为更新作业的DML)。第一个到达那里的实例将在WHERE子句中找到的行上设置排他锁。如果第二个实例要开始他们的查询，他们将在第一个实例提交数据之前看到数据的视图。它将被锁阻止，并且无法更新该行，但是一旦该行被解锁，它将执行更新并在其批量收集中返回相同的行集。

如果我使用序列化隔离，则每个实例在开始事务时都会获得数据的一致视图。同样的锁定会发生，阻塞也会发生，但是当第二个实例尝试执行更新时，我应该看到一个 “ORA-08177: 无法对该事务序列化访问” 错误。这基本上可以被忽略，因为我知道工作是由不同的实例找到的。

我还考虑过使用SELECT进行更新，然后使用更新而不是更新... 批量收集，但我认为这会给我相同的结果。由于SELECT和UPDATE在两个语句中分开，因此它也可能有更大的冲突机会。

如果可以保证冲突实际上不会引起问题，我宁愿使用更简单的READ _ COMMITTED隔离。否则我好移动到连载隔离。

我也对满足基本要求的其他选项持开放态度-应该只允许一个实例将数据视为等待和准备就绪。

更新总是在当前模式下获取行-这是因为它们现在存在。因此，当一个会话提交时，等待此操作的其他会话中的任何更新都将看到更改。

例如，在一个会话中运行此代码:

create table t (
  c1 int, c2 varchar2(10)
);

insert into t values ( 1, 'WAITING' );
insert into t values ( 2, 'WAITING' );
insert into t values ( 3, 'RUNNING' );
commit;

declare
  recs dbms_sql.number_table;
begin
  update t
  set    c2 = 'RUNNING'
  where  c2 = 'WAITING'
  returning c1 bulk collect into recs;
  
  dbms_output.put_line ( 'Updated ' || recs.count );
  for i in 1 .. recs.count loop
    dbms_output.put_line ( recs(i) );
  end loop;
end;
/

Updated 2
1
2
复制

如果此时启动第二个会话并运行匿名块，它将等待第一个提交/回滚。当你这样做，你会看到这个输出在第二:

Updated 0
复制

这是因为第二个会话中的更新获取的行，因为它们是在第一次提交之后; 这意味着它没有看到具有等待值的行。

您可以使事务可序列化并忽略ORA-08177错误，但没有必要。

您可以使用SELECT...如果你愿意，也可以更新。为了避免读取一致性问题，批量收集查询输出并使用此数组来驱动更新:

declare
  recs dbms_sql.number_table;
begin
  select c1
  bulk collect into recs
  from   t
  where  c2 = 'WAITING'
  for update;
  
  forall i in 1 .. recs.count
    update t
    set    c2 = 'RUNNING'
    where  c1 = recs (i);
  
  dbms_output.put_line ( 'Updated ' || recs.count );
  for i in 1 .. recs.count loop
    dbms_output.put_line ( recs(i) );
  end loop;
end;
/
复制

有几个原因，你可能更喜欢这个，而不是简单的更新:

nowait | wait N | 跳过锁定子句

如果由于某种原因，一个更新需要很长时间才能提交，则会话可能会迅速开始堆积。如果你不走运，这可能会使你的应用程序无法使用。这些选项允许事务停止处理，而不是等待 (可能无限期地)。

在更新之前，事务会做 (很多) 其他工作

选择更新也可以在当前模式下工作，在能够锁定行时查看数据。如果查询不返回任何行，则您可能能够立即停止事务，从而避免浪费工作，因为更新不会改变任何内容。

您在事务中有其他DML可能导致死锁

如果事务运行许多更新/删除，则需要注意避免死锁。听起来这里不是这种情况，但是对于一般的事务管理来说，这是值得记住的。