ODP .Net Connection Pool Problem on Web Application

[mingki]

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.DataAcce ss.Client.Oracl eException Connection
request timed out at ...
Oracle.DataAcce ss.Client.Oracl eConnection.Ope n()
...."

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
OracleConnectio n.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.Threadi ng.ThreadAbortE xception: Thread was being
aborted. at Oracle.DataAcce ss.Client.OpsSq l.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 OracleDataReade r,
OracleCommand, OracleParameter s and OracleConnectio n 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/downloa...dAbortTest.zip

in which there are a .Net solution, a plsql script
"pkg_test_threa d_abort.plsql" for the stored procedure being called
against the "all_object s" table and a screenshot
"Database_Sessi ons_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

[mingki]

Hi all, I think I have found a reason for this problem:

The problem is caused by, when the IIS server's "executionTimeo ut" for
httpRequest is reached, IIS will call Thread.Abort() to stop the
processing thread for the Request.

For instance, If a web page is requested, meanwhile the database is
very busy, the Database can't return all the result to the web
application within 40 sec (the default value of executionTimeou t in
machine.config) , this thread will be aborted!

I have just read the Release note of ODP .NET 10.2.0.1.0, in the
section "TIPS, LIMITATIONS AND KNOWN ISSUES", it says:

8. Thread.Abort() should not be used, as unmanaged resources may remain
unreleased properly, which can potentially cause memory leaks and
hangs.
It seems that we can just avoid the problem by setting the
executionTimeou t value to a reasonablily large value. However, this
will greatly affect the web server performance as some Threads will be
hold and they are not able to serve other requests.

Please advise!

Regards,
Alex