Take the 2-minute tour ×
Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free, no registration required.

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.

share|improve this question
    
I'll accept Aaron's answer as he was first to answer, though all answers provide valid points. Thanks everyone. –  Jalayn Nov 10 '11 at 8:34
add comment

3 Answers 3

up vote 2 down vote accepted

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.

share|improve this answer
    
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
    
You can have deadlocks when you use connection pooling in a single application. –  Aaron Digulla Nov 9 '11 at 9:35
add comment

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.

share|improve this answer
add comment

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.

share|improve this answer
    
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). –  Emmad Kareem Nov 9 '11 at 19:43
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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