title: How does SqlConnection manage IsolationLevel?
body: <p>This <a href="http://msdn.microsoft.com/en-us/library/tcbchxcb.aspx" rel="noreferrer">MSDN article</a> states that:  </p>
  
  <blockquote>
    <p>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.</p>
  </blockquote>
  
  <p>The <a href="http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlconnection_members%28v=VS.71%29.aspx" rel="noreferrer">SqlConnection</a> class has no member that may hold the isolation level. So how does a connection know what isolation level to run in???  </p>
  
  <p>The reason I'm asking this is because of the following scenario:  </p>
  
  <ol>
  <li>I opened a transaction using
  TransactionScope in Serializable
  mode, say "T1".</li>
  <li>Opened a connection for T1.</li>
  <li>T1 is finished/disposed, connection
  goes back to connection pool.</li>
  <li>Called another query on same
  connection (after getting it from
  connection pool) and this query runs
  in serializable mode!!!</li>
  </ol>
  
  <p>Problem:  </p>
  
  <ol>
  <li>How does the pooled connection still
  know what isolation level was
  associated to it???</li>
  <li>How to revert it back to some other
  transaction level???</li>
  </ol>
  
  <blockquote>
    <p><strong>Resolution:</strong><br>
    The reason why pooled connections are returning the serializable isolation level is because of the following reason:</p>
    
    <ol>
    <li>You have one connection pool (let's say CP1)</li>
    <li>CP1 may have 50 connections.</li>
    <li>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).</li>
    <li>After executing the query C1(Serializable) goes back to CP1.</li>
    <li>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.</li>
    <li>So, slowly, Serialzable is set to multiple connections in CP1.</li>
    <li>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.</li>
    </ol>
    
    <p>Hope that clears a few doubts. :)</p>
  </blockquote>