Ability to insert child record if parent record is locked but foreign key target unchanged [CORE1606]

[firebird-automations]

Submitted by: Adam Gardner (s3057043)

Assigned to: @alexpotapchenko

Relate to CORE2604
Is related to QA305

Votes: 1

You can not insert a child record if the parent record is locked, even if the foreign key target field is unchanged.

For example, using the Employee database and two instances of iSQL.

Window 1:
update employee set salary=salary+10 where emp_no=4;
[do not commit yet]

Window 2:
insert into employee_project (emp_no, proj_id) values (4, 'GUIDE');

In the first transaction, we do not modify the emp_no, so there is no reason why the second transaction should not be allowed to insert the record into employee_project. This behaviour can cause unnecessary bottlenecks.

Firebird is unable to identify which of the fields were changed, so pessimistically assumes that the emp_no may have changed. If you are in a WAIT transaction, it will succeed once the first transaction commits. If you are in a NOWAIT transaction, you will receive a lock conflict immediately.

Ideally, Firebird should only block the insert on the child if the foreign key target was involved in the update. I am also open to other possible solutions. For example, we could declare for Firebird whether a particular key field was a surrogate key. If so, Firebird could disallow updates for such fields and therefore would not need to check whether the field was locked or not.

If you need more information or further test cases, please let me know.

[firebird-automations]

Modified by: @pcisar

Workflow: jira [ 13472 ] => Firebird [ 14267 ]

[firebird-automations]

Commented by: Andrea Casati (cyberlaundry)

Same behaviour on FB2.0.3

[firebird-automations]

Modified by: @dyemanov

assignee: Alexander Potapchenko [ lightfore ]

[firebird-automations]

Modified by: @dyemanov

status: Open [ 1 ] => Resolved [ 5 ]

resolution: Fixed [ 1 ]

Fix Version: 2.5 Beta 1 [ 10251 ]

[firebird-automations]

Commented by: Philip Williams (unordained)

Isn't this change a rather big deal?

I suspect quite a few people have used the current behavior to implement locking (on purpose) -- lock the parent record by applying a dummy update to it, to be sure no new children will be added during some operation. I would generally use "id=id", but not everyone would. Besides, that doesn't actually "change" the field, so will it prevent insertions now?

The logic I remember getting from Helen on this was that once a transaction acquires a record write lock by applying any updates to it, other transactions have no FK guarantees on that row -- transaction 1 may have only updated one field to start with, but may later come back around and delete the entire record, or modify its PK! You can't assume that a transaction will only touch a record once. The lock is there to protect other transactions from assuming something that isn't guaranteed, and won't be re-verified later (no deferred constraints, particularly FK constraints.)

Example:

Transaction 1: update parent set useless_field = useless_field + 1 where id = 4; -- not an update on the PK field
Transaction 2: insert into child (parent_id, other_field) values (4, 7); -- FK check succeeds, record is inserted
Transaction 1: delete from parent where id = 4; -- an update on the PK field, but the damage is done; on-delete-cascade cannot see other newly inserted record
Transaction 1: commit;
Transaction 2: commit; -- either FB auto-deletes the record without informing transaction 1 (bad), or the record stays (bad) or you need to implement deferred constraints so you can catch this at commit time, and throw an exception here

Not really a workaround:

Transaction 1: update parent set useless_field = useless_field + 1 where id = 4; -- not an update on the PK field
Transaction 2: insert into child (parent_id, other_field) values (4, 7); -- let's assume this gives TX2 a record lock on parent=4, to prevent the parent from being deleted or relocated (pk change)
Transaction 1: delete from parent where id = 4; -- raise an exception; although TX1 should have a record lock (and does?), another transaction also does, so now it can't be deleted?

[firebird-automations]

Commented by: @hvlad

Phillip,

delete from parent will fail in both cases, because
a) fk check works independent if transactions isolation and it see all child records, even not committed
b) child record is active (not committed) so cascade action (if specified) will raise "update conflict" error

[firebird-automations]

Commented by: Philip Williams (unordained)

Vlad, thanks. To make sure I understand (documentation? what?):

If the parent record is deleted (TX1) before the child is created (TX2), the child transaction will notice the error -- it can see the parent, but the FK check sees the parent has been deleted (or its PK changed), so it fails anyway.

If the parent is deleted (TX1) after the child is created (TX2), the FK check on the parent will notice the new children and attempt updates or deletes, which will fail because of record locks held by TX2, even if the child record has since been committed. (Or it will attempt no action, and fail because of the presence of child records.) Even if TX2 commits before TX1, TX1 cannot delete the parent record, because it cannot modify the record inserted by TX2, as TX2 was not already committed when TX1 started.

Because an update to the invisible child record can never succeed, it shouldn't matter that there may be triggers defined on the child table; those triggers won't get run, so there's no opportunity for TX1 to run triggers on data visible only in TX2, leading to confusion in TX1.

So my second example was correct: TX1 will fail to delete a record on which it could reasonably think it already had a record lock by having performed an update. If a table has two separate unique fields (candidate keys), with two child tables defined with FK's referencing one of those unique fields each, a transaction can only truly acquire a record lock via a row update if it modifies (touches?) both of those fields, to ensure both FK's will fail immediately? There won't be a data-integrity problem, just a weirdness with record locks unexpectedly going missing.

[firebird-automations]

Commented by: @hvlad

Philip,

At first, there is no such thing as "record lock" in Firebird, thanks to MVCC. There are transactions, its states and rules of transactions visibility to each other.

> So my second example was correct: TX1 will fail to delete a record

Yes, and i confirmed it

> If a table has two separate unique fields (candidate keys), with two child tables defined with FK's referencing one of those unique fields each, a transaction can only truly acquire a record lock via a row update if it modifies (touches?) both of those fields, to ensure both FK's will fail immediately? There won't be a data-integrity problem, just a weirdness with record locks unexpectedly going missing.

Again, there is no such thing as "record lock ". As soon as *any* field of master record included into *any* PK\UK is modified, record marked with special flag and no child record can create reference to this master record. Yes, this is compromiss between complexity and usability. And this was also discussed in fb-devel.

If you need more details, please, re-read corresponding discussion in fb-devel ("Serious error in FB 2.0 and 2.1 Foreign Key handling" by Sean Leyne at 11 Apr 2008) and ask there.

[firebird-automations]

Modified by: @hvlad

Link: This issue relate to CORE2604 [ CORE2604 ]

[firebird-automations]

Modified by: @pcisar

issuetype: Bug [ 1 ] => Improvement [ 4 ]

[firebird-automations]

Modified by: @pcisar

Link: This issue is related to QA305 [ QA305 ]

[firebird-automations]

Commented by: @pcisar

Test created.

[firebird-automations]

Modified by: @pcisar

status: Resolved [ 5 ] => Closed [ 6 ]

[firebird-automations]

Modified by: @pavel-zotov

QA Status: No test

[firebird-automations]

Modified by: @pavel-zotov

QA Status: No test => Done successfully