Hi Vlad,

>
> Second transaction have no knowledge which fields was changed by
> 1st transaction so it must wait for stable master record version.
>
> > Is this an implementation limitation in Firebird or a deliberate

design choice?

> > If it is a design choice, then what is the reason?
>
> I'd said it is implementation details. Not looked what standard say

If I understand you correctly, it is as I suspected. Firebird as an
implementation artifact (rather than a design choice). This artifact
is that there is no way for Tr2 to know that even though the parent
record has been modified in Tr1, the FK target itself has not and is
safe. It must therefore be pessimistic.

Unfortunately for me, this pessimism is unnecessary as our primary
keys are surrogates that never change by design.

> You may add it to the tracker of course but do not expet quick fix

Of course not.
--

So currently I have three workarounds:

1. Drop the foreign key constraint, and use triggers on the master and
detail tables to replicate any update and delete action.

Two problems with this. Firstly the relationships become non-obvious.
Secondly, there are transaction isolation issues for say uncommitted
deletes on the master table which would not be allowed under a
declared constraint, but due to MGA the child would still see a valid
value.

2. Split master tables into two so that the master records themselves
never need to be updated.

3. Use wait transactions in this particular GUI and cross my fingers
that other transactions I may have no control over are well behaved
and commit in reasonable time.

I imagine that I am not Columbus here. This must have bitten other
people as well. Are there any other workarounds that I have not
considered?

TIA
Adam