This article explains how to get, set, and reset the transaction isolation level on a SQL and Entity connection. In a previous article I already explained how important it is to explicitly set the appropriate isolation level when using transactions. I'm sure you're not going to wrap each and every database call in an explicit transaction (TransactionScope is simply too heavy to wrap around a simple SELECT statement). Nevertheless you should realize that every single T-SQL statement that you launch from your application will run in a transaction, hence will behave according to a transaction isolation level.
If you don't explicitely start a transaction or use a transaction scope, then SQL Server will run the statement as a transaction on its own. You don't want your SQL commands to read unofficial data (in the READ UNCOMMITTED level) or apply too heavy locks (in the SERIALIZABLE level) on the database, so you want to make sure that you're running with the correct isolation level. This article explains how to do this.
Setting the Transaction Isolation Level
Setting the appropriate isolation level on a session/connection is done with a standard T-SQL statement, like the following:
T-SQL
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
It's easy to write an extension method on the SqlConnection and EntityConnection classes that allows you to do this call in C#, like this:
C#
using (AdventureWorks2008Entities model = new AdventureWorks2008Entities())
{
model.Connection.Open();
// Explicitely set isolation level
model.Connection.SetIsolationLevel(IsolationLevel.ReadUncommitted);
// Your stuff here ...
}
Here's the whole extension method. I implemented it against IDbConnection to cover all connection types. The attached Visual Studio solution contains the full code:
C#
public static void SetIsolationLevel(this IDbConnection connection, IsolationLevel isolationLevel)
{
if (isolationLevel == IsolationLevel.Unspecified || isolationLevel == IsolationLevel.Chaos)
{
throw new Exception(string.Format("Isolation Level '{0}' can not be set.", isolationLevel.ToString()));
}
if (connection is EntityConnection)
{
SqlConnection sqlConnection = (connection as EntityConnection).StoreConnection as SqlConnection;
sqlConnection.SetIsolationLevel(isolationLevel);
}
else if (connection is SqlConnection)
{
IDbCommand command = connection.CreateCommand();
command.CommandText = string.Format("SET TRANSACTION ISOLATION LEVEL {0}", isolationLevels[isolationLevel]);
command.ExecuteNonQuery();
}
}
Getting the Transaction Isolation Level
If you want to retrieve the current isolation level on your connection, you have to first figure out how to do this in T-SQL. Unfortunately there is no standard @@ISOLATIONLEVEL function or so. Here's how to do it:
T-SQL
SELECT CASE transaction_isolation_level
WHEN 0 THEN 'Unspecified'
WHEN 1 THEN 'Read Uncommitted'
WHEN 2 THEN 'Read Committed'
WHEN 3 THEN 'Repeatable Read'
WHEN 4 THEN 'Serializable'
WHEN 5 THEN 'Snapshot'
END AS [Transaction Isolation Level]
FROM sys.dm_exec_sessions
WHERE session_id = @@SPID
Altough you're querying a dynamic management view, the code requires no extra SQL Permissions (not even VIEW SERVER STATE). A user can always query his own sessions.
Again, you can easily wrap this into an extension method, that can be called like this:
C#
using (AdventureWorks2008Entities model = new AdventureWorks2008Entities())
{
model.Connection.Open();
// Get isolation level
// Probably returns 'ReadUncommitted' (due to connection pooling)
MessageBox.Show(model.Connection.GetIsolationLevel().ToString());
}
If you run the same code from the attached project, you'll indeed notice that the returned isolation level will be -most probably- READ UNCOMMITTED. This is because we -most probably- reuse the connection from the SetIsolationLevel() sample. As I already mentioned in a previous article, the transaction isolation level is NOT reset on pooled connections. So even if you're not explicitly use transactions, you still should still set the appropriate transaction isolation level. There's no default you can rely on.
OK, here's the corresponding extension method:
C#
public static IsolationLevel GetIsolationLevel(this IDbConnection connection)
{
string query =
@"SELECT CASE transaction_isolation_level
WHEN 0 THEN 'Unspecified'
WHEN 1 THEN 'ReadUncommitted'
WHEN 2 THEN 'ReadCommitted'
WHEN 3 THEN 'RepeatableRead'
WHEN 4 THEN 'Serializable'
WHEN 5 THEN 'Snapshot'
END AS [Transaction Isolation Level]
FROM sys.dm_exec_sessions
WHERE session_id = @@SPID";
if (connection is EntityConnection)
{
return (connection as EntityConnection).StoreConnection.GetIsolationLevel();
}
else if (connection is SqlConnection)
{
IDbCommand command = connection.CreateCommand();
command.CommandText = query;
string result = command.ExecuteScalar().ToString();
return (IsolationLevel)Enum.Parse(typeof(IsolationLevel), result);
}
return IsolationLevel.Unspecified;
}
Simple and powerful, isn't it? Stuff like this should ship with the framework!
Temporarily using a Transaction Isolation Level
With the new GetIsolationLevel() and SetIsolationLevel() methods it becomes easy to set the isolation level to execute some commands, and then reset the level to its original value. I wrapped these calls in a class implementing IDisposable so you can apply the using statement, like this:
C#
using (AdventureWorks2008Entities model = new AdventureWorks2008Entities())
{
model.Connection.Open();
// Set and reset isolation level
using (TransactionIsolationLevel inner = new TransactionIsolationLevel(model.Connection, IsolationLevel.Snapshot))
{
// Your stuff here ...
}
}
Again, the code is very straightforward. All you need is a constructor, a Dispose-method, and a variable to store the original isolation level:
C#
/// <summary>
/// Transaction Isolation Level.
/// </summary>
public class TransactionIsolationLevel : IDisposable
{
/// <summary>
/// The database connection.
/// </summary>
private IDbConnection connection;
/// <summary>
/// Original isolation level of the connection.
/// </summary>
private IsolationLevel originalIsolationLevel;
/// <summary>
/// Initializes a new instance of the TransactionIsolationLevel class.
/// </summary>
/// <param name="connection">Database connection.</param>
/// <param name="isolationLevel">Required isolation level.</param>
public TransactionIsolationLevel(IDbConnection connection, IsolationLevel isolationLevel)
{
this.connection = connection;
this.originalIsolationLevel = this.connection.GetIsolationLevel();
this.connection.SetIsolationLevel(isolationLevel);
}
/// <summary>
/// Resets the isolation level back to the original value.
/// </summary>
public void Dispose()
{
this.connection.SetIsolationLevel(this.originalIsolationLevel);
}
}
Source Code
The attached project contains the extension methods, the IDisposable class, and some demo calls against a local AdventureWorks database:
Here it is: U2UConsult.SQL.TransactionIsolationLevel.Sample.zip (87,53 kb)
Enjoy!