|
dev
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
ODP .Net Connection Pool Problem on Web ApplicationI 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 min***@gmail.com wrote:
Show quote > Hi Developers, You set maxpool to 10 but you start 20 threads, which I think will> > 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 cause problems as all 20 will want a connection. Also, the connection string is very long. Be aware that to re-use a connection from the pool, the connection string has to match exactly with the connectionstring of a live connection in the pool. So I'd suggest to store the data you have in the connection string now in a tns file, or define the name with a network setup ( I do that too, works without problems, I never have to use such a long connection string). FB -- ------------------------------------------------------------------------ Get LLBLGen Pro, productive O/R mapping for .NET: http://www.llblgen.com My .NET blog: http://weblogs.asp.net/fbouma Microsoft MVP (C#) ------------------------------------------------------------------------ thanks, FB!
yes, I have started 20 thread to get the connections from pool. However, all of them will be aborted and the 10 connections in pool are supposed to be released back to the pool . Unluckly it seems to me that all the connections in the pool are no longer available unless the application is restarted. I expect that 10 of those connections are not being closed...
-- Show quote____________________________________ William (Bill) Vaughn Author, Mentor, Consultant Microsoft MVP INETA Speaker www.betav.com/blog/billva www.betav.com Please reply only to the newsgroup so that others can benefit. This posting is provided "AS IS" with no warranties, and confers no rights. __________________________________ <min***@gmail.com> wrote in message news:1139883772.188048.87570@g44g2000cwa.googlegroups.com... > thanks, FB! > > yes, I have started 20 thread to get the connections from pool. > However, all of them will be aborted and the 10 connections in pool are > supposed to be released back to the pool . > > Unluckly it seems to me that all the connections in the pool are no > longer available unless the application is restarted. > In the source code, the OracleConnection instance should be disposed by
the using() block: using (OracleConnection dbConnection = new OracleConnection(blah)) { } the connection is supposed to be disposed, please let me know if I got any mis-understanding about the use of using() {}. Hi FB,
In addtion, I am sure, and you may have a try, that if I set "pooling=false", the above program is working fine. Regards, Alex You're describing a (very) typical problem when working with a .NET (ASP)
application. I would read the article I wrote on handling the Connection pool. It addresses these issues. See http://www.betav.com/sql_server_magazine.htm -- Show quote____________________________________ William (Bill) Vaughn Author, Mentor, Consultant Microsoft MVP INETA Speaker www.betav.com/blog/billva www.betav.com Please reply only to the newsgroup so that others can benefit. This posting is provided "AS IS" with no warranties, and confers no rights. __________________________________ <min***@gmail.com> wrote in message news:1139803745.149382.300430@g43g2000cwa.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 > I have seen a very similar issue come up when using SQL connection and
leaving them open. It is possible that only one connection in your code fails to close the connection, but if that piece of code is executed several times over, you will receive this error. I have run into this problem when using data readers for fetching SQL records. Any time you open a connection or use a data reader, it must be followed by a Close call. Interestingly enough, I was occasionally still receiving errors of the sort. I determined the error to be caused by exceptions thrown while in the midst of a connection; I now wrap any data reader connection as follows: SqlDataReader dr = null; try { // SQLHelper - Microsoft Data access App. dr = SQLHelper.ExecuteReader (....); while (dr.Read ()) { _myStrings.Add (dr.GetString(0)); _myInt.Add (dr.GetInt32(1)); } dr.Close(); } catch { dr.Close (); throw; } In some cases, I will simply put a finally block that has the dr.Close() in it, but only if I don't care to re-throw the exception. Anyway, I recommend you ensure anywhere you open a connection, you close it. .NET does not close them when the object goes out of scope. I have find some reason for the problem.
The problem is caused by, when the IIS server's "executionTimeout" 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 executionTimeout 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 executionTimeout 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 ExecutionTimeout can be set in your applications Web.Config
configuration file. Doing this only affects the one application instead of all IIS applications. Furthermore, I would investigate the programmatic overriding of the setting. Using the .NET configuration namespace I would think (but have not tried!) that you should be able to override this setting on a per-page basis. This way, only pages that execute intensive database calls have this extended execution timeout. I have received word that extending the executionTimeout setting is common for sites that accept large uploads. There exist some Microsoft recommendations on this that you should Google.  |
|||||||||||||||||||||||
Other interesting topics