SqlConnection.ClearPool as best practice on command timeout?

If a timeout occurs on a SqlCommand while it is in the middle of
executing a transaction contained withing a stored procedure
(transaction is started in the stored procedure with BEGIN TRANS, as
opposed to being started in the .Net application), database resources
can stay locked even after the connection is closed when connection
pooling is enabled.

However, if SqlConnection.ClearPool is called prior to closing the
connection, then the connection is discarded (not returned to the
pool) when it is closed. This results in the transaction being rolled
back, freeing the database resources.

So it would seem that a 'best practice' would be to always call
SqlConnection.ClearPool whenver a command timeout occurs.

I haven't seen any discussion of this anywhere. Was wondering what
other people might think about doing an unconditional ClearPool under
these circumstances.

(It would be nice if there was some way to detect if there was an open
transaction on a connection, in the case where the transaction was
started from within a stored procedure, but don't believe this is the
case. That way, the ClearPool could be called only when there was
actually an open transaction that needed to be rolled back.)

Ira
Fri Mar 07 18:26:31 CST 2008

..or, is there a way to discard just the connection that timed out,
without clearing entire connection pool?

Clearing the whole pool does seem a bit drastic, but not sure how else
to kill open transactions on the timed-out connection.

Again, this would be for a transaction that was started inside a
stored procedure, rather than in ADO.Net itself.

William
Fri Mar 07 23:40:00 CST 2008

Let's make sure that we're talking about an ASP or XML service application.
A middle-tier or client/server application does not need the Connection
pooling mechanism.
If it's an ASP or Web service, then yes, this is an unfortunate side-effect
of the current Connection pooling mechanism. I have proposed a "real" close
it now method for some time. The side-effect of clearing the pool or all
pools is a transitory loss of performance that can be minimized by setting
the min pool size.

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)
____________________________________________________________________________________________
"Ira Gladnick" <IraGladnick@yahoo.com> wrote in message
news:5face299-fbf5-4fe8-9124-976f121679e8@e6g2000prf.googlegroups.com...
> If a timeout occurs on a SqlCommand while it is in the middle of
> executing a transaction contained withing a stored procedure
> (transaction is started in the stored procedure with BEGIN TRANS, as
> opposed to being started in the .Net application), database resources
> can stay locked even after the connection is closed when connection
> pooling is enabled.
>
> However, if SqlConnection.ClearPool is called prior to closing the
> connection, then the connection is discarded (not returned to the
> pool) when it is closed. This results in the transaction being rolled
> back, freeing the database resources.
>
> So it would seem that a 'best practice' would be to always call
> SqlConnection.ClearPool whenver a command timeout occurs.
>
> I haven't seen any discussion of this anywhere. Was wondering what
> other people might think about doing an unconditional ClearPool under
> these circumstances.
>
> (It would be nice if there was some way to detect if there was an open
> transaction on a connection, in the case where the transaction was
> started from within a stored procedure, but don't believe this is the
> case. That way, the ClearPool could be called only when there was
> actually an open transaction that needed to be rolled back.)

Ira
Sat Mar 08 04:03:54 CST 2008

Mr. Vaughn,

Always great to get your feedback--you are an internet treasure.

I am dealing with both ASP.Net and Windows client/server applications.

I was wondering if I get you to elaborate a bit on why pooling might
not be needed for client/server. Wouldn't performance be negatively
impacted in a client/server app if pooling was disabled? Also not
clear about middle tier--isn't ASP.net effectively middle tier?

It's quite amazing that there isn't a "real close" mechanism, or at
least the equivalent of being able to do a SET XACT_ABORT from within
ADO.Net.

A workaround that occured to me this evening was possibly to start an
ADO.Net transaction AFTER a timeout occurs on a connection, and then
rollback the transaction. Will try that at work first thing on
Monday..

Ira
Sat Mar 08 13:49:53 CST 2008

On Mar 8, 2:03 am, Ira Gladnick <IraGladn...@yahoo.com> wrote:
> I was wondering if I get you to elaborate a bit on why pooling might
> not be needed for client/server.

Well, I see now that Bill Vaughn has discussed this extensively <a
target="_blank" href="http://betav.com/blog/billva/2007/05/
managing_and_monitoring_net_co.html">elsewhere</a>,,,

Ira
Sat Mar 08 13:53:28 CST 2008

On Mar 8, 2:03 am, Ira Gladnick <IraGladn...@yahoo.com> wrote:
> I was wondering if I get you to elaborate a bit on why pooling might
> not be needed for client/server.

I see now where Bill Vaughn has addressed this aspect extensively at
http://betav.com/blog/billva/2007/05/managing_and_monitoring_net_co.html

Anybody with questions regarding just about any apect of pooling
should definitely follow the above link.

Ira
Sat Mar 08 13:55:38 CST 2008

On Mar 8, 2:03 am, Ira Gladnick <IraGladn...@yahoo.com> wrote:

> I was wondering if I get you to elaborate a bit on why pooling might
> not be needed for client/server.

I see now where Bill Vaughn has addressed this aspect extensively at
http://betav.com/blog/billva/2007/05/managing_and_monitoring_net_co.html

Anybody with questions regarding just about anything about pooling
should definitely follow the above link.

William
Mon Mar 10 20:58:33 CDT 2008

If you haven't already uncovered this, in a client/server application, a
more viable strategy is to leave the connection open for the lifetime of the
application. Since the pool is not shared by any other processes, there is
very little utility in the Open, query, close strategy. IMHO this can
actually hurt performance when compared to a static open connection (or
two). The open connection can be really closed as necessary, can maintain
state like #tempdb cursors, server-side cursors or other manageable state. I
discuss this more completely in my book (chapter 9--it's devoted to
connecting).

So, in ASP you have so many application instances starting, opening
connections, running queries and closing connections (dozens at a time) it
makes sense to have a common pool of reusable connections. I brought up this
very issue in the initial design discussions decades ago. I got the
impression that MS thought it was too expensive to add the method to
"really" close the connection. They felt that the flush the pool was close
enough. It does the job with a canon when it needs to be done with a pin.
Consider that the mechanism is designed to be "generic" and implementable by
all of the .NET data access data providers.

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)
____________________________________________________________________________________________
"Ira Gladnick" <IraGladnick@yahoo.com> wrote in message
news:15ec4d4b-17f9-4932-bdd1-7b60568c857b@s19g2000prg.googlegroups.com...
> Mr. Vaughn,
>
> Always great to get your feedback--you are an internet treasure.
>
> I am dealing with both ASP.Net and Windows client/server applications.
>
> I was wondering if I get you to elaborate a bit on why pooling might
> not be needed for client/server. Wouldn't performance be negatively
> impacted in a client/server app if pooling was disabled? Also not
> clear about middle tier--isn't ASP.net effectively middle tier?
>
> It's quite amazing that there isn't a "real close" mechanism, or at
> least the equivalent of being able to do a SET XACT_ABORT from within
> ADO.Net.
>
> A workaround that occured to me this evening was possibly to start an
> ADO.Net transaction AFTER a timeout occurs on a connection, and then
> rollback the transaction. Will try that at work first thing on
> Monday..