ODP .Net Connection Pool Problem on Web Application
- From: mingki@xxxxxxxxx
- Date: 12 Feb 2006 20:09:05 -0800
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
.
- Follow-Ups:
- Re: ODP .Net Connection Pool Problem on Web Application
- From: Mario Pareja
- Re: ODP .Net Connection Pool Problem on Web Application
- From: William \(Bill\) Vaughn
- Re: ODP .Net Connection Pool Problem on Web Application
- From: Frans Bouma [C# MVP]
- Re: ODP .Net Connection Pool Problem on Web Application
- Prev by Date: Re: Best Way of Deleting All Rows in a DataView
- Next by Date: RE: No Answers In MSDE Group, Trying Here DB Access From Server Explorer
- Previous by thread: Need to close DataReader?
- Next by thread: Re: ODP .Net Connection Pool Problem on Web Application
- Index(es):
Relevant Pages
|
