I ran into an interesting issue today.  I have a .NET code object that access the database to read from a custom audit table.  Since the audit table gets inserted into as part of every transaction I wanted to make sure the code that reads from the table doesn’t get blocked and doesn’t block the normal business transactions.  I decided to have the reads run with an isolation level of “Read Uncommitted”.  I realize there is a risk of reading a record that isn’t committed, but the way the processing works the insert into the audit table is the last thing that happens before a transaction commits.  So I figure it is extremely low risk.

I decided to simply wrap my data access calls with a TransactionScope like the following:

TransactionOptions transactionOptions = new TransactionOptions();
transactionOptions.IsolationLevel = IsolationLevel.ReadUncommitted;

using (TransactionScope ts = 

new TransactionScope(TransactionScopeOption.Suppress, 

transactionOptions, EnterpriseServicesInteropOption.None))
{
  // Code that retrieves data from the database
  ts.Complete();
}

When I went to test this out it didn’t work.  It ran, but if another process had a lock on the audit table I was still getting blocked.  After thinking about this a bit I realized what was happening.  Because I set the TransactionScopeOption to “Suppress” the TransactionOptions parameter was completely ignored. After all, there wasn’t a transaction, so no need to worry about options. 

To get things to work I had to change the TransactionScopeOption to “RequiresNew”.  It seems a bit odd to have to do that, but my other option would have been to modify the SQL to use “WITH (NOLOCK)” or pass the SQL to set the isolation level directly.  Since I want to support multiple DBMS’s I wanted to avoid doing anything funky with the SQL.