We have a process that may last between a few seconds and a few minutes. As such, we may update just a few records or thousands of them. The process can be committed only when everything has been done correctly.
Recently we have begun having problems when attempting to commit transaction as they were marked as "rollbackOnly". I've discovered that in fact the transaction had been marked "rollbackOnly" because it had timed out a bit earlier (not clear to see in the log files as our application server is Websphere and its transaction manager just gives a one-line warning), and as such it could not be committed. Of course it makes sense that you cannot commit a transaction that has timed out.
The problem is in fact that we never set the transaction timeout in transaction definitions, and that in that case the value of the timeout is the one set at the application server level (see Websphere's transaction settings, the paragraph on "Total transaction lifetime timeout"). That value is 2 minutes and sometimes we exceed it.
Now, that explains the setting (and in case somebody encounters a "rollbackOnly" transaction, I've provided here a possible explanation).
I'm tempted to increase the timeout, but I've also seen in the documentation that the timeout can be set to 0, meaning there is no timeout.
As such, my question is: what are the dangers of setting an unlimited transaction life timeout ?
Note that I'm aware of programmatic/database solutions to my problem like for example working on temporary tables, and reversing data from temporary tables to the real tables when we know everything has been done right (using PL/SQL for example). I'm just curious about the transaction timeout thing.
Best Answer
Most databases don't have automatic deadlock detection, so the timeout is the only way to stop those. A timeout does harm long running transactions, too, but without this, the first deadlock would stop your application.