|Subject||Re: [firebird-support] Deadlock exception occurs but it shouldn't?|
> On Dec 24, 2014, at 3:22 AM, brucedickinson@... [firebird-support] <email@example.com> wrote: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.
> 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:
> As far as I understand, such configuration should prevent deadlock exception to occur. However, deadlock still occurs from time to time:
> Update conflicts with concurrent update.
> Concurrent transaction number is 57258.
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.