Oracle “每行之前” 触发器导致表锁定而不更新主键列

嗨，汤姆，

我发现我们的 “每行之前” 触发器有一个奇怪的行为。

在我们的数据库中，我们有一些表经常被重新引用 (50-80个引用)。
我们所有的表都有五个常见列PK，CREATIONTIME，CREATIONUSER，UPDATETIME，UPDATEUSER。
每个表也只有一个 “每行之前” 触发器，因此我们可以控制必要语句的执行顺序。最重要的功能是从sequence.nextVal中设置主键。另外，其他四个公共字段也将在此触发器中设置。

如果我对其他表引用的一个表运行更新，则所有引用的表也将被锁定，尽管没有涉及主键。

请找到我的LiveSQL脚本与一个示例表结构。
不幸的是，LiveSQL的限制不允许访问v $ locked_objects或v $ lock。请在此脚本中找到具有预期结果的注释。

您能否解释一下，如果我们仅使用一个触发器，为什么所有表都被锁定？只有当我们更新键列或插入nwe记录时，有没有办法告诉数据库锁定表？

您还能解释一下这些锁对锁定表上的DML有何影响？

亲切的问候，
于尔根

嗨，于尔根，

感谢令人敬畏的测试用例。我们实际上在不久前的AskTOM上回答了一个类似的问题，但是经过10分钟的搜索，我无法找到它。

这是触发器的限制-从我可以得出的结果来看，我们基本上检查触发器内容，如果有 * 任何 * 引用主键列作为分配的目标，我们将 “假设最坏的情况”，你可能会更新它。

例如，即使我使触发器明显为false-您仍然会看到额外的锁:

SQL> CREATE OR REPLACE TRIGGER TR_BR_A
  2  BEFORE UPDATE ON A
  3  FOR EACH ROW
  4  DECLARE
  5      tmpVar NUMBER;
  6  BEGIN
  7      IF false THEN
  8         :new.ID := 0;
  9      END IF;
 10  END;
 11  /

Trigger created.

SQL> UPDATE A set description = 'Live' where id = 1;

1 row updated.

SQL> select locked_mode, OBJECT_NAME from v$locked_object l, all_OBJECTS ob where ob.OBJECT_ID =l.OBJECT_ID;

LOCKED_MODE OBJECT_NAME
----------- ----------------------------------------
          3 A
          3 B

所以我建议在这一点上分离触发器。当您到达12c时，可以完全避免这种情况，因为您可以将序列作为表定义的一部分

SQL> CREATE TABLE A (
  2    ID           NUMBER(10)    default seq_a.nextval NOT NULL,
  3    DESCRIPTION  VARCHAR2(50)  NOT NULL,
  4    CREATIONUSER VARCHAR2(50)  NOT NULL,
  5    UPDATEUSER   VARCHAR2(50)  NOT NULL
  6    );

Table created.