Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I'm using Oracle10g and I have a problem that is challenging me. The application has a timeout of 45 seconds. It accesses a user from my database and always performs the same action. The problem is that the application when a session ends by timeout, ends only in the level of application not in the database.

This causes the database to continue processing and perform the action successfully, however the application execute another call causing duplication of data. (A primary key is determined by sequence)

What is my idea? End the session when the execution of certain instruction exceed 45 seconds in the database, from this specific user. The problem is that the application uses a connection pool, and the application treats those who enter or not the database.

I can not implement it for profile, because the connections are on the bench for days, and only when necessary to make the appointment.

I need to know how long it is taking a particular query to run. My need is to know when a user, it only makes a single query in the database, it times out the application, which is 45 seconds.

Where can I get this information? With it I could determine which session closed, since a profile would be unworkable.

I apologize for my bad English. I'm still learning.

Cheers Rainier Morilla

share|improve this question
    
I'm not sure why you're rejecting profiles. It sounds like you want to set up a profile that has either a 'LOGICAL_READS_PER_CALL' or a 'CPU_PER_CALL' limit. I'm not sure what it means for a connection to be "on the bench for days" or why that prevents you from using profiles. –  Justin Cave Apr 4 '12 at 21:04
2  
Have you thought about tuning the query that is taking so long? It sounds like you're dealing with a known query that is causing the issue. Fix the cause rather than working around it? –  Phil Apr 4 '12 at 21:09
    
@Phil Reducing the query time would decrease the number of times the problem presents itself, but would not fix the architecture flaw causing the problem. The problem would continue to occur just not at an easy to diagnose rate. –  Leigh Riffel Apr 4 '12 at 21:39
    
"causing duplication of data": you should create a unique index on those columns that should be unique. Then the second call would throw an error and you don't have "duplicates". A PK on a generated number is never enough, you should always have a unique index on the real (i.e. "business") primary key. –  a_horse_with_no_name Apr 5 '12 at 6:28
add comment

1 Answer 1

up vote 4 down vote accepted

It sounds like the transaction is being committed (perhaps automatically) even when the application times out. Changing the COMMIT behavior would be the best option.

If you can't do that, you could put the database action in a package and then have the package check after it runs the action to see how much time has elapsed. If the step took more than 45 seconds then it should ROLLBACK, otherwise COMMIT.

Although this answers your question, it is a fragile solution and you really should look into changing the COMMIT behavior or causing a database disconnect when a timeout occurs.

share|improve this answer
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.