Hello Ann Maybe I am not understanding correctly, but:
*Case 1:* - Transaction T1 starts with settings: READ WRITE WAIT READ COMMITTED - Transaction T1 updates a record which ID is 23 - Transaction T2 starts with settings: READ WRITE WAIT READ COMMITTED - Transaction T2 updates the same record which ID is 23 - Transaction T2 is waiting, and waiting, and waiting - Transaction T1 COMMIT - Transaction T2 COMMIT - A SELECT shows the changes that transaction T2 did *Case 2:* - Transaction T1 starts with settings: READ WRITE WAIT READ COMMITTED - Transaction T2 starts with settings: READ WRITE WAIT READ COMMITTED - Transaction T2 updates a record which ID is 23 - Transaction T1 updates the same record which ID is 23 - Transaction T1 is waiting, and waiting, and waiting - Transaction T2 COMMIT - Transaction T1 COMMIT - A SELECT shows the changes that transaction T1 did So, I can not understand your phrase: "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." Because I can update the record. Greetings. Walter. On Fri, Dec 26, 2014 at 6:15 PM, Ann Harrison [email protected] [firebird-support] <[email protected]> wrote: > > > > > > On Dec 24, 2014, at 3:22 AM, [email protected] [firebird-support] < > [email protected]> 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 > >
