On Fri, Dec 26, 2014 at 6:15 PM, Ann Harrison aharrison@... [firebird-support] <firebird-support@yahoogroups.com> wrote:

 

> On Dec 24, 2014, at 3:22 AM, brucedickinson@... [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