Hi all.

In FB 1.5 and older when detail record is inserting and master record
is updating at this time a lock conflict exception is raised. This is prevent
possible FK violations but is too strong - exception raised regardless of
old and new values of key fields. For example

create table master (
id_master int not null primary key,
name varchar(255)
)

create table detail (
id_detail int not null primary key,
id_master int not null referencing master (id)
)

insert into master values (1, 'first')
commit

start transaction 1
update master set name = 'xxx'
when id_master = 1

start transaction 2
insert into detail values (1, 1)

at this point we have "lock conflict" error if transaction 2 is in
"no wait" mode or transaction 2 will wait for transaction 1 end
if it is in "wait" mode (of course if transaction 1 will be commited
we got the same "lock conflict" error).

As you see FK violation is not possible in such scenario
regardless of how transaction 1 will be ended (commit or rollback)

Some time ago i made patch for this problem. Idea is to compare
old and new values of that fields from master record on which detail
are referenced and if they are equal allow to insert detail record. Old
values evaluated by reconstructing backversion of master record.
You can see it in VIO_get_current.

Unfortunately i miss case when master record updated more than
one time in the same transaction. In such case backversion on disk is
not a real backversion. Real backversion exist only in undo log and can
not be reached by this check. Manipulating with savepoint i can broke FK.

So i decide to rollback my patch (i'll do it today).

But i have a proposition which allow to complete my fix. I propose to
introduce new record flag which will indicate that at least one of key fields
is changed and multiply updates is present. If there are no multiply updates
i'll read old values from backversion and compare key fields. If there are
no key fields change in any updates i'll don't check old values and accept
detail record. If one of secondary updates change any of the key fields i'll
reject detail record. This is not so precise as i want but not so restrictive
as it is in current Firebird versions (1.5.x and below)

If there are no objections i'll implement it after first alpha release.
If there are any other propositions a like to hear it.


Regards,
Vlad