ADO.NET, connection pooling and ASP.NET

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
Wed Aug 31 10:47:58 CDT 2005

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" <informed@community.nospam> wrote in message
news:uVzOVKkrFHA.1172@TK2MSFTNGP11.phx.gbl...
> 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.
>
>

MiloszSkalecki
Wed Aug 31 11:02:02 CDT 2005

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.
>
>
>

Bruce
Wed Aug 31 11:14:11 CDT 2005

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" <informed@community.nospam> wrote in message
news:uVzOVKkrFHA.1172@TK2MSFTNGP11.phx.gbl...
> 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
Wed Aug 31 12:09:27 CDT 2005

> Are you using a Shared connection object?

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

Cheers, Rob.

Rob
Wed Aug 31 12:12:16 CDT 2005

> 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.

Marina
Wed Aug 31 12:10:35 CDT 2005

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

"Rob Nicholson" <informed@community.nospam> wrote in message
news:eVI6a5krFHA.2880@TK2MSFTNGP12.phx.gbl...
>> Are you using a Shared connection object?
>
> Not that I'm aware of - how would you create a shared connection object?
>
> Cheers, Rob.
>
>

Rob
Wed Aug 31 12:31:27 CDT 2005

> 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
Wed Aug 31 12:45:02 CDT 2005

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" <informed@community.nospam> wrote in message
news:etH3tFlrFHA.248@TK2MSFTNGP14.phx.gbl...
>> 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.
>
>

Cor
Wed Aug 31 13:41:38 CDT 2005

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

Rob
Wed Aug 31 14:59:45 CDT 2005

> 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
Thu Sep 15 05:34:31 CDT 2005

> 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
Thu Sep 15 06:22:37 CDT 2005

> 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.

Kevin
Thu Sep 15 06:43:49 CDT 2005

> 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" <informed@community.nospam> wrote in message
news:OSsFnCeuFHA.3864@TK2MSFTNGP12.phx.gbl...
>> 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
Thu Sep 15 07:24:54 CDT 2005

> 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
Thu Sep 15 08:24:37 CDT 2005

> 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" <informed@community.nospam> wrote in message
news:uWQ9RAfuFHA.3660@tk2msftngp13.phx.gbl...
>> 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
Thu Sep 15 12:57:15 CDT 2005

> 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
Thu Sep 15 14:15:03 CDT 2005

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" <informed@community.nospam> wrote in message
news:uIH828huFHA.1560@TK2MSFTNGP09.phx.gbl...
>> 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
Sat Sep 17 10:49:21 CDT 2005

> 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.