>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. > >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. > >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?
Hei Jonatan! There's more to transactions than just wait/nowait. Transaction isolation is one aspect that hasn't been mentioned (you may look here: http://www.ibexpert.net/ibe/index.php?n=Doc.TransactionOptionsExplained), even though I don't think changing this by itself will fix your problem (changing from snapshot to read committed may reduce the frequency of the problem a little bit). My idea of transactions is that they should comprise a logical unit of work, hence I wonder why do you put 30 seconds of work into one transaction if they're not related (I guess they're not related since you want things to be skipped if records have locks). Sure, I sometimes update a million rows and take 100000 of them in the same transaction even though they're not related, but I wouldn't do this if I expected there to be a concurrency issue (deadlock is if two or more transactions prevents each other from committing, it is a concurrency issue if it is only one transaction preventing another from committing). To prevent concurrency issues in our main applications, we typically have status fields (one field saying whether it is free, reserved or finished and another field saying who has reserved it) which we set (and commits, so that it is visible to other transactions) before the main update process starts. If the update of the status field succeeded, we know that that actual record is reserved for our use, if not, we know that we have to try another. Then we just change the status fields again once the update process finishes. A simple solution that works in our case. If you e.g. have some summary table to update, then an alternative is to use INSERT rather than UPDATE (where you insert with a positive value if you want to increase a count or sum and negative value if you want to decrease). Though you haven't mentioned anything about summary tables, so I won't say anything more about this yet. HTH, Set
