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.

 

[Guest]

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.

 

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

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]

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]

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]

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.