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

  • Article

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:

https://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.

Comments

  • Anonymous
    February 02, 2009
    PingBack from http://www.anith.com/?p=4685

  • Anonymous
    February 02, 2009
    The comment has been removed

  • Anonymous
    February 04, 2009
    The comment has been removed

  • Anonymous
    February 06, 2009
    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.

  • Anonymous
    February 22, 2009
    @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.

  • Anonymous
    February 24, 2009
    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.  :-)

  • Anonymous
    September 03, 2014
    SQL Server 2014 now resets the isolation level of pooled connections. See stackoverflow.com/.../sql-server-isolation-level-leaks-across-pooled-connections.