LINQ + TransactionScope将不会更改SQL Server Profiler中的隔离级别 [英] LINQ + TransactionScope will not change isolation level in SQL Server Profiler

问题描述

我正在使用以下格式通过linq提交对数据库的更改.

I'm using the following format for commiting changes to my db using linq.

Begin Transaction (Scope Serialized, Required)
    Check Business Rule 1...N
    MyDataContext.SubmitChanges()
    Save Changes Done In Previous Query To Log File
End Transaction Scope

但是在SQL Server分析器中，我在Connection:Start中看到以下行.

But in the SQL Server profiler I see the following line in the Connection:Start.

set transaction isolation level read committed

我经历了这个过程( http://social.msdn.microsoft.com/Forums/zh-CN/adodotnetentityframework/thread/93a45026-0425-4d49-a4ac-1b882e90e6d5 )，并认为我有答案；

I went through this (http://social.msdn.microsoft.com/Forums/en-US/adodotnetentityframework/thread/93a45026-0425-4d49-a4ac-1b882e90e6d5) and thought I had an answer;

直到我在Microsoft Connect上看到此(https://connect.microsoft.com/VisualStudio/feedback/details/565441/transactionscope-linq-to-sql?wa=wsignin1.0).

Until I saw this (https://connect.microsoft.com/VisualStudio/feedback/details/565441/transactionscope-linq-to-sql?wa=wsignin1.0) on Microsoft Connect.

有人可以告诉我我的代码是否实际上是在序列化隔离级别下执行的，或者只是在读取已提交状态下运行的情况下才有效吗?

Can someone please tell me whether my code is actually executed under Serialized Isolation Level or whether it is infact just running under read committed?

推荐答案

这取决于您如何创建事务.

It depends on how you created the transaction.

如果执行内联SQL来开始它(例如，EG.BEGIN TRAN)，则L2S将不知道该事务，并将在READ COMMITTED中启动一个新的嵌套事务.

If you executed inline SQL to begin it (EG. BEGIN TRAN), L2S will not be aware of the transaction and will spin up a new nested one in READ COMMITTED.

但是，如果您使用System.Transaction或在DataContext上设置了事务，则SubmitChanges将参与该事务.

However, if you used System.Transaction, or have a transaction set on your DataContext, SubmitChanges will participate in that transaction.

如果选择TM: Begin Tran和TM: Commit Tran事件类，则可以在Profiler中看到这些事务的开始和停止.

You can see these transaction starting and stopping in Profiler if you choose the TM: Begin Tran and TM: Commit Tran event classes.

注意:ADO.Net不会批量发布BEGIN TRAN，也不会批量发布SET TRANSACTION ISOLATION，这是在较低级别完成的.

Note: ADO.Net does not issue BEGIN TRAN nor does it issue SET TRANSACTION ISOLATION in batches, this is done at a lower level.

如果您确实要确认该行为，请在表上创建一个触发器，该触发器将当前隔离级别插入到日志记录表中并进行检查.

If you really want to confirm the behavior, create a trigger on a table that inserts the current isolation level into a logging table and check on it.

您可以通过运行以下命令来提高当前的隔离级别:

You can pick up your current isolation level by running:

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

这篇关于LINQ + TransactionScope将不会更改SQL Server Profiler中的隔离级别的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！