ODP .Net Connection Pool Problem on Web Application

Discussion in 'ASP .Net' started by mingki@gmail.com, Feb 13, 2006.

  1. Guest

    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
    , Feb 13, 2006
    #1
    1. Advertising

  2. Guest

    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
    , Feb 15, 2006
    #2
    1. Advertising

  3. dguisinger

    Joined:
    Jul 6, 2006
    Messages:
    1
    Alex, did you find a better solution? I am experiencing the same issue with MySQL .NET Connector, and have been struggling with it for a few months locking up when 2 or more clients are accessing our web service at the same time. Sometime on very small queries, sometimes on very large ones. Very random in appearances, but matching what you describe. I'm concerned about DoS attack issues with opening that threat up for longer time-outs.

    Did you find a better solution? Is that original solution working well for you to this date?

    Thanks,
    Dan
    dguisinger, Jul 6, 2006
    #3
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. =?Utf-8?B?Sm9vc3QgRXZlcnRzZQ==?=

    ODP.NET : VB.NET and PLSQLAssociativeArray, hanging webserver

    =?Utf-8?B?Sm9vc3QgRXZlcnRzZQ==?=, Nov 5, 2004, in forum: ASP .Net
    Replies:
    1
    Views:
    2,279
    John Saunders
    Nov 6, 2004
  2. SK

    odp.net

    SK, Feb 14, 2005, in forum: ASP .Net
    Replies:
    2
    Views:
    679
    Scott Allen
    Feb 14, 2005
  3. venky

    Odp.net

    venky, Feb 15, 2005, in forum: ASP .Net
    Replies:
    0
    Views:
    887
    venky
    Feb 15, 2005
  4. Nathan
    Replies:
    0
    Views:
    1,606
    Nathan
    Jan 5, 2006
  5. Big George

    Forcing odp.net connection

    Big George, Aug 30, 2012, in forum: ASP .Net
    Replies:
    1
    Views:
    773
    Arne Vajhøj
    Aug 30, 2012
Loading...

Share This Page