0

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.

0
2

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.

2
  • Thanks, detecting deadlocks seems to be a good reason (although in our case, our application is the only one accessing that database so we should be fine). If I don't get other answers, I will accept this one.
    – Jalayn
    Nov 9 '11 at 9:33
  • 2
    You can have deadlocks when you use connection pooling in a single application. Nov 9 '11 at 9:35
2

The problem is you may lock resources for a long time. Even if your row locks don't get escalated to table locks, you run the risk of locking groups of records in both the tables and any associated indexes, which can prevent other processes from accessing them. And if your program hangs or gets killed without closing the connection, those tables could be locked for hours.

You could try raising the timeout to (say) five minutes and see if that helps, but I'd be really wary about disabling it completely.

2

Let me respond you with a quote:

Infinity is extremely long. Even by the time the Sun turns into a red giant (it explodes), it's still a looong way to Infinity. The average programmer dies at 72. There is simply no real-world situation, where we want to wait that long. Infinite timeout is just an absurd thing. Use an hour, day, week, month, 1 year, 10 years. But not Infinity. (source)

Just think about what you want to allow your users, and that depends of the consequences it has to the system. Starting a transaction, leaving the web browser open, and … go for lunch? Go home? For the evening? For the week-end? For a three-week vacation? … Then, set your time-out accordingly.

1

This is a side point, but I think it is important. If you application in an on-line application with live users using it, I find it strange that you have transactions that need to update thousands of records while you have users logged on.

Either your database design can be modified or you should consider running a batch process on the background maybe. I suggest you consider this first before changing the database settings.

The danger of changing the setting is as you have mentioned; users can click a mouse or hit enter and wait for long time without response. This is not OK in many cases.

2
  • The application is not online, as it is a "system" process if we can call it that way, there is no interactions with users. But +1, I did not precise it and you make a valid point.
    – Jalayn
    Nov 9 '11 at 18:26
  • @Jalayn, thanks for your response. If the application is not an on-line application, then there are ways to quickly load databases in batch. For example, you can partition the data table and add the new data simply as a new partition in less than 1 second even if you have 1000s of rows (assuming you can design your table as a partitioned table).
    – NoChance
    Nov 9 '11 at 19:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.