Re: Connection Pooling nastiness
- From: Nick <SaintNick@xxxxxxxxxxxxx>
- Date: Mon, 11 Feb 2008 08:16:04 -0800
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
"Patrice" wrote:
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....
--
Patrice
"Nick" <SaintNick@xxxxxxxxxxxxx> a écrit dans le message de news:
B732463D-F5F9-4325-9EBA-4EA402246090@xxxxxxxxxxxxxxxx
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?
- Follow-Ups:
- Re: Connection Pooling nastiness
- From: WenYuan Wang [MSFT]
- Re: Connection Pooling nastiness
- References:
- Re: Connection Pooling nastiness
- From: Patrice
- Re: Connection Pooling nastiness
- Prev by Date: Map datatable A to datatable B
- Next by Date: Re: Connection Pooling nastiness
- Previous by thread: Re: Connection Pooling nastiness
- Next by thread: Re: Connection Pooling nastiness
- Index(es):
Relevant Pages
|
