The client code that handles timeouts

re: The client code that handles timeouts

gbn — Fri, 11 Jan 2013 11:15:54 GMT

Why not use SET XACT_ABORT ON so the client code doesn't need to run anything?

Example: code crashes but connections stays open in the connection pool.

See my answers on SO http://stackoverflow.com/search?tab=votes&q=user%3a27535%20%2bset%20%2bxact_abort

re: The client code that handles timeouts

dan holmes — Fri, 11 Jan 2013 13:58:44 GMT

This seems to be a behavior only when 'Connection Pooling' is on. 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.

dan

http://dnhlmssql.blogspot.com/

re: The client code that handles timeouts

Adam Machanic — Fri, 11 Jan 2013 18:58:55 GMT

gbn: XACT_ABORT will *not* abort the transaction in case of an Attention event (a.k.a. timeout).

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.

re: The client code that handles timeouts

Alexander Kuznetsov — Fri, 11 Jan 2013 21:01:01 GMT

@gbn: on one hand, Ken Henderson clearly stated in his blog "There's no such thing as a query timeout": "An attention tells the server to cancel the connection's currently executing query (if there is one) as soon as possible. An attention doesn't rollback open transactions". Of course, it was written long ago, on 20 Oct 2005.

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: "Use Caution with Explicit Transactions in Stored Procedures". Because SQL Server is not open source, we cannot verify ourselves...

re: The client code that handles timeouts

Alexander Kuznetsov — Fri, 11 Jan 2013 21:02:33 GMT

@Dan Holmes: in my experience not using connection pooling is usually more expensive than using it.

re: The client code that handles timeouts

Adam Machanic — Sat, 12 Jan 2013 17:50:07 GMT

@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.

Here's another reference from Microsoft: 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

"At the time of the attention the transaction is not rolled back unless transaction abort (XACT_ABORT) has been enabled."

--Adam

re: The client code that handles timeouts

Alexander Kuznetsov — Mon, 14 Jan 2013 20:46:59 GMT

@gbn I ran more tests, and XACT_ABORT ON consistently rolls back on timeout. Thank you for your comment.

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.

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.

@Adam yes probably the behavior changed at some time.

re: The client code that handles timeouts

handles — Fri, 01 Feb 2013 06:07:49 GMT

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.