C# – How does SqlConnection manage IsolationLevel

ado.netcnetsql servertransactions

This MSDN article states that:

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.

The SqlConnection class has no member that may hold the isolation level. So how does a connection know what isolation level to run in???

The reason I'm asking this is because of the following scenario:

  1. I opened a transaction using
    TransactionScope in Serializable
    mode, say "T1".
  2. Opened a connection for T1.
  3. T1 is finished/disposed, connection
    goes back to connection pool.
  4. Called another query on same
    connection (after getting it from
    connection pool) and this query runs
    in serializable mode!!!

Problem:

  1. How does the pooled connection still
    know what isolation level was
    associated to it???
  2. How to revert it back to some other
    transaction level???

Resolution:
The reason why pooled connections are returning the serializable isolation level is because of the following reason:

  1. You have one connection pool (let's say CP1)
  2. CP1 may have 50 connections.
  3. You pick one connection C1 from CP1 and execute it with Serializable. This connection has its isolation level set now.
    Whatever you do, this will not be reset (unless this connection is
    used to execute a code in a different isolation level).
  4. After executing the query C1(Serializable) goes back to CP1.
  5. If steps 1-4 are executed again then the connection used may be some other connection than C1, let's say C2 or C3. So, that will also
    have its isolation level set to Serializable.
  6. So, slowly, Serialzable is set to multiple connections in CP1.
  7. When you execute a query where no explicit isolation level setting is being done, the connection picked from CP1 will decide the
    isolation level. For e.g. if such a query requests for a connection
    and CP1 uses C1(Serializable) to execute this query then this query
    will execute in Serializable mode even though you didn't explicitly
    set it.

Hope that clears a few doubts. 🙂

Best Answer

Isolation levels are implemented in the underlying DBMS, say SqlServer. Setting the isolation level most probably sets up SQL commands which set the isolation level for the connection.

The DBMS keeps the isolation level as long as the connection stays open. Because the connections is put into the pool, it stays open and keeps the settings made before.

When messing around with isolation levels, you should either reset the isolation level at the end of any transaction, or, even better, set it when a new connection is requested.

Related Topic