Connection Pooling nastiness

[Nick]

Who can prove that the following cannot happen:

1. client A gets an ADO.Net/SqlClient connection from the connection pool
2. client A fires a TSQL batch (select a,b,c from x) at Sql Server 2K5
3. Sql Server goes to work but the are some locked resources
4. ADO.Net loses patience and times out on Connection.CommandTimeout and
throws an exception to the client
5. client A catches the exception, "closes" the connection, thus returning
it to the pool
6. client B now gets handed the the same connection from the pool, for which
the pool fires an sp_reset_connection over that connection
8. the sp_reset_connection command gets queued and the supposedly clean
connection gets handed to client B
9. client B fires its TSQL batch (select d,e,f from z) at the connection and
awaits results

we now have the following situation on that one same connection:
-Sql Server is still busy with client A's TSQL batch
-after which it will execute the queued sp_reset
-after which it will get round to client B's TSQL batch

what if now the following occurs:

10. Sql Server finally finishes client A's TSQL batch and signals the
connection that there are results to be fetched.
11. ADO.Net receives that signal and consumes the results and passes them to
the awaiting client
12. The client, expecting results (d,e,f) instead receives (a,b,c)

Pretty ugly huh?

I'm not certain of my speculations on the exact proceedings of the first 11
steps, but we certainly see step 12.

Is it by design that this the default behaviour of ADO.Net connection
pooling? Or is it documented somewhere that a command.timeout exception
should be handled in some way to avoid this happening?

 

[Patrice]

What kind of application is this ? For ASP.NET application, this is caused
most often by using static data (that is they are shared by all users).

Don't know for details but I've never have seen someone reporting such a
behavior caused by how connections are handled. IMO your best bet is to
expose the raw facts and give some more details about your application
before looking at a lower level....

 

[Nick]

I was preparing a reply for you when I again carefully studied our logs of an
incident where we see a "beautiful" chain of request 1 having a
command.timeout, request 2 receiving request 1's result, and finally request
3 receiving request 2's result. Then I suddenly noticed that request 1 was on
a different webserver than request 2 and 3.
So basically that's blown a whole in my theory of blaming Connection
Pooling. (I'm not aware that Sql Server is aware of connection pooling at the
client side). And also in anything we're doing on the client-side, such as
with static data as you suggested.

Now the only suspect would suddenly seem to be Sql Server. Even more
unlikely...

Nick

 

[William Vaughn]

Ah, unless this is an ASP.NET application and the "clients" are instances in
the same application domain, it does not work like this.
The Connection Pool is created on a per-process basis. In a client/server
model, a pool unique to the connection string, process and transaction scope
is created on the client. No other client ever shares this pool--just
connections opened within the process that use the same ConnectionString.

hth

--
__________________________________________________________________________
William R. Vaughn
President and Founder Beta V Corporation
Author, Mentor, Dad, Grandpa
Microsoft MVP
(425) 556-9205 (Pacific time)
Hitchhiker’s Guide to Visual Studio and SQL Server (7th Edition)
____________________________________________________________________________________________

 

[WenYuan Wang [MSFT]]

Hello Nick,

It sounds like the issue is on SQL Server side. The first command has a
timeout exception. The second command received the first request's result.
But the request 1 and request 2 are on two different Web Servers. ADO.net
connection pool doesn't share connection with other machine.

Did you close DBReader/DBConnection after your first request failed on
Timeout? We met an issue that DBCommand returned the wrong result before.
The root cause is that we forget to close connection after executing
command. My idea is to ensure you have closed all DBReaders/DBConnections
carefully.

We also suggest you may post in SQL Server related newsgroup, because to
special SQL Server issue, the people in those groups will be more likely to
be able to help and familiar in the connections on SQL Server side.
All MSDN Managed Newsgroup is included in the following list.
http://msdn2.microsoft.com/en-us/subscriptions/aa974230.aspx

Hope this helps. If you have any more concern, please feel free to let me
know, We are glad to assist you.

Have a great day,
Best regards,

Wen Yuan
Microsoft Online Community Support
=====================================
This posting is provided "AS IS" with no warranties, and confers no rights.

 

[Bhavna S]

Hi Nick,

We are facing the same problem in our application. Did you find a solution to this one yet? If yes, please could you let me know your findings?

thanks,
Bhavna

 

[sloan]

If you change the subject line, it doesn't "stick to" the same thread.

This is an orphan post now, fyi.

No one knows what you're talking about since you disassociated the post with
the thread by changing the subject line.