> Again, thank you guys - I've found the issue in a application that started a > "read-only" transaction to update some labels on a form and keeping the OAT > stuck. > > With my luck, I closed the app and froze the poor server, now trying to sweep > 7 days worth of transactions from all other tables... ouch - I was not > popular. All in all, I made some program changes, the database is back up > and running and the OAT seems to be moving forward slowly as it should. > > What a great day this is - this has been troubling us for a while now and we > could never pin point the problem.
Additionally, I would take a step back and re-think transaction management in your client applications in general. According to your header page statistics, you had > 50 mio transactions in 7 days, which means in your example approx. 83 transactions / second (if my math doesn't fail *g*) in case of an 24/7 environment. The transaction ID is a 32-bit integer, thus you can have ~ 2 bio. transactions until a backup/restore of your database is needed, which is ~ 280 days after your last restore. Keep an eye on that. -- With regards, Thomas Steinmaurer http://www.upscene.com/ > Regards, > Wim > > --- In firebird-support@yahoogroups.com, Svein Erling Tysvær > <svein.erling.tysvaer@...> wrote: >> >>> Thank you very much for your responses - this makes sense. We have a couple >>> of server side applications that run >>> constantly. Although, they should all be closing their connections. We >>> will investigate further. >>> >>> Could this somehow be related to the .NET driver with connection pooling? >>> The one application that I suspect >>> starts and commits small incremental transactions, but uses connection >>> pooling? >> >> A common reason would be using CommitRetaining rather than Commit. Although >> CommitRetaining can be convenient, it stops the OAT from moving on so you >> must ascertain that a hard Commit is used occasionally. Connection pooling >> by itself shouldn't cause your problems, unless there's also some kind of >> "transaction pooling" (but a transaction can span several connections). Note >> that the transaction does not have to do any modification to the database, >> even SELECTs can make the OAT stuck (the one exception is that transactions >> that are read only AND read committed do not stop the OAT from moving). >> >> Actually, we had a similar (though not related) problem recently, which >> turned out to be due to having a TIB_Transaction (IB Objects) with >> AutoCommit, and then in code do 'if TIB_Transaction1.TransactionIsActive >> then TIB_Transaction1.Commit'. Due to the AutoCommit, TransactionIsActive >> was false, so no hard Commit was done. AutoCommit did CommitRetaining, so >> the OAT got stuck until the program terminated. Our users typically start >> that program in the morning and closes it at the end of the day, so we only >> had one day of delay. Your gap indicates that your problematic transaction >> (well, there could be several problematic transactions, you'll find out) was >> started shortly after the restore, so programs started later than about July >> 4th are not suspected. >> >> Set >> > > > > > ------------------------------------ > > ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ > > Visit http://www.firebirdsql.org and click the Resources item > on the main (top) menu. Try Knowledgebase and FAQ links ! > > Also search the knowledgebases at http://www.ibphoenix.com > > ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ > Yahoo! Groups Links > > >
