Pete2003
asked on
Oracle / SQL /ASP lockups
Hi All,
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?
Txs
Peter
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?
Txs
Peter
ASKER
Ok please explain this to me as this is quite important ...
Are you saying that if I do a database connect a dnt then disconnect it does not close the connection ?
How does that work .. and how can I verify that it actually is pooled ?
This is important as I'll have about 400 Intranet users on this site ...
Txs
Are you saying that if I do a database connect a dnt then disconnect it does not close the connection ?
How does that work .. and how can I verify that it actually is pooled ?
This is important as I'll have about 400 Intranet users on this site ...
Txs
ASKER
Does this also sort out my 'hanging' problem ?
Will the other pages just open new connections if this one is broken ?
Will the other pages just open new connections if this one is broken ?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Does this also sort out my 'hanging' problem ?
Yes....
itsvtk
Yes....
itsvtk
ASKER
Will check out 1st thing in the morning .. and just made the Q 500 pts just in case :)
ASKER
Ok this doesn't work , as if I have 10 concurrent users using the application oracle opens 10 connections.
ASKER
Also what do I do if I have ascx components on my page ... they also access the database ... do they open their own connections ? if not what do u recommend ?
ASKER
itsvtk .. u still there :)
ASKER
ok sorted it out myself .. but u pointed in right direction ... txs anyway
You have to use one connection for one page... on page_load you create a connection, on page_unload close the connection.
it will improve the performance. Actually when u close the connection it will not be closed. It will be stored in pool.
Next time ( in next page page_load event ) it will get the the connection from pool.
itsvtk