Re: [Firebird-devel] ODP: ODP: ODP: Inserts and FKs
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 nullFirebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] ODP: ODP: ODP: Inserts and FKs
Let's go, based on Karol's previous example. 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 should not be visible for this transaction, but FB will accept it commit; No errors at all! []s Carlos http://www.firebirdnews.org FireBase - http://www.FireBase.com.br DS> 06.09.2019 23:00, Carlos H. Cantu wrote: >> Sending screenshots of the isql sessions will not help, since what really >> matters is the >> time of starting/commit the transactions. DS>You don't need to send screenshots. Send script. Second instance of isql can be run in DS> the right moment using command "shell".Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] ODP: ODP: ODP: Inserts and FKs
06.09.2019 23:00, Carlos H. Cantu wrote: Sending screenshots of the isql sessions will not help, since what really matters is the time of starting/commit the transactions. You don't need to send screenshots. Send script. Second instance of isql can be run in the right moment using command "shell". -- WBR, SD. 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: ODP: [Firebird-devel] ODP: ODP: Inserts and FKs You can use your own example (that you sent before), just follow the comments that I marked in red, and you will reproduce the problem. Sending screenshots of the isql sessions will not help, since what really matters is the time of starting/commit the transactions. []s Carlos http://www.firebirdnews.org FireBase - http://www.FireBase.com.br Better show your commands in isql then we can reproduce problem. Regards, Karol Bieniaszewski Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
[Firebird-devel] ODP: ODP: ODP: Inserts and FKs
Better show your commands in isql then we can reproduce problem. Regards, Karol Bieniaszewski Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Inserts and FKs
Thanks Ann! So far all the opinions are that the currently behavior is wrong (or inconsistent, at last). I'll be glad if someone who has other RDBMS installed can compare how they behave with the exactly described scenario, and report the results back here. Please pay attention in the transaction isolations and make sure they are started/commit in the correct times. Thanks! Carlos http://www.firebirdnews.org FireBase - http://www.FireBase.com.br AH> Cheers, AH> Ann >> On Sep 6, 2019, at 8:24 AM, Mark Rotteveel wrote: >> >>> On 6-9-2019 01:46, Carlos H. Cantu wrote: >>> I understand that there are other scenarios where the currently FK >>> behavior is correct and makes sense, for example, in the case of >>> avoiding deleting a master record with "commited but not visible >>> childs", AH> Yes. Unique, Primary Key, and Foreign Key constraints are AH> handled in a special omniscient mode to avoid concurrent, AH> incompatible changes. Triggers and check constraints operate in AH> the mode of the user transaction. >>> but for the reported example, the currently behavior looks >>> incorrect, and for people with business logic implemented in triggers, >>> it may/will lead to incorrect results. >> >> I think you're right. You should only be able to insert records that >> reference records that are visible to your transaction. Given Tx2 started >> before Tx1 committed, the effects from Tx1 aren't visible to your >> transaction. Your insert in Tx2 should fail as the master record from Tx1 >> doesn't exist from the perspective of Tx2. AH> Interesting. In the case of inserting a child, the master must AH> be visible to the transaction doing the insert. In the case of AH> deleting a master, the existence of a child - even if uncommitted must block the delete. >> >>> Does anyone knows if this behavior is following the Standard? >> >> I don't think this behaviour is correct in view of the standard, but I >> haven't looked it up. >> AH> No, this behavior is not standard compliant. AH> Good luck, AH> Ann Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] ODP: ODP: Inserts and FKs
Title: Re: ODP: [Firebird-devel] ODP: Inserts and FKs
If you wanna test, please use isql... I don't know how flamerobin deals with transactions internally.
I just tested with FB 3 and it is the same (wrong, imho) behavior as FB 2.5.
[]s
Carlos
http://www.firebirdnews.org
FireBase - http://www.FireBase.com.br
>>tx1 starts (read commited)
>>tx1 INSERT INTO A (ID) VALUES (1);
>>tx2 starts (snapshot isolation) - How did you started tx2? If you are in isql, you need at last to run some statement, like a select, to actually start a transaction. For example, run this on the >>second isql section:
>>commit; -- making sure that if there is a transaction started, it will be closed now
>>select * from a; -- Doing this you are actually starting a transaction (tx2)
>>tx1 commits
>>tx2 INSERT INTO B (ID, ID_A) VALUES (1, 1);
>>tx2 commits – i have an error here! violation of FOREIGN KEY constraint "FK_B__A" on table "B" Foreign key reference target does not exist Problematic key value is ("ID_A" = 1)
>>or transaction wait if i specifi wait for locks.
>>I tested your steps (following my comments above) and get no error (FB 2.5).
I have tested this by 2 instances of Flamerobin.
But i have tested this under FB3 (WI-V3.0.4.32989 Firebird 3.0) not 2.5.
I have not 2.5 installed as i have migrated all to FB3.
Regards,
Karol Bieniaszewski
Firebird-Devel mailing list, web interface at
https://lists.sourceforge.net/lists/listinfo/firebird-devel
[Firebird-devel] ODP: ODP: Inserts and FKs
>>tx1 starts (read commited)
>>tx1 INSERT INTO A (ID) VALUES (1);
>>tx2 starts (snapshot isolation) - How did you started tx2? If you are in
>>isql, you need at last to run some statement, like a select, to actually
>>start a transaction. For example, run this on the >>second isql section:
>>commit; -- making sure that if there is a transaction started, it will be
>>closed now
>>select * from a; -- Doing this you are actually starting a transaction (tx2)
>>tx1 commits
>>tx2 INSERT INTO B (ID, ID_A) VALUES (1, 1);
>>tx2 commits – i have an error here! violation of FOREIGN KEY constraint
>>"FK_B__A" on table "B" Foreign key reference target does not exist
>>Problematic key value is ("ID_A" = 1)
>>or transaction wait if i specifi wait for locks.
>>I tested your steps (following my comments above) and get no error (FB 2.5).
I have tested this by 2 instances of Flamerobin.
But i have tested this under FB3 (WI-V3.0.4.32989 Firebird 3.0) not 2.5.
I have not 2.5 installed as i have migrated all to FB3.
Regards,
Karol Bieniaszewski
Firebird-Devel mailing list, web interface at
https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Inserts and FKs
Cheers, Ann > On Sep 6, 2019, at 8:24 AM, Mark Rotteveel wrote: > >> On 6-9-2019 01:46, Carlos H. Cantu wrote: >> I understand that there are other scenarios where the currently FK >> behavior is correct and makes sense, for example, in the case of >> avoiding deleting a master record with "commited but not visible >> childs", Yes. Unique, Primary Key, and Foreign Key constraints are handled in a special omniscient mode to avoid concurrent, incompatible changes. Triggers and check constraints operate in the mode of the user transaction. >> but for the reported example, the currently behavior looks >> incorrect, and for people with business logic implemented in triggers, >> it may/will lead to incorrect results. > > I think you're right. You should only be able to insert records that > reference records that are visible to your transaction. Given Tx2 started > before Tx1 committed, the effects from Tx1 aren't visible to your > transaction. Your insert in Tx2 should fail as the master record from Tx1 > doesn't exist from the perspective of Tx2. Interesting. In the case of inserting a child, the master must be visible to the transaction doing the insert. In the case of deleting a master, the existence of a child - even if uncommitted must block the delete. > >> Does anyone knows if this behavior is following the Standard? > > I don't think this behaviour is correct in view of the standard, but I > haven't looked it up. > No, this behavior is not standard compliant. Good luck, Ann > > > Fireball https://lists.sourceforge.net/lists/listinfo/firebird-devel Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] ODP: Inserts and FKs
Title: Re: ODP: [Firebird-devel] Inserts and FKs
tx1 starts (read commited)
tx1 INSERT INTO A (ID) VALUES (1);
tx2 starts (snapshot isolation) - How did you started tx2? If you are in isql, you need at last to run some statement, like a select, to actually start a transaction. For example, run this on the second isql section:
commit; -- making sure that if there is a transaction started, it will be closed now
select * from a; -- Doing this you are actually starting a transaction (tx2)
tx1 commits
tx2 INSERT INTO B (ID, ID_A) VALUES (1, 1);
tx2 commits – i have an error here! violation of FOREIGN KEY constraint "FK_B__A" on table "B" Foreign key reference target does not exist Problematic key value is ("ID_A" = 1)
or transaction wait if i specifi wait for locks.
I tested your steps (following my comments above) and get no error (FB 2.5).
[]s
Carlos
http://www.firebirdnews.org
FireBase - http://www.FireBase.com.br
I have tested this and i got an error all the time or i get waiting for lock depending on transaction settings.
I do the following:
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;
tx1 starts (read commited)
tx1 INSERT INTO A (ID) VALUES (1);
tx2 starts (snapshot isolation)
tx1 commits
tx2 INSERT INTO B (ID, ID_A) VALUES (1, 1);
tx2 commits – i have an error here! violation of FOREIGN KEY constraint "FK_B__A" on table "B" Foreign key reference target does not exist Problematic key value is ("ID_A" = 1)
or transaction wait if i specifi wait for locks.
I have also create empty triggers but no change
I suppose you do someting different. Please describe exact steeps to reproduce this.
Regards,
Karol Bieniaszewski
Firebird-Devel mailing list, web interface at
https://lists.sourceforge.net/lists/listinfo/firebird-devel
[Firebird-devel] ODP: Inserts and FKs
I have tested this and i got an error all the time or i get waiting for lock
depending on transaction settings.
I do the following:
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;
tx1 starts (read commited)
tx1 INSERT INTO A (ID) VALUES (1);
tx2 starts (snapshot isolation)
tx1 commits
tx2 INSERT INTO B (ID, ID_A) VALUES (1, 1);
tx2 commits – i have an error here! violation of FOREIGN KEY constraint
"FK_B__A" on table "B" Foreign key reference target does not exist Problematic
key value is ("ID_A" = 1)
or transaction wait if i specifi wait for locks.
I have also create empty triggers but no change
I suppose you do someting different. Please describe exact steeps to reproduce
this.
Regards,
Karol Bieniaszewski
Firebird-Devel mailing list, web interface at
https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Inserts and FKs
Thanks Mark! I really wish that FB could be improved to avoid that situation. Let's see what our friends thinks about it. []s Carlos http://www.firebirdnews.org FireBase - http://www.FireBase.com.br MR> On 6-9-2019 01:46, Carlos H. Cantu wrote: >> I understand that there are other scenarios where the currently FK >> behavior is correct and makes sense, for example, in the case of >> avoiding deleting a master record with "commited but not visible >> childs", but for the reported example, the currently behavior looks >> incorrect, and for people with business logic implemented in triggers, >> it may/will lead to incorrect results. MR> I think you're right. You should only be able to insert records that MR> reference records that are visible to your transaction. Given Tx2 MR> started before Tx1 committed, the effects from Tx1 aren't visible to MR> your transaction. Your insert in Tx2 should fail as the master record MR> from Tx1 doesn't exist from the perspective of Tx2. >> Does anyone knows if this behavior is following the Standard? MR> I don't think this behaviour is correct in view of the standard, but I MR> haven't looked it up. MR> Mark Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Inserts and FKs
APvFd> What I wanted to suggest is even more ugly ;) :-) Anyway, do you know if the described behavior fits in the SQL Standard? Do you think FB can be improved to avoid the described "flaw"? []s Carlos http://www.firebirdnews.org FireBase - http://www.FireBase.com.br Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Inserts and FKs
BTW I also agree that this shoud throw FK voilation exception on detail insert. -Eredeti üzenet- Feladó: Carlos H. Cantu [mailto:lis...@warmboot.com.br] Küldve: 2019. szeptember 6., péntek 14:13 Címzett: For discussion among Firebird Developers Tárgy: Re: [Firebird-devel] Inserts and FKs MA> If you do master and detail in separate transaction then tx2 should MA> not be started before tx1, tx2 start MUST wait for tx1 SUCCESSFUL MA> commit, otherwise your business logis FLAWED. Tx2 wasn't start before tx1. Read again my first email. []s Carlos http://www.firebirdnews.org FireBase - http://www.FireBase.com.br Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel __ Information from ESET Mail Security, version of virus signature database 19976 (20190906) __ The message was checked by ESET Mail Security. http://www.eset.com __ Information from ESET Mail Security, version of virus signature database 19976 (20190906) __ The message was checked by ESET Mail Security. http://www.eset.com Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Inserts and FKs
On 6-9-2019 01:46, Carlos H. Cantu wrote: I understand that there are other scenarios where the currently FK behavior is correct and makes sense, for example, in the case of avoiding deleting a master record with "commited but not visible childs", but for the reported example, the currently behavior looks incorrect, and for people with business logic implemented in triggers, it may/will lead to incorrect results. I think you're right. You should only be able to insert records that reference records that are visible to your transaction. Given Tx2 started before Tx1 committed, the effects from Tx1 aren't visible to your transaction. Your insert in Tx2 should fail as the master record from Tx1 doesn't exist from the perspective of Tx2. Does anyone knows if this behavior is following the Standard? I don't think this behaviour is correct in view of the standard, but I haven't looked it up. Mark -- Mark Rotteveel Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Inserts and FKs
On 06.09.2019 15:09, Carlos H. Cantu wrote: APvFd> Certainly you can repeat search for master record in trigger but IMHO APvFd> that's not beautiful solution. Not sure if I understand what you mean with "repeat search for master record". if (exists (select * from master where )) i.e. repeat what was already done by trigger The only workaround that I can think of, to not break the business logic, would be checking rows_affected after the update and if it zero, raise an exception. This would need to be done in all triggers with such logic, and I see it as an ugly workaround, not a real solution. What I wanted to suggest is even more ugly ;) Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Inserts and FKs
MA> If you do master and detail in separate transaction then tx2 MA> should not be started before tx1, tx2 start MUST wait for tx1 MA> SUCCESSFUL commit, otherwise your business logis FLAWED. Tx2 wasn't start before tx1. Read again my first email. []s Carlos http://www.firebirdnews.org FireBase - http://www.FireBase.com.br Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Inserts and FKs
APvFd> Certainly you can repeat search for master record in trigger but IMHO APvFd> that's not beautiful solution. Not sure if I understand what you mean with "repeat search for master record". The only workaround that I can think of, to not break the business logic, would be checking rows_affected after the update and if it zero, raise an exception. This would need to be done in all triggers with such logic, and I see it as an ugly workaround, not a real solution. []s Carlos http://www.firebirdnews.org FireBase - http://www.FireBase.com.br APvFd> On 06.09.2019 14:14, Carlos H. Cantu wrote: >> Re: [Firebird-devel] Inserts and FKs >> >> Hi >> >> "tx2 inserts a detail record in TableB >> Trigger on TableB tries to update master record, >> since the record is still not visible for this snapshot, >> update has null effect" >> >> I do not understand this point. If table B i detail it can only insert >> record for visible to it record in master. Or do you in your trigger >> you update whole table master not only master record of this detail? >> >> >> Trigger updates only the master record. The problem is exactly the >> fact that the insert of the detail record doesn't fail, because FK is >> out of transaction control so it can see the master record (but the >> update can't see it). APvFd> Certainly you can repeat search for master record in trigger but IMHO APvFd> that's not beautiful solution. APvFd> Firebird-Devel mailing list, web interface at APvFd> https://lists.sourceforge.net/lists/listinfo/firebird-devel Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Inserts and FKs
Why do you insert master and detail record in separate transaction? If you do master and detail in separate transaction then tx2 should not be started before tx1, tx2 start MUST wait for tx1 SUCCESSFUL commit, otherwise your business logis FLAWED. This scenario is looks like "Dirty read" : https://en.wikipedia.org/wiki/Isolation_(database_systems)#Dirty_reads. You leak uncommied (for tx2) information. -Eredeti üzenet- Feladó: Carlos H. Cantu [mailto:lis...@warmboot.com.br] Küldve: 2019. szeptember 6., péntek 1:47 Címzett: firebird-devel@lists.sourceforge.net Tárgy: [Firebird-devel] Inserts and FKs Recently I had a real situation that drove me crazy for several days while trying to find what actually happened. After lots of thinking, checking the auditing logs and chatting with Vlad, the only scenario that could explain what happened is: TableA (master) TableB (detail) There is FK between TableB and TableA tx1 starts tx1 inserts master record in TableA tx2 starts (snapshot isolation) tx1 commits tx2 inserts a detail record in TableB Trigger on TableB tries to update master record, since the record is still not visible for this snapshot, update has null effect tx2 commits (no error) The detail insert does not fail with broken FK because FK checking is out of transaction control, but the update ran by the trigger had no effect because the master record was not visible for the update. >From the consistency point of view, in this example, if the trigger could not >see the record and modify it, the FK validation should fail too. IMHO, if the >transaction isolation doesn't allow the record to be seen (at trigger level) >it should not be seen at the FK validation level too. I understand that there are other scenarios where the currently FK behavior is correct and makes sense, for example, in the case of avoiding deleting a master record with "commited but not visible childs", but for the reported example, the currently behavior looks incorrect, and for people with business logic implemented in triggers, it may/will lead to incorrect results. Does anyone knows if this behavior is following the Standard? Any comments? []s Carlos http://www.firebirdnews.org Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel __ Information from ESET Mail Security, version of virus signature database 19974 (20190905) __ The message was checked by ESET Mail Security. http://www.eset.com __ Information from ESET Mail Security, version of virus signature database 19976 (20190906) __ The message was checked by ESET Mail Security. http://www.eset.com Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Inserts and FKs
On 06.09.2019 14:14, Carlos H. Cantu wrote: Re: [Firebird-devel] Inserts and FKs Hi "tx2 inserts a detail record in TableB Trigger on TableB tries to update master record, since the record is still not visible for this snapshot, update has null effect" I do not understand this point. If table B i detail it can only insert record for visible to it record in master. Or do you in your trigger you update whole table master not only master record of this detail? Trigger updates only the master record. The problem is exactly the fact that the insert of the detail record doesn't fail, because FK is out of transaction control so it can see the master record (but the update can't see it). Certainly you can repeat search for master record in trigger but IMHO that's not beautiful solution. Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Inserts and FKs
Title: Re: [Firebird-devel] Inserts and FKs Hi "tx2 inserts a detail record in TableB Trigger on TableB tries to update master record, since the record is still not visible for this snapshot, update has null effect" I do not understand this point. If table B i detail it can only insert record for visible to it record in master. Or do you in your trigger you update whole table master not only master record of this detail? Trigger updates only the master record. The problem is exactly the fact that the insert of the detail record doesn't fail, because FK is out of transaction control so it can see the master record (but the update can't see it). []s Carlos http://www.firebirdnews.org FireBase - http://www.FireBase.com.br Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
