oracle - 如何使 MERGE 可序列化

Question

在执行并发 MERGE 时，每个 session 都对主键列 id 使用不同的值(在下面的代码段中显示为 ***)，一切都很好如果我在 2 个终端 session 中手动执行此操作。

MERGE
 INTO x
   USING (SELECT *** as id FROM DUAL) MERGE_SRC
     ON (x.id = MERGE_SRC.id)
 WHEN MATCHED THEN UPDATE SET val = val + 1 WHERE id = ***
 WHEN NOT MATCHED THEN INSERT VALUES (***, 99);
COMMIT;

但是，使用 3 个或更多线程运行多线程负载测试时，我会相对较快地遇到 ORA-08177 with locked table。这是为什么？ (为什么它是不确定的，因为它并不总是在事务重叠时发生？)

表是用

创建的

create table x (id int primary key, val int);

SQL Server 顺便说一句，运行相同的实验时，从不使用等效的 MERGE 语句抛出异常。当同时处理同一行时，情况更是如此。

是不是因为 MERGE 可能不是原子的，而可序列化模式乐观地运行，所以竞争可能只有在充分竞争的情况下才会显示？尽管如此，为什么即使不是同时处理同一行也会发生这种情况？

顺便说一句，我尝试 fix this using the strictest lock available没有成功。因此，非常感谢任何关于如何使这个原子化的想法。看起来放宽隔离级别会使我摆脱异常，但如果在同一行上有 2 个更新，则存在不一致的风险(否则为什么它首先会在可序列化模式下犹豫不决)。

Answer 1

您看到的异常是使用严格序列化的直接结果。如果同时有多个事件事务，每个事务都以 SET TRANSACTION ISOLATION LEVEL SERIALIZABLE 开始，当其中任何一个提交时，其他事务将得到 ORA-08177。这就是强制执行严格序列化的方式——如果另一个事务提交到可序列化 session 需要的表中，数据库将在任何以 ISOLATION LEVEL SERIALIZABLE 启动的 session 中抛出 ORA-08177。所以，基本上，如果您确实需要严格序列化，则必须智能地处理 ORA-08177，如下所示:

DECLARE
  bSerializable_trans_complete  BOOLEAN := FALSE;
  excpSerializable              EXCEPTION;
  PRAGMA EXCEPTION_INIT(excpSerializable, -08177);
BEGIN
  <<SERIALIZABLE_LOOP>>
  WHILE NOT bSerializable_trans_complete
  LOOP
    BEGIN
      SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

      MERGE ...; -- or whatever

      COMMIT;

      bSerializable_trans_complete := TRUE;  -- allow SERIALIZABLE_LOOP to exit
    EXCEPTION
      WHEN excpSerializable THEN
        ROLLBACK;
        CONTINUE SERIALIZABLE_LOOP;
    END;
  END LOOP;  -- SERIALIZABLE_LOOP
END;

序列化不是魔法，也不是“免费”的(“免费”的意思是“我作为开发人员无需做任何事情就可以正常工作”)。它需要开发人员更多的计划和工作才能使其正常运行，而不是更少。分享和享受。