The client code that handles timeoutshttp://sqlblog.com/blogs/alexander_kuznetsov/archive/2013/01/10/the-client-code-that-handles-timeouts.aspxAfter timeouts we need to make sure that active transactions, if any, are rolled back. All timeout handling must be done on the client. This post provides the implementation and unit tests. Implementation The following class extends SqlCommand and rollsenCommunityServer 2.1 SP2 (Build: 61129.1)re: The client code that handles timeoutshttp://sqlblog.com/blogs/alexander_kuznetsov/archive/2013/01/10/the-client-code-that-handles-timeouts.aspx#47112Fri, 11 Jan 2013 11:15:54 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:47112gbn<p>Why not use SET XACT_ABORT ON so the client code doesn't need to run anything?</p>
<p>Example: code crashes but connections stays open in the connection pool.</p>
<p>See my answers on SO <a rel="nofollow" target="_new" href="http://stackoverflow.com/search?tab=votes&amp;q=user%3a27535%20%2bset%20%2bxact_abort">http://stackoverflow.com/search?tab=votes&amp;q=user%3a27535%20%2bset%20%2bxact_abort</a></p>
re: The client code that handles timeoutshttp://sqlblog.com/blogs/alexander_kuznetsov/archive/2013/01/10/the-client-code-that-handles-timeouts.aspx#47113Fri, 11 Jan 2013 13:58:44 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:47113dan holmes<p>This seems to be a behavior only when 'Connection Pooling' is on. &nbsp;If you add 'Pooling=False' to the connection string, then the moment the connection object goes out of scope or is closed both the connection and transaction are gone.</p>
<p>--</p>
<p>dan</p>
<p><a rel="nofollow" target="_new" href="http://dnhlmssql.blogspot.com/">http://dnhlmssql.blogspot.com/</a></p>
re: The client code that handles timeoutshttp://sqlblog.com/blogs/alexander_kuznetsov/archive/2013/01/10/the-client-code-that-handles-timeouts.aspx#47118Fri, 11 Jan 2013 18:58:55 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:47118Adam Machanic<p>gbn: XACT_ABORT will *not* abort the transaction in case of an Attention event (a.k.a. timeout). </p>
<p>dan: sure, we could disable connection pooling. But now you've taken a major, major performance and scalability hit. Is it worth it? No way. Use Alex's code (or roll your own) and keep pooling wherever possible.</p>
re: The client code that handles timeoutshttp://sqlblog.com/blogs/alexander_kuznetsov/archive/2013/01/10/the-client-code-that-handles-timeouts.aspx#47121Fri, 11 Jan 2013 21:01:01 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:47121Alexander Kuznetsov<p>@gbn: on one hand, Ken Henderson clearly stated in his blog &quot;There's no such thing as a query timeout&quot;: &quot;An attention tells the server to cancel the connection's currently executing query (if there is one) as soon as possible. &nbsp;An attention doesn't rollback open transactions&quot;. Of course, it was written long ago, on 20 Oct 2005.</p>
<p>On the other hand, I just ran a few tests, and XACT_ABORT ON consistently rolls back after a timeout. Does it always do so? Dan Guzman thinks so: &quot;Use Caution with Explicit Transactions in Stored Procedures&quot;. Because SQL Server is not open source, we cannot verify ourselves...</p>
re: The client code that handles timeoutshttp://sqlblog.com/blogs/alexander_kuznetsov/archive/2013/01/10/the-client-code-that-handles-timeouts.aspx#47122Fri, 11 Jan 2013 21:02:33 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:47122Alexander Kuznetsov<p>@Dan Holmes: in my experience not using connection pooling is usually more expensive than using it.</p>
re: The client code that handles timeoutshttp://sqlblog.com/blogs/alexander_kuznetsov/archive/2013/01/10/the-client-code-that-handles-timeouts.aspx#47134Sat, 12 Jan 2013 17:50:07 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:47134Adam Machanic<p>@alex and @gbn: I just ran some tests as well, and it seems this did change at some point -- XACT_ABORT does indeed always roll back the transaction when an Attention event occurs.</p>
<p>Here's another reference from Microsoft: <a rel="nofollow" target="_new" href="https://blogs.msdn.com/b/psssql/archive/2008/07/23/how-it-works-attention-attention-or-should-i-say-cancel-the-query-and-be-sure-to-process-your-results.aspx">https://blogs.msdn.com/b/psssql/archive/2008/07/23/how-it-works-attention-attention-or-should-i-say-cancel-the-query-and-be-sure-to-process-your-results.aspx</a></p>
<p>&quot;At the time of the attention the transaction is not rolled back unless transaction abort (XACT_ABORT) has been enabled.&quot;</p>
<p>--Adam</p>
re: The client code that handles timeoutshttp://sqlblog.com/blogs/alexander_kuznetsov/archive/2013/01/10/the-client-code-that-handles-timeouts.aspx#47154Mon, 14 Jan 2013 20:46:59 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:47154Alexander Kuznetsov<p>@gbn I ran more tests, and XACT_ABORT ON consistently rolls back on timeout. Thank you for your comment.</p>
<p>On one hand, when we handle timeouts on the client, we can implement it only once in one place, as I demonstrated in my code samples. This solves our problem right away.</p>
<p>On the other hand, we absolutely want to roll back ASAP, so it makes sense to add SET XACT_ABORT ON to all my reading procedures, and redeploy. (All my writes have SET XACT_ABORT ON already.) This is a better solution in the long run.</p>
<p>@Adam yes probably the behavior changed at some time.</p>
re: The client code that handles timeoutshttp://sqlblog.com/blogs/alexander_kuznetsov/archive/2013/01/10/the-client-code-that-handles-timeouts.aspx#47434Fri, 01 Feb 2013 06:07:49 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:47434handles<p>I just came onto your post and found it quite interesting. I am also associated with Door Handles, Door knobs, kitchen Door Handles, Door Handles suppliers, Door handles UK, Ironmongery Suppliers, Handles, knobs and love to enjoy the stuff on the same as its rarely found on internet. Thanks again for writing such a good post.</p>