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
