Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?
share|improve this question

2 Answers 2

up vote 10 down vote accepted

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.

share|improve this answer
    
Never seen those "must explicitly" and "Better is to explicitly" in MSDN articles, books, blogs, docs, etc. –  Fulproof Dec 18 '13 at 2:19

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.