> On Dec 24, 2014, at 3:22 AM, brucedickin...@wp.pl [firebird-support]
> <firebird-support@yahoogroups.com> wrote:
>
> I have two threads which constantly and at the same time are writing to this
> table:
>
> UPDATE OR INSERT INTO PARAMS (NAME) VALUES(:P_NAME) MATCHING (NAME) RETURNING
> ID;
>
>
> I've set my transaction parameters like this:
>
> FtraMain.TRParams.Add('isc_tpb_write');
> FtraMain.TRParams.Add('isc_tpb_read_committed');
> FtraMain.TRParams.Add('isc_tpb_wait');
> FtraMain.TRParams.Add('isc_tpb_no_rec_version');
>
> As far as I understand, such configuration should prevent deadlock exception
> to occur. However, deadlock still occurs from time to time:
>
> Deadlock.
> Deadlock.
> Update conflicts with concurrent update.
> Concurrent transaction number is 57258.
>
The "Deadlock" error is somewhat misleading. This is not a classic deadlock of
the sort that databases that implement lock-based concurrency get. However,
the solution is the same as for a deadlock (i.e. roll back and retry your
update) so at a high level, deadlock isn't a bad description.
What you're seeing is Firebird's way of avoiding dirty writes in a system with
multiple record versions. The rule is that if the most recent version of a
record was not committed when your transaction started, then you can't update
that record. In "concurrency" mode, which provides a stable snapshot of the
database, the rule is the minimum necessary to avoid losing concurrent writes.
"Read_committed" mode was added later to meet some programmers' expectation
that a transaction would always see the most recently committed version of a
record, and to hell with consistency. However the rules for update conflicts
were not changed at the same time, so even if you can see a change that's
committed now but wasn't when you started, you still can't update that record.
Good luck,
Ann
