1
CREATE TABLE t1 (
     t1pk NUMBER PRIMARY KEY NOT NULL
    ,t1val NUMBER
);
CREATE TABLE t2 (
     t2pk NUMBER PRIMARY KEY NOT NULL
    ,t2fk NUMBER
    ,t2val NUMBER
    ,CONSTRAINT t2fk FOREIGN KEY (t2fk)
        REFERENCES t1 (t1pk) ON DELETE CASCADE
);

INSERT INTO t1 (t1pk, t1val)
VALUES (1, 1);

INSERT INTO t2 (t2pk, t2fk, t2val)
VALUES (1, 1, 1);

COMMIT;

CREATE SEQUENCE seq1
    MINVALUE 1
    MAXVALUE 999999999999999999999999999;

CREATE OR REPLACE TRIGGER trg1
BEFORE
INSERT -- Problematic code.
OR UPDATE
OR DELETE ON t1
FOR EACH ROW
BEGIN
    IF (INSERTING)
    THEN
        -- Problematic code.
        :NEW.t1pk := seq1.NEXTVAL;
    END IF;
END trg1;
/

Session 1:

UPDATE t2 -- Table 2!
SET t2val = t2val;

Session 2:

UPDATE t1 -- Table 1!
SET t1val = t1val;

In session 2 the update does not come back and waits until session 1 closes the transaction with commit or rollback. This is not what I do expect. The reason seems to be the trigger code with pk generation from sequence. If I remove that sequence code the update in session 2 does not wait and come back while the transaction of session 1 is still open. What is wrong?

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

Toru
  • 905
  • 1
  • 9
  • 28

0 Answers0