Submitted by: @WarmBooter

Votes: 3

During a master-detail insert using two different transactions, where the detail transaction is snapshot, FB allows the detail insert even when the master record is not visible for the detail transaction. It can be easily reproduced with the following steps:

Open two isql sessions (isql1 and isql2) connected to the same database, and follow the sequence of commands in this order:

isql1:
CREATE TABLE A
(
ID BIGINT NOT null PRIMARY KEY);

CREATE TABLE B
(
ID BIGINT NOT null PRIMARY KEY,
ID_A BIGINT NOT NULL
);

ALTER TABLE B ADD constraint FK_B__A FOREIGN KEY(ID_A) REFERENCES A(ID) ON UPDATE CASCADE ON DELETE CASCADE;

commit;
INSERT INTO A (ID) VALUES (1);

isql2:
commit; -- to be sure that if there is active transaction, it will be ended now
select * from a; -- just to be sure that snapshot transaction is started now

isql1:
commit;

isql2:
INSERT INTO B (ID, ID_A) VALUES (1, 1); -- This SHOULD break, since master is not visible for this transaction, but FB will accept it
-- Any trigger based action against the master record will have null effect, but the detail insert will not fail!
commit;

PS: For people with business rules on triggers, this behavior may lead to wrong results.

Commits: 4a322b3 7e70c01