SQL Server: Isolation level leaks across pooled connections

by

The connection pool calls sp_resetconnection before recycling a connection. Resetting the transaction isolation level is not in the list of things that sp_resetconnection does. That would explain why “serializable” leaks across pooled connections.

I guess you could start each query by making sure it’s at the right isolation level:

if not exists (
              select  * 
              from    sys.dm_exec_sessions 
              where   session_id = @@SPID 
                      and transaction_isolation_level = 2
              )
    set transaction isolation level read committed

Another option: connections with a different connection string do not share a connection pool. So if you use another connection string for the “serializable” queries, they won’t share a pool with the “read committed” queries. An easy way to alter the connection string is to use a different login. You could also add a random option like Persist Security Info=False;.

Finally, you could make sure every “serializable” query resets the isolation level before it returns. If a “serializable” query fails to complete, you could clear the connection pool to force the tainted connection out of the pool:

SqlConnection.ClearPool(yourSqlConnection);

This is potentially expensive, but failing queries are rare, so you should not have to call ClearPool() often.

More Related Contents:

  1. TSQL Try / Catch within Transaction or vice versa?
  2. ADO.NET calling T-SQL Stored Procedure causes a SqlTimeoutException
  3. Comma separated results in SQL
  4. T-SQL stored procedure that accepts multiple Id values
  5. Understanding PIVOT function in T-SQL
  6. How do I escape a percentage sign in T-SQL?
  7. Using RegEx in SQL Server
  8. Replace duplicate spaces with a single space in T-SQL
  9. How do you count the number of occurrences of a certain substring in a SQL varchar?
  10. How do I use the CONCAT function in SQL Server 2008 R2?
  11. How can sanitation that escapes single quotes be defeated by SQL injection in SQL Server?
  12. How do I exclude Weekend days in a SQL Server query?
  13. EXEC sp_executesql with multiple parameters
  14. Preserving ORDER BY in SELECT INTO
  15. Select / Insert version of an Upsert: is there a design pattern for high concurrency?
  16. Can you call a webservice from TSQL code?
  17. How to add custom attributes to SQL connection string?
  18. Multiple Column Pivot in T-SQL
  19. Convert decimal time to hours and minutes
  20. Joining multiple tables returns NULL value
  21. Which join syntax is better?
  22. Compute MD5 hash of a UTF8 string
  23. LPAD in SQL Server 2008
  24. SQL Query Where Column = ” returning Emoji characters 🎃 and 🍰
  25. How to check SQL Server Database compatibility after sp_dbcmptlevel is deprecated?
  26. How do I remove non-breaking spaces from a column in SQL server?
  27. How to set value to variable using ‘execute’ in t-sql?
  28. Update SQL with consecutive numbering
  29. Nested transactions in Sql Server
  30. Count number of records returned by group by

Leave a Comment