ADO.NET, connection pooling and ASP.NET

Discussion in 'ASP .Net' started by Rob Nicholson, Aug 31, 2005.

  1. We're getting an occasional occurrence of the following error when two users
    try and open the same record in our ASP.NET app:

    "There is already an open DataReader associated with this Connection which
    must be closed first."

    As suggested, I was closing the connection in the Finally part of the outer
    Try but I wasn't closing the data reader as well so I assume that if the
    following happens, the above error could occur

    Try
    Open connection
    Open data reader
    Some operation causes the exception to fire
    Catch ex As Exception
    .. handle exception
    Finally
    Close the connection [data reader is still open here]
    End Try

    I've modified this to close the data reader as well in the Finally section
    but will have to wait until the next release to see if this resolves it.

    However, it raised a question about connection pooling, hence the post:

    Is connection pooling done in ASP.NET at a session, application or other
    level? When we look in SQL, we see various connections from the web server
    called ".Net SqlClient data provider". I understand the concept of
    connection pooling but what happens in the following situation:

    1. User A opens web page causing an open connection, data read and close
    connection
    2. Connection goes into the pool
    3. User B opens same web page causing identical open connection, data read
    and close connection

    Does (3) re-use the connection opened by User A that's been put in the pool?
    Is the pooling by the aspnet_wp.exe process or against the user's session?

    Thanks, Rob.
    Rob Nicholson, Aug 31, 2005
    #1
    1. Advertising

  2. Rob Nicholson

    Marina Guest

    Are you using a Shared connection object?

    If so, this is not something you should be doing in an ASP.NET application.
    It means that all users are using the same connection object. Since you are
    not synchronizing access to it, there are times when multiple users will end
    up trying to use it - and then you get an error. If you were to synchronize
    access to the connection, it would just mean horrible performance, as users
    are always in line waiting for the connection object to become available.


    "Rob Nicholson" <> wrote in message
    news:...
    > We're getting an occasional occurrence of the following error when two
    > users
    > try and open the same record in our ASP.NET app:
    >
    > "There is already an open DataReader associated with this Connection which
    > must be closed first."
    >
    > As suggested, I was closing the connection in the Finally part of the
    > outer
    > Try but I wasn't closing the data reader as well so I assume that if the
    > following happens, the above error could occur
    >
    > Try
    > Open connection
    > Open data reader
    > Some operation causes the exception to fire
    > Catch ex As Exception
    > .. handle exception
    > Finally
    > Close the connection [data reader is still open here]
    > End Try
    >
    > I've modified this to close the data reader as well in the Finally section
    > but will have to wait until the next release to see if this resolves it.
    >
    > However, it raised a question about connection pooling, hence the post:
    >
    > Is connection pooling done in ASP.NET at a session, application or other
    > level? When we look in SQL, we see various connections from the web server
    > called ".Net SqlClient data provider". I understand the concept of
    > connection pooling but what happens in the following situation:
    >
    > 1. User A opens web page causing an open connection, data read and close
    > connection
    > 2. Connection goes into the pool
    > 3. User B opens same web page causing identical open connection, data read
    > and close connection
    >
    > Does (3) re-use the connection opened by User A that's been put in the
    > pool?
    > Is the pooling by the aspnet_wp.exe process or against the user's session?
    >
    > Thanks, Rob.
    >
    >
    Marina, Aug 31, 2005
    #2
    1. Advertising

  3. Elo,

    Connection pool cannot return the same connection object to many clients. I
    think you are leaving reader opened. You have to close reader/connection
    after populating the data.

    --
    Milosz Skalecki
    MCP, MCAD


    "Rob Nicholson" wrote:

    > We're getting an occasional occurrence of the following error when two users
    > try and open the same record in our ASP.NET app:
    >
    > "There is already an open DataReader associated with this Connection which
    > must be closed first."
    >
    > As suggested, I was closing the connection in the Finally part of the outer
    > Try but I wasn't closing the data reader as well so I assume that if the
    > following happens, the above error could occur
    >
    > Try
    > Open connection
    > Open data reader
    > Some operation causes the exception to fire
    > Catch ex As Exception
    > .. handle exception
    > Finally
    > Close the connection [data reader is still open here]
    > End Try
    >
    > I've modified this to close the data reader as well in the Finally section
    > but will have to wait until the next release to see if this resolves it.
    >
    > However, it raised a question about connection pooling, hence the post:
    >
    > Is connection pooling done in ASP.NET at a session, application or other
    > level? When we look in SQL, we see various connections from the web server
    > called ".Net SqlClient data provider". I understand the concept of
    > connection pooling but what happens in the following situation:
    >
    > 1. User A opens web page causing an open connection, data read and close
    > connection
    > 2. Connection goes into the pool
    > 3. User B opens same web page causing identical open connection, data read
    > and close connection
    >
    > Does (3) re-use the connection opened by User A that's been put in the pool?
    > Is the pooling by the aspnet_wp.exe process or against the user's session?
    >
    > Thanks, Rob.
    >
    >
    >
    =?Utf-8?B?TWlsb3N6IFNrYWxlY2tp?=, Aug 31, 2005
    #3
  4. Rob Nicholson

    Bruce Barker Guest

    connection pooling is done at the AppDomain level. for asp.net this means at
    the application (all asp.net threads for the same vdir).

    connections are pooled by connection string (ie there is a pool per
    connection string). when a connection is requested, if one is found in the
    pool its reused, if not a connection is created. when a connection is
    closed, its returned to the pool. a timer is started, and if its not reused
    in the specified time limit is actually closed.

    the error happens when a connection is returned to the pool (the connection
    is closed), but a datareader has not read all results sets (this happens
    with the reader is closed), so the connection is still in use (to
    sqlserver). the real issuse is there is pending results sets on the
    connection, that will not be detected until a new request is sent to
    sqlserver over the connection. asp.net could have sent a cancel request
    first, but this would require an extra network round trip.

    -- bruce (sqlwork.com)


    "Rob Nicholson" <> wrote in message
    news:...
    > We're getting an occasional occurrence of the following error when two
    > users
    > try and open the same record in our ASP.NET app:
    >
    > "There is already an open DataReader associated with this Connection which
    > must be closed first."
    >
    > As suggested, I was closing the connection in the Finally part of the
    > outer
    > Try but I wasn't closing the data reader as well so I assume that if the
    > following happens, the above error could occur
    >
    > Try
    > Open connection
    > Open data reader
    > Some operation causes the exception to fire
    > Catch ex As Exception
    > .. handle exception
    > Finally
    > Close the connection [data reader is still open here]
    > End Try
    >
    > I've modified this to close the data reader as well in the Finally section
    > but will have to wait until the next release to see if this resolves it.
    >
    > However, it raised a question about connection pooling, hence the post:
    >
    > Is connection pooling done in ASP.NET at a session, application or other
    > level? When we look in SQL, we see various connections from the web server
    > called ".Net SqlClient data provider". I understand the concept of
    > connection pooling but what happens in the following situation:
    >
    > 1. User A opens web page causing an open connection, data read and close
    > connection
    > 2. Connection goes into the pool
    > 3. User B opens same web page causing identical open connection, data read
    > and close connection
    >
    > Does (3) re-use the connection opened by User A that's been put in the
    > pool?
    > Is the pooling by the aspnet_wp.exe process or against the user's session?
    >
    > Thanks, Rob.
    >
    >
    Bruce Barker, Aug 31, 2005
    #4
  5. > Are you using a Shared connection object?

    Not that I'm aware of - how would you create a shared connection object?

    Cheers, Rob.
    Rob Nicholson, Aug 31, 2005
    #5
  6. Rob Nicholson

    Marina Guest

    You would declare it as Shared. Same way you would create any other Shared
    variable.

    "Rob Nicholson" <> wrote in message
    news:...
    >> Are you using a Shared connection object?

    >
    > Not that I'm aware of - how would you create a shared connection object?
    >
    > Cheers, Rob.
    >
    >
    Marina, Aug 31, 2005
    #6
  7. > connection pooling is done at the AppDomain level. for asp.net this means
    at
    > the application (all asp.net threads for the same vdir).


    Thanks - that clears up one question at least and explains how the errors
    we're getting could occur, i.e. one user within the same app leaves a data
    reader open but another user could try and use the same connection.

    > the error happens when a connection is returned to the pool (the

    connection
    > is closed), but a datareader has not read all results sets (this happens


    And yes, that situation could potentially occur. Fingers crossed that we're
    resolved it in the next beta release.

    Cheers, Rob.
    Rob Nicholson, Aug 31, 2005
    #7
  8. > You would declare it as Shared. Same way you would create any other Shared
    > variable.


    Ahh I see, well sort of. The connection object itself is effectively stored
    in the session cache so it's user/session specific and not shared between
    users. Kind of like this:

    Program startup:

    Session.Add("Connection", New SqlConnection(connstring))

    Database access:

    Dim cn As SqlConnection = Session.Item("Connection")
    cn.Open()
    ...data reader stuff...
    cn.Close()

    I assume that this is okay, i.e. the SqlConnection object is kept alive
    whilst the session is alive but the cn.Close() bit above is all that's
    needed to return the connection to the pool.

    Or do we need to destroy and re-create the SqlConnection object as well each
    time?

    Cheers, Rob.
    Rob Nicholson, Aug 31, 2005
    #8
  9. Rob Nicholson

    Marina Guest

    What you have there is fine, since each user has their own session, and thus
    their own connection. Users should not be getting each other's connections
    in this scenario. I would only expect you to get those types of errors if
    User A does something with the connection, but the reader is not closed.
    User A then does something else, and the error occurrs because the reader is
    still using the connection. I would not expect User B to have any effect on
    this scenario.

    My personal preference would be to not keep the actual connection in
    session. It would be to actually create the object every time, use it, and
    close it. You don't want to keep things in Session unless you absolutely
    need to. And if you ever change your session state from InProc to something
    else, you will get errors since SqlConnection is not serializable.

    Closing the connection in the Finally should be all you need to make sure
    the connection is closed. So that the next time the user does something with
    the connection, you don't get that message.

    "Rob Nicholson" <> wrote in message
    news:...
    >> You would declare it as Shared. Same way you would create any other
    >> Shared
    >> variable.

    >
    > Ahh I see, well sort of. The connection object itself is effectively
    > stored
    > in the session cache so it's user/session specific and not shared between
    > users. Kind of like this:
    >
    > Program startup:
    >
    > Session.Add("Connection", New SqlConnection(connstring))
    >
    > Database access:
    >
    > Dim cn As SqlConnection = Session.Item("Connection")
    > cn.Open()
    > ...data reader stuff...
    > cn.Close()
    >
    > I assume that this is okay, i.e. the SqlConnection object is kept alive
    > whilst the session is alive but the cn.Close() bit above is all that's
    > needed to return the connection to the pool.
    >
    > Or do we need to destroy and re-create the SqlConnection object as well
    > each
    > time?
    >
    > Cheers, Rob.
    >
    >
    Marina, Aug 31, 2005
    #9
  10. Rob,

    Keep in mind that you don't know if the user does not just close his browser
    and your sessions keep alive until the session.aborts and than there is
    nothing closed.

    I just keep it by opening and clossing the connections in the right time
    inside the handling of the page.

    Just my thought,

    Cor
    Cor Ligthert [MVP], Aug 31, 2005
    #10
  11. > I just keep it by opening and clossing the connections in the right time
    > inside the handling of the page.


    Thanks for the comment. We do something similar. We use object mapping
    whereby on the page load, the connection is opened, data read and closed in
    the Page_Load event.

    Cheers, Rob.
    Rob Nicholson, Aug 31, 2005
    #11
  12. > If so, this is not something you should be doing in an ASP.NET
    application.
    > It means that all users are using the same connection object. Since you

    are
    > not synchronizing access to it, there are times when multiple users will

    end

    Later...

    Ahh, I understand what you mean here - as all users on a single web server
    are running under a single task (e.g. aspnet_wp.exe), then any shared
    (global objects) are shared between users.

    Cheers, Rob.
    Rob Nicholson, Sep 15, 2005
    #12
  13. > the error happens when a connection is returned to the pool (the
    connection
    > is closed), but a datareader has not read all results sets (this happens


    Hmm, this is still happening very occasionally with our application. I
    thought we'd found all the situations whereby a data reader might be left
    open on an error condition but maybe not.

    Cheers, Rob.
    Rob Nicholson, Sep 15, 2005
    #13
  14. > Ahh, I understand what you mean here - as all users on a single web server
    > are running under a single task (e.g. aspnet_wp.exe), then any shared
    > (global objects) are shared between users.


    One bit of clarification for you: "Global" and "Shared" mean 2 different
    things. An object which is globally accessible may or may not be
    thread-safe. A static (Shared) object is certainly NOT thread-safe.

    --
    HTH,

    Kevin Spencer
    Microsoft MVP
    ..Net Developer
    Neither a follower nor a lender be.

    "Rob Nicholson" <> wrote in message
    news:...
    >> If so, this is not something you should be doing in an ASP.NET

    > application.
    >> It means that all users are using the same connection object. Since you

    > are
    >> not synchronizing access to it, there are times when multiple users will

    > end
    >
    > Later...
    >
    > Ahh, I understand what you mean here - as all users on a single web server
    > are running under a single task (e.g. aspnet_wp.exe), then any shared
    > (global objects) are shared between users.
    >
    > Cheers, Rob.
    >
    >
    Kevin Spencer, Sep 15, 2005
    #14
  15. > One bit of clarification for you: "Global" and "Shared" mean 2 different
    > things. An object which is globally accessible may or may not be
    > thread-safe. A static (Shared) object is certainly NOT thread-safe.


    Does the ASPNET_WP process run multiple threads per session?

    Cheers, Rob.
    Rob Nicholson, Sep 15, 2005
    #15
  16. > Does the ASPNET_WP process run multiple threads per session?

    Yes. Each Request is handled by a new Thread. This is because HTTP is
    stateless. When a Page is finished processing, the Thread it is created on
    is returned to the ASP.Net worker process ThreadPool.

    In addition, any Thread can spawn multiple Threads.

    --
    HTH,

    Kevin Spencer
    Microsoft MVP
    ..Net Developer
    Neither a follower nor a lender be.

    "Rob Nicholson" <> wrote in message
    news:...
    >> One bit of clarification for you: "Global" and "Shared" mean 2 different
    >> things. An object which is globally accessible may or may not be
    >> thread-safe. A static (Shared) object is certainly NOT thread-safe.

    >
    > Does the ASPNET_WP process run multiple threads per session?
    >
    > Cheers, Rob.
    >
    >
    Kevin Spencer, Sep 15, 2005
    #16
  17. > Yes. Each Request is handled by a new Thread. This is because HTTP is
    > stateless. When a Page is finished processing, the Thread it is created on
    > is returned to the ASP.Net worker process ThreadPool.


    Hmm, anyone ever heard of a connection going back into the pool before it
    should?

    Cheers, Rob.
    Rob Nicholson, Sep 15, 2005
    #17
  18. Not really. The Connection is returned to the pool only when the connection
    is closed one way or another. The Dispose closes it and Dispose is called
    (eventually) by the GC so... but that's pretty unlikely.

    --
    ____________________________________
    William (Bill) Vaughn
    Author, Mentor, Consultant
    Microsoft MVP
    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.
    __________________________________

    "Rob Nicholson" <> wrote in message
    news:...
    >> Yes. Each Request is handled by a new Thread. This is because HTTP is
    >> stateless. When a Page is finished processing, the Thread it is created
    >> on
    >> is returned to the ASP.Net worker process ThreadPool.

    >
    > Hmm, anyone ever heard of a connection going back into the pool before it
    > should?
    >
    > Cheers, Rob.
    >
    >
    William \(Bill\) Vaughn, Sep 15, 2005
    #18
  19. > Not really. The Connection is returned to the pool only when the
    connection
    > is closed one way or another. The Dispose closes it and Dispose is called
    > (eventually) by the GC so... but that's pretty unlikely.


    That's what I thought so I'm a bit flummoxed as to how this situtation is
    occuring... We have our own OR mapping system through which we load data
    into objects. Therefore, there is only one place in the code where it opens
    the reader and it's closing it correctly in normal operation and in error
    operation.

    Cheers, Rob.
    Rob Nicholson, Sep 17, 2005
    #19
    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. nita
    Replies:
    1
    Views:
    856
    Saravana
    Nov 20, 2004
  2. ronaldlee

    Transfer ADO Code to ADO.NET

    ronaldlee, Dec 17, 2004, in forum: ASP .Net
    Replies:
    1
    Views:
    445
    Kevin Spencer
    Dec 17, 2004
  3. Replies:
    0
    Views:
    1,300
  4. RyoSaeba
    Replies:
    2
    Views:
    3,461
    RyoSaeba
    Oct 6, 2006
  5. Replies:
    2
    Views:
    624
Loading...

Share This Page