"When a connection is closed and returned to the pool, the isolation level from the last SET TRANSACTION ISOLATION LEVEL statement is retained"?

Question

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?

Answer 1

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.

Answer 2

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

Not to me, but I can see there are other ways to read it. If you want the documentation updated to make it clearer, the proper place to request this is on Microsoft Connect. The people here do not maintain Microsoft documentation.

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)?

If you are reusing a pooled connection, then yes, the "pre-existing" isolation level is that which was in effect when the pooled connection was last closed. Best practice is to explicitly set the isolation level needed when connecting.

or all the default values on all connection in the pool will be changed to last one? but again quite unknown before hand?

No.

Answer 3

SQL Server 2014 now resets the isolation level of pooled connections for most TDS protocol versions. Previous SQL Server versions did not reset the isolation level.