rollback of drop FK-constraint in session #1 can lead to violation of FK due to session #2 ------------------------------------------------------------------------------------------
Key: CORE-3653 URL: http://tracker.firebirdsql.org/browse/CORE-3653 Project: Firebird Core Issue Type: Bug Reporter: Pavel Zotov SESSION #1 ----------------- isql -n test0.fdb SQL> recreate table tdetl(id int primary key, pid int); SQL> recreate table tmain(id int primary key); SQL> commit; SQL> insert into tmain values( 1); SQL> commit; SQL> insert into tdetl values( 1, null); SQL> alter table tdetl add constraint tdetl_fk foreign key (pid) references tmain(id); SQL> commit; SESSION #2 ----------------- isql -n test0.fdb SQL> alter table tdetl drop constraint tdetl_fk; SQL> SESSION #1 ----------------- SQL> update tdetl set pid=-111 where id=1; SQL> commit; -- PASSED! though there is no confirmation of dropping FK in SESSION #2... SESSION #2 ----------------- SQL> rollback; SQL> exit; SESSION #1 ----------------- SQL> exit; Hereafter in single session: isql -n test0.fdb SQL> set list on; SQL> select * from rdb$relation_constraints rc where rc.rdb$constraint_name = 'TDETL_FK'; RDB$CONSTRAINT_NAME TDETL_FK RDB$CONSTRAINT_TYPE FOREIGN KEY RDB$RELATION_NAME TDETL RDB$DEFERRABLE NO RDB$INITIALLY_DEFERRED NO RDB$INDEX_NAME TDETL_FK -- yes, old FK is alive SQL> insert into tdetl values(2,-222); Statement failed, SQLSTATE = 23000 violation of FOREIGN KEY constraint "TDETL_FK" on table "TDETL" -Foreign key reference target does not exist SQL> set list off; SQL> select d.*,m.id master_id from tdetl d left join tmain m on d.pid=m.id; ID PID MASTER_ID ============ ============ ============ 1 -111 <null> As result we have data in child table that are not present in parent. This effect also appears for TIL = read committed; // i.e. wait = ON, by default The only TIL when database is protected from this is read committed no wait. Checked on: 1) 2.0.6.13266 2) 2.1.4.18393 3) 2.5.2.26387 -- This message is automatically generated by JIRA. - If you think it was sent incorrectly contact one of the administrators: http://tracker.firebirdsql.org/secure/Administrators.jspa - For more information on JIRA, see: http://www.atlassian.com/software/jira ------------------------------------------------------------------------------ RSA(R) Conference 2012 Save $700 by Nov 18 Register now http://p.sf.net/sfu/rsa-sfdev2dev1 Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel