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