Results 1 to 4 of 4
  1. #1
    Join Date
    Feb 2006
    Posts
    3

    Unanswered: 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/downloa...dAbortTest.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

  2. #2
    Join Date
    May 2004
    Location
    Dominican Republic
    Posts
    721
    Alex, it seems to me that you're using the default connection pool for your application, have you tried creating a new connection pool designed only for this application and try with that ? as I see it, it would get full as connection come and go (check also the connection pool's time out to release those connections). Also, tipically you will find SQL*Net message from client to be the event that most waited on web applications, I would consider this as *normal*.

  3. #3
    Join Date
    Feb 2006
    Posts
    3

    Thanks!

    Hi JMartinez,

    Thanks for your information. I will try to have my own connection pool.

    In my test case program, 20 New Threads is made to obtain a connection from the pool and interact with the DB. These threads are ABORT while they are querying the DB. It is supposed that the aborted threads will release the connections back to the connection pool, that the main thread could obtain the available connection from the pool.

    However, the real situation is that all connections will be not available anymore unless the application is restarted.

    If I use "pooling=false" in the connection string, there will be no such problem.

    If I don't Abort the threads, there will be just one or two "Request timeout" Exceptions, while the main thread can still obtain the connection from pool.

    Thanks for your help!

    Regards,
    Alex



    Quote Originally Posted by JMartinez
    Alex, it seems to me that you're using the default connection pool for your application, have you tried creating a new connection pool designed only for this application and try with that ? as I see it, it would get full as connection come and go (check also the connection pool's time out to release those connections). Also, tipically you will find SQL*Net message from client to be the event that most waited on web applications, I would consider this as *normal*.

  4. #4
    Join Date
    Feb 2006
    Posts
    3
    Hi all, I think I have found a reason for this 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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •