Jonatan Lauritsen wrote:
I have Firebird 2.x database which from time to time has rather long transactions - up to 30 seconds and so, there is high probability that the same record will be updated from the several different transactions and the lock conflict - deadlock exeption will be raised. I am currently using nowait transactions.
The question is - what is the best option in this case - to use wait or nowait transactions? My feeling is that it is better to invest in optimization and decreas of the running time and keep the nowait mode. Btw, who needs to have two or more transactions in queue.
The other problem with wait transactions can be the fact that database is used by several applications and not all of them will migrate from no-wait to wait transaction. Can this be an issue somehow?
Which is the best option in no-wait vs wait transactions and which option does emulate the BDE transaction behaviour most closely?
Ann W. Harrison answers:
Generally I prefer wait transactions to no-wait to avoid "live lock" - the problem that occurs when Transaction A has a resource that Transaction B needs. B tries, fails, retries, fails again and so on until A finally releases the resource. When B is a wait transaction, it sits quietly until A releases the resource, then either succeeds (if A failed) or gets and error if A succeeded. The wait case avoids a lot of pointless cycles.
I have no idea how BDE transactions behave ... Borland had some very imaginative engineers.
There should be no problem mixing wait and no wait transactions - some will use more resources, some will return errors to the user (or whatever is on the other end) sooner.
Jonatan Lauritsen reply:
Just some notes.
- Almost all of A-transactions end with success in my case, so almost always B-transactions will fail with deadlock message anyway. Therefore the wait mode has no advantages in my case.
- There are floating around some stories from my clients that the deadlock messages can remain in database up to the restart of the Firebird server, it is said that backup/restores is needed in some times. Is it really so? It would be nice to get some confirmation to it. As I understand, then any problems with locks should be removed when the client rollback transaction or in the worst case disconnect from the database.
- But what to do with concurrent updates? Is it possible purely in Firebird (2.1.x) implement some kind of transaction queue? So that all the work is done by nested transactions but only when the required records do not have the locks on them. Maybe there is already available some queueing middleware for this. In the worst case it can be implement as DataSnap server, isn't it?
Ann W. Harrison answers:
1) Almost all of A-transactions end with success in my case, so almost always B-transactions will fail with deadlock message anyway. Therefore the wait mode has no advantages in my case.
Actually, I think it does, depending on what B is trying to do. If B can decide that it really doesn't care about the record A changed then fine. If B really really wants to change that record, so rolls back and retries the change, then B may get 15 error before A finally commits. Each of those 15 retries is wasted cycles and possibly wasted I/O.
2) There are floating around some stories from my clients that the deadlock messages can remain in database up to the restart of the Firebird server, it is said that backup/restores is needed in some times. Is it really so?
It absolutely should not be. If there's a conflicting transaction that they haven't found and terminated, then yes, restarting the server will clear the conflict, but so will finding and terminating the transaction.
Firebird reports two different types of deadlock: one has a primary error of Update Conflict and represents a conflict between two user transactions. The other, which may be reported as an internal error first, then deadlock, is a bug in the code - meaning that it should never happen. Firebird's internal structures should be deadlock free. If your users are finding internal deadlocks (which is unlikely, given the amount of use the code has had over the decades) then they are seeing a bug and should report it.
It would be nice to get some confirmation to it. As I understand, then any problems with locks should be removed when the client rollback transaction or in the worst case disconnect from the database.
User level contention is
3) But what to do with concurrent updates? Is it possible purely in Firebird (2.1.x) implement some kind of transaction queue? So that all the work is done by nested transactions but only when the required records do not have the locks on them. Maybe there is already available some queueing middleware for this.
That's sometimes implemented with a gateway record - something you must be able to update before you can proceed with what you're actually trying to do. Someone may have a better solution based on newer versions of Firebird. SELECT ... FOR UPDATE WITH LOCK might work, or it might just move the problem.