Sql-server – “When a connection is closed and returned to the pool, the isolation level from the last SET TRANSACTION ISOLATION LEVEL statement is retained”

connection-poolingisolation-levelsnapshot-isolationsql servertransaction

The MSDN online article "Snapshot Isolation in SQL Server" states:

  • "An isolation level has connection-wide scope, and once set for a connection with the SET TRANSACTION ISOLATION LEVEL statement, it remains in effect until the connection is closed or another isolation level is set. When a connection is closed and returned to the pool, the isolation level from the last SET TRANSACTION ISOLATION LEVEL statement is retained. Subsequent connections reusing a pooled connection use the isolation level that was in effect at the time the connection is pooled"

Isn't it self-contradictory paragraph ("until" vs. "retained")?

Then, if "the isolation level from the last SET TRANSACTION ISOLATION LEVEL statement is retained" after closing the connection and returning it to a pool, how it should be understood:

  • that the default isolation level will have arbitrary value (different connections in the pool will have different isolation levels, and its value will depend on the connection being re-opened)?
  • or all the default values on all connection in thу pool will be changed to last one? but again quite unknown before hand?

Best Answer

A connection from the pool will have the isolation level set by the last client to use that connection. Yes, it really is that scary.

The long and the short of it is that if you change the isolation level of a connection you must explicitly set it back to READ COMMITTED before closing. Better is to explicitly declare your required isolation level at the start of any batch, to ensure your code isn't impacted by somebody else being sloppy, and return it to the default at the end.

This behaviour, while baffling, is apparently by-design.

Related Question