TransactionScope, IsolationLevel, ReadUncommitted: Using SqlConnection and TransactionScopes right in c#

first, lets look at some code we’re using:

var options = new TransactionOptions()
{
IsolationLevel = IsolationLevel.ReadUncommitted,
}
using(var ts = new TransactionScope(TransactionScopeOption.RequiresNew, options))
{
using(var c = new SqlConnection(...))
{
// write some data with sql connection
}
}

note the “ReadUncommitted” transaction level above.

if we look at the transaction level inside the using of the SqlConnection its, like expected, “ReadUncommitted”.

ok, so far so good, everything fine here. now, lets read some data:

using(var c = new SqlConnection(...))
{
// read some data with sql connection
}

lets look at the transaction level again within the using: its “Read Uncommitted“. hmm. but the default level of SQL Server is “Read Committed” isnt it?!

lets rerun our program and execute this snippet again wihout executing a transaction scope before:

using(var c = new SqlConnection(...))
{
// read some data with sql connection
}

“Read Committed“.

to understand this behavior we need some facts:

  1. the default transaction level for a new sql server session (connection) is “Read Committed”
  2. the last setted transaction level is kept for the time the session exists (logical, if we set it, we expect it to be set until we change it)
  3. SqlConnection is pooled by default (can be disabled, but we want the speed improvements)

ok, knowing all that stuff we learned above this is also comprehensible, but not really intuitive. i would have expected the connection is reset when put back into the pool to its initial transaction level, whatever it was at that time, but thats not the case.

ok, knowing this behavior we still dont know how this could possibly cause a database inconsistency…

we contacted MS Support and wanted help on that topic. sent them the logs of this machine, they said: you’re using “Read Uncommitted”, dont use that, use snapshot (available since sql 2008). because when using Read Uncommitted when reading data from a table with ntext or nvarchar(max) [this is our “off row data” from the message above] this could lead to inconsistency.

so we ended up changing our code to ensure a transaction level before we’re using it. therefore we changed our ConnectionFactory (fortunately we had one ;)) to execute something like this before returning a SqlConnection:

private void EnsureDefaultIsolationLevel(DbConnection connection, IsolationLevel isolationLevel)
{
if (isolationLevel == IsolationLevel.Unspecified)
return;
// level is explicitly defined, we dont want to change that
if (System.Transactions.Transaction.Current != null)
return;

connection.EnsureOpen();

DbCommand command;
if (!TryCreateCommand(out command))
return;

using (command)
{
command.Connection = connection;
var sqlIsolationLevel = IsolationLevelMap[isolationLevel];
command.CommandText = $"SET TRANSACTION ISOLATION LEVEL {sqlIsolationLevel};";
command.ExecuteNonQuery();
}
}

so what this code does is simply:

check if there is a transaction scope defined, if yes, do nothing because transactionscope defines a isolation level. otherwise simply execute a “set transaction isolation level” command to ensure we’re not “recycling” the last isolation level.

good to know: why does first check “Transaction.Current != null” work? because when you dont define the transactionscope before the connection is opened, the transactionscope is useless and the default transactionlevel is kept and no transaction is created on the sql server.

until now the error has not happened again, so we (and the MS support guy) think this is the solution. we’ll see 😉

hope this helps somebody to better understand how this stuff works internally and what the caveats are you have to implement when you want to be sure which transaction level your sql connection uses if you don’t explicitly define one 😉
or maybe out there is somebody thats facing the exact same issue right now. 😀