November 15th, 2010, 07:29 AM
Dealing with limbo transactions
We are using FB 2.5 (x64, W2k3, 20GB) and periodically get the situation where one or several tables get stuck at a specific record and fetching records after the given record takes hours to complete. But once it fetches to the end of the table, the table gets functional.
What we observed to be the cause of this were stuck transactions, which we terminated by end-tasking windows processes behind them.
Would you suggest a way to terminate limbo transactions (deleting mon$statements doesn't help) without having to terminate corresponding processes?
Is there a way that we could auto-terminate all transactions that are taking longer than the specific duration?
November 15th, 2010, 07:21 PM
Without laboring the obvious, you need to fix whatever is requiring this trauma.
Presumably this drastic action is leaving your session in limbo until it times out.
I am doubtful that any global setting to ease this situation is going to be ideal for normal processing.
If it is really taking hours I suggest you try asking all users to exit and stop and start the server - or just do it. At least try it to see if that fixes the situation in which case it is (presumably) an open session issue.
Just some (educated) guesses.