Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> ODP .Net Connection Pool Problem on Web Application

ODP .Net Connection Pool Problem on Web Application

From: <mingki_at_gmail.com>
Date: 12 Feb 2006 20:10:52 -0800
Message-ID: <1139803852.591013.239440@z14g2000cwz.googlegroups.com>


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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US