Re: [Firebird-devel] ODP: ODP: ODP: ODP: Inserts and FKs
> On Sep 7, 2019, at 8:58 AM, Karol Bieniaszewski > wrote: > > You have right, there is a bug and the big one! > I suppose that index of Foreign Key is not validated by existence of value in > the record itself and its (the record version) transaction numer is not > compared to snapshot number. > It's also an old bug, probably dating to the implementation of foreign keys in InterBase. The obvious implementation - validate the foreign key in the context of the client transaction - fails miserably in snapshot mode when the parent record is deleted by one transaction and a matching child record is inserted by a second concurrent transaction. That leads to orphaned child records, which is very wrong. The next possible implementation is to use the same internal omniscient mode that maintains unique and primary key constraints. The omniscient mode sees the current state of the database is that there is a committed parent record that matches the proposed insert. That eliminates the orphan child problem, but introduces the problem Carlos discovered. The transaction that stores the child record can "see" the master record for the purpose of validating the relationship between the two records, but for no other purpose. That's slightly obscure case - updating the master when a child is stored tends to create a hotspot - but it's certainly legitimate. Good luck, Ann Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
[Firebird-devel] ODP: ODP: ODP: ODP: Inserts and FKs
You have right, there is a bug and the big one! I suppose that index of Foreign Key is not validated by existence of value in the record itself and its (the record version) transaction numer is not compared to snapshot number. Please report a bug to the tracker. regards, Karol Bieniaszewski Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
[Firebird-devel] [FB-Tracker] Created: (CORE-6138) Inconsistent behavior regarding visibility of master record on detail inserts
Inconsistent behavior regarding visibility of master record on detail inserts - Key: CORE-6138 URL: http://tracker.firebirdsql.org/browse/CORE-6138 Project: Firebird Core Issue Type: Bug Components: Engine Affects Versions: 2.5.9, 3.0.4, 3.0.3, 3.0.2, 3.0.1, 3.0.0 Reporter: Carlos H. Cantu 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. -- 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 Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] ODP: ODP: ODP: Inserts and FKs
Title: Re: [Firebird-devel] ODP: ODP: ODP: Inserts and FKs If you had understood the case, you would have noticed that the transaction in isql-1 doesn't matter. Either readcommited or snapshot will show the same result. That's why I didn't mind about running set transaction in my step-by-step instructions. isql-2 must be snapshot, and this is the default isolation for isql transactions (so, no need for set transaction). Point is: there is an inconsistency in the currently Firebird behavior that can be easily reproduced. I'll open a ticket in the tracker. Since I have zero knowledge about FB code, I cannot fix it by myself. IMHO, this ia a serious problem that deserves attention. []s Carlos http://www.firebirdnews.org FireBase - http://www.FireBase.com.br Why do you think that one isql have transaction read commited but second have snapshot? I do not see your transaction command. If you depend on automatic transactions started by isql, then both are the same, no randomness... Regards, Karol Bieniaszewski Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
