Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> ODP .Net Connection Pool Problem on Web Application
Hi Developers,
I am a .Net developer of a Large Online Retailling Company. I would like to have your help on a Connection Pool issue.
Recently we have developed a Web Application on ODP .Net to work with Oracle Database 10g. To gain the performance, connection pool enable is a must. Everything work fine throughout the development period and the performance is Great, however while the Web Application is deployed to the production, sooner or later (a few minutes or a few days) all the Connections in the connection pool will be unavailable and the ODP .NET keeps throwing:
"The Exception: Oracle.DataAccess.Client.OracleException Connection
request timed out at ...
Oracle.DataAccess.Client.OracleConnection.Open()
..."
It seems to me that all the Connections in the Connection Pool are busy, as a result once the "Connection timeout" period is over, the OracleConnection.Open() throws this Exception.
However, when I look into the sessions information at the Database, all the Sessions (Max Pool Size=40, for instance) are in Wait Events - SQL*Net message from client for a long long time.
I can just find a reason for this: In the Web Application, every
concurrent incoming requests is handled by a separating thread
concurrently. For some pages, it may hit a long query in the database.
For some reason, those pages will be Timeout and IIS 6.0 will call
"Thread.Abort()" to stop the corresponding thread. I can catch
Exception "System.Threading.ThreadAbortException: Thread was being
aborted. at Oracle.DataAccess.Client.OpsSql.ExecuteReader( ... " at the
DataAccess Layer.
To reproduce the problem in a simplier program, I have created a long-running query aginst the all_objects table in DB and started 20 threads for it. At the middle of each Thread Executing, I issue the Thread.Abort(). After all threads are aborted and all OracleDataReader, OracleCommand, OracleParameters and OracleConnection are disposed in the finally block, I tried to run the query but it will fail to obtain a connection from the connection pool.
If I set "pooling = false", there will be no problem.
The testing program can be downloaded at: http://ki-gallery.dyndns.org/download/ThreadAbortTest.zip
in which there are a .Net solution, a plsql script
"pkg_test_thread_abort.plsql" for the stored procedure being called
against the "all_objects" table and a screenshot
"Database_Sessions_Status.jpg" about the idle sessions in the database.
Please try to use Anti-Virus program to scan the zip package :)
I thank you for your help !
Regards,
Alex
Received on Sun Feb 12 2006 - 22:10:52 CST