Oracle / SQL /ASP lockups
Posted on 2005-03-09
I have a application where all the users connect via a single oracle user to an oracle database (web based app)
What happens here is that I open a connection on app start and close it on app close, and every other web user that logs in uses this physical connection ( I let oracle handle the connection pooling)
The problem arises if one of the users inadvertantly runs a query which either hangs/takes long time, etc.
The CPU on the server also goes to 100% and eve if I close all the explorer windows, nothing happens as the asp process is still trying to make the oracle call ..
The only solution is then to kill the aspnet_wp.exe process and connect again.
This is obviously not a solution when the app goes out into the world. What is the correct way of handling problems like this?