如何更改隔离级别? [英] how to change isolation level?

问题描述

我正在使用EF 4.0，并且我想使用隔离级别，因为在事务中，我想在读取时阻止寄存器.

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.

好吧，在SQL Server中，我尝试使用以下命令更改隔离级别:

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

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

在C#中，我使用以下代码尝试阻止寄存器:

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();                  
}

如果我逐行执行我的代码，如果我得到了 SaveChanges ，但仍然没有执行，例如，如果我使用SQL Server Management Studio对表颜色进行查询，则可以得到记录.

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.

然后，如果我执行保存更改并得到 scope.Clompete()，则如果我尝试通过Management Studio查询，由于寄存器被阻止，我什么也不会得到，所以当我完成我的C#代码，释放寄存器，然后在Management Studio中得到结果.

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.

所以我的问题是，如何在C#中使用可序列化的隔离级别?我在做错什么吗?

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

P.S .:我注意到，如果我使用此命令:

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

DBCC useroptions;

我看到隔离级别是 serializable ，但是如果我关闭Management Studio并再次连接，并且看到隔离级别是默认的 readCommitted .

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.

推荐答案

• 默认EF事务隔离级别基于使用的数据库提供程序.

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

    ef代码中未指定的隔离级别应导致数据库服务器的默认隔离级别.

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

    在SQL Server中，默认隔离级别为READ COMMITED.

    In SQL Server default isolation level is READ COMMITED.

    因此您无需在EF代码上指定IsolationLevel.如果在数据库端进行设置，它也将作为EF的默认IsolationLevel.

    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.

    如何在DB Check上更改IsolationLevel 数据库引擎中的隔离级别 和 设置事务隔离级别(Transact-SQL)

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

    更新

    要更改隔离级别，请在SSMS上运行以下提到的命令:

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

    USE YourDatabaseName;
    GO
    SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
    

    要检查是否应用?

    USE YourDatabaseName;
    GO
    DBCC useroptions
    

    MSDN说:

    一次只能设置一个隔离级别选项，并且它可以保持为该连接设置的状态，直到对其进行显式更改为止.全部在事务内执行的读取操作在指定隔离级别的规则，除非语句的FROM子句指定不同的锁定或版本控制表的行为.

    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.

    这篇关于如何更改隔离级别?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！