sp_reset_connection Does NOT Reset TRANSACTION ISOLATION LEVEL: Unexpected Behavior By Design
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:
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.