Toad World® Forums

Question regarding open transaction timeout


#1

Hi again,

When connected to an Oracle database, I am getting occasional messages from TDA that I have had a transaction open for a long time, and asks if I want to commit or rollback. However, I’ve only been running select queries, and haven’t done anything to update the tables.

I shortened the length of time for the timeout, just to test it out more, and also found that once the message pops up, if I just let the timer run down, although it says it is going to roll back the transaction, it doesn’t seem to, as the message will show up again a short time later (even though I’ve done nothing else in TDA).

Now, I am currently running version 2.5.1 (business policy prevents me from upgrading). However, I noticed in the release notes for 2.6, there was reference to a fix put in relating to the open transaction timeout. However, I’ve been unable to find any information on what that issue was, and if it’s related to what I’m seeing.

I’m still running some scenarios to gather data, but I thought I’d check and see if anyone had any suggestions.

Any information would be appreciated.

Thanks,

Matt


#2

Oracle has no way of detecting whether or not you have started a transaction so even if you are just running selects you can start a transaction and keep locks (Think for instance of SELECT…FOR UPDATE). So in Oracle if you execute anything to the database we assume that a transaction has been started. If you want to get rid of this warning message just set the transaction time out to something very high.

/Mauritz
Quest Software


#3

OK, so that’s why the message is popping up. Why, though, does it continue to pop up repeatedly, even though it says it is going to roll back the transaction? I tested this by opening TDA, running one query, and then waiting. The message came up several times afterward. Is it just because TDA is trying to roll back a transaction that in all likelihood does not exist?

I know that changing the timeout interval will make this go away as well. I’m just trying to understand what it is that TDA is doing.

Thanks,

Matt


#4

Oops, that is a bug. What happens if you do not click the any button but just lets the control timeout is that it will treat it as if you have pressed the “Keep Open” button in the dialog (Which means it will bug you again about it). I fixed this for the next release though so that it really does do a rollback.

/Mauritz