how to change isolation level?

Question

I am using EF 4.0, and I would like to use the isolation level serializable, because in a transaction I would like to block a register when is read.

Well, in SQL Server I try to change the isolation level with this command:

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

And in C# I use this code to try to block the register:

using (var scope = new TransactionScope(TransactionScopeOption.Required, new TransactionOptions { IsolationLevel = System.Transactions.IsolationLevel.Serializable }))
{
   Entities myContext = new Entities();
   string colors = "select * from Colors where IDColor = 2";
   Colors myColor = myContext.Colors.SqlQuery(colors).FirstOrDefault<Colors>();
   myColor.Color = "Red";
   myContext.SaveChanges();
   scope.Complete();                  
}

If I execute line by line my code, if I get the SaveChanges but still is not execute, if I do a query to the table colors with SQL Server Management Studio for example, I can get the record.

Then, if I execute the save changes and I get the scope.Clompete(), the if I try the query with Management Studio, I get nothing because the register is blocked, so when I finish my C# code, the register is release and I get the result in Management Studio.

So my question is, how to use the serializable isolation level in C#? am I doing something wrong?

P.S.: I notice that if I use this command:

DBCC useroptions;

I see that the isolation level is serializable, but if I close Management Studio and connect again and see the isolation level is the default readCommitted.

So my question is how to set the default isolation level for my database.

Answer 1

• Default EF transaction isolation level is based on used database provider.

• Unspecified isolation level in your ef code should result in default isolation level for database server.

• In SQL Server default isolation level is READ COMMITED.

• So you don't need to specify IsolationLevel on your EF code.If you set it on DB side it takes as default IsolationLevel for EF as well.

How to change IsolationLevel on DB Check Isolation Levels in the Database Engine and SET TRANSACTION ISOLATION LEVEL (Transact-SQL)

UPDATE

For change the isolation level run below mentioned command on SSMS :

USE YourDatabaseName;
GO
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

For check whether is it applied ?

USE YourDatabaseName;
GO
DBCC useroptions

MSDN says:

Only one of the isolation level options can be set at a time, and it remains set for that connection until it is explicitly changed. All read operations performed within the transaction operate under the rules for the specified isolation level unless a table hint in the FROM clause of a statement specifies different locking or versioning behavior for a table.

I hope this will help to you.