Jimmy May's Blog

SQL Server Performance, Best Practices, & Productivity

sp_reset_connection Does NOT Reset TRANSACTION ISOLATION LEVEL: Unexpected Behavior By Design

sp_reset_connection Does NOT Reset TRANSACTION ISOLATION LEVEL: Unexpected Behavior By Design

  • Comments 6

I've long been an advocate of the best practice of explicitly setting the transaction isolation level in my scripts.

At my old day job as an enterprise architect I mandated that along with SET NOCOUNT ON, the preamble for all SProcs explicitly must declare the appropriate transaction isolation level.

In addition, all ad hoc queries by DBAs had to be prefaced with a read uncommitted declaration.  To save time & promote compliance, I distributed a SQL template for quick access (set_tx_iso_level.tql) which contains two lines:

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
-- DBCC UserOptions

<ADD> We'll save the dirty reads & data integrity debate for another day, eh? </ADD>

Many customer applications exercise connection pooling.  Doing so enhances performance & preserves system resources.  For more information, see this MSDN article:

Using Connection Pooling with SQL Server

The system SProc sp_reset_connection facilitates the re-use of connections from the connection pool.  The SProc is so ubiquitous that I typically filter it from SQL Trace output.

My friend, buddy, pal PFE Gennady "Dr. G" Kostinsky brought to my attention that sp_reset_connection does NOT reset the transaction isolation level to the server default from the previous connection's setting.

To my surprise, this unexpected behavior is by design.

O the humanity!  See for yourself on Connect:

http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=243527

This has profound implications.  Imagine the havoc wrought to applications by everything from intended declarations to developer abuse to vendor apps—the list goes on-&-on...

In light of sp_reset_connection's behavior, the explicit declaration of the isolation level is not merely a best practice but is a mandatory practice.  Engineering discipline demands it.

Leave a Comment
  • Please add 5 and 6 and type the answer here:
  • Post
  • PingBack from http://www.anith.com/?p=4685

  • Jimmy,

    Thanks for sharing this one.

    The more i work with the customers the more i witness issues associated with isolation level. Such guidance as you provide here is very helpful both for me as a consultant and to the customers that rely on the online community.

  • You're absolutely right, Alik.  Isolation level is an important component of application architecture.

    If one is to indulge in the best practice cited above of explicitly declaring it in code, one must do so from the ground up--from the very first SProc & code snippet to each-&-every one thereafter.  

    A related issue is my frustration many times by vendors not allowing us to control isolation level in their tools.  We need the ability to control isolation level & not be at the mercy either of vendor decisions--vendors who don't know the characteristics of my app--or to chance (that is, the setting from the previous connection as I've documented above).

  • This is awesome and should be checked in the BPA.  I'll try to get it included in the PTO course.  Thanks for pointing that out.

  • @Curtis: Thanks Curtis, let me know what you find out.

    In addition to potential/inevitable challenges with sp_reset_connection & connection pooling, I'm amazed at the myriad vendor tools which do not allow us to choose the isolation level we want or need.  Instead we're forced to accept a default which isn't always optimal.

  • Boy, this one really strikes me as potentially devastating in certain cases.  Not a good "by design" in my mind.  But then again neither are numerous things such as 63 sectors, allocation order scanning, etc.  :-)

Page 1 of 1 (6 items)