ADO.NET, connection pooling and ASP.NET

[Rob Nicholson]

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]

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" <in******@community.nospam> wrote in message
news:uV**************@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.

[Milosz Skalecki]

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 Barker]

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" <in******@community.nospam> wrote in message
news:uV**************@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 Nicholson]

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

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

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

"Rob Nicholson" <in******@community.nospam> wrote in message
news:eV**************@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 Nicholson]

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

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" <in******@community.nospam> wrote in message
news:et*************@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 Ligthert [MVP]]

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 Nicholson]

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

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

> 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 Spencer]

> 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" <in******@community.nospam> wrote in message
news:OS**************@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 Nicholson]

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

> 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" <in******@community.nospam> wrote in message
news:uW**************@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 Nicholson]

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

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" <in******@community.nospam> wrote in message
news:uI**************@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 Nicholson]

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