The TransactionScope class seems to "leak" it's isolation level to future queries on the same (pooled) connection.
I would expect the isolation level of the connection to be restored when the transaction ends or is disposed.
Here is a snippet which reproduces the behavior:
SqlConnection.ClearAllPools();
var conn = new SqlConnection(new SqlConnectionStringBuilder { DataSource = @".\sqlexpress", IntegratedSecurity = true }.ConnectionString);
Action printIsolationLevel = () =>
{
var cmd = conn.CreateCommand();
cmd.CommandText = @"SELECT CASE transaction_isolation_level
WHEN 0 THEN 'Unspecified'
WHEN 1 THEN 'ReadUncommitted'
WHEN 2 THEN 'ReadCommitted'
WHEN 3 THEN 'Repeatable'
WHEN 4 THEN 'Serializable'
WHEN 5 THEN 'Snapshot' END AS TRANSACTION_ISOLATION_LEVEL
FROM sys.dm_exec_sessions
where session_id = @@SPID";
Console.WriteLine(cmd.ExecuteScalar());
};
conn.Open();
printIsolationLevel(); // "ReadCommitted"
conn.Close();
using (var scope = new TransactionScope(
TransactionScopeOption.RequiresNew,
new TransactionOptions { IsolationLevel = System.Transactions.IsolationLevel.Serializable }))
{
conn.Open();
printIsolationLevel(); // "Serializable"
conn.Close();
}
conn.Open();
printIsolationLevel(); // "Serializable" !!?
conn.Close();
The TransactionScope class seems to "leak" it's isolation level to future queries on the same (pooled) connection.
I would expect the isolation level of the connection to be restored when the transaction ends or is disposed.
Here is a snippet which reproduces the behavior: