SQL Server 永久修改事务隔离级别 sql server默认隔离级别

转载

编程小匠人传奇 2025-09-04 20:53:59

文章标签 Sales 隔离级别 sql 文章分类 SQL Server 数据库

最近在分析SQL Server的死锁时，发现一个比较有意思的现象，发现死锁当中一个会话的隔离级别为序列化（Serializable），这个是让人比较奇怪的地方，我们知道SQL Server数据库的默认隔离级别为已提交读（READ COMMITTED），除非人为设置事务隔离级别(TRANSACTION ISOLATION LEVEL)，否则事务隔离级别会使用数据库的默认隔离级别。在分析了死锁相关的存储过程后，没有发现有人为修改事务隔离级别的地方。在分析过后，我们判断应该是在应用程序代码里面有设置隔离级别，下面我们通过一个小实验来构造这样的一个案例。

测试环境数据库为AdventureWorks2014，如下所示，我简单写了一点C#代码，截取黏贴部分C#代码在此，在这段代码中，我们使用TransactionScope，我们先更新Sales.SalesOrderDetail,然后查询 [Sales].[SalesOrderHeader]的相关数据来绑定Grid控件

try

{

using (TransactionScope scope = new TransactionScope())

{

using (SqlConnection conn = new SqlConnection(connString))

{

string cmdText = "UPDATE Sales.SalesOrderDetail SET OrderQty=2 WHERE SalesOrderID=43659 AND SalesOrderDetailID=1;";

SqlCommand cmd = new SqlCommand(cmdText, conn);

conn.Open();

cmd.ExecuteNonQuery();

}

using (SqlConnection conn = new SqlConnection(connString))

{

DataSet sqldataset = new DataSet();

string cmdText = "SELECT * FROM [Sales].[SalesOrderHeader] WHERE SalesOrderID=43659;";

SqlCommand cmd = new SqlCommand(cmdText, conn);

SqlDataAdapter sqladapter = new SqlDataAdapter(cmdText, conn);

sqladapter.Fill(sqldataset, "spt_values");

gvData.DataSource = sqldataset;

gvData.DataBind();

}

scope.Complete();

}

catch (TransactionAbortedException exc)

{

log.Error("错误", exc);

}

然后另外一个会话，就直接用SSMS开启一个事务（懒得构造C#代码案例，主要是太浪费时间了），主要执行下面逻辑：

BEGIN TRAN

UPDATE [Sales].[SalesOrderHeader] SET SubTotal = SubTotal + 10

WHERE SalesOrderID=43659;

WAITFOR DELAY '00:00:10';

SELECT  TOP 10 * FROM Sales.SalesOrderDetail

--ROLLBACK TRAN;

执行上面SQL语句，然后运行最上面C#代码，立马就能构造出一个死锁案例，如下截图所示，测试环境为SQL Server 2014，我就使用扩展事件system_health捕获的死锁（当然，你可以使用任何方式，例如Profile或Trace捕获死锁相关信息），使用SQL将死锁的XML信息查出

如下所示，你会看到使用TransactionScope的会话的隔离级别为isolationlevel="serializable (4)"，具体可以参考下面死锁的XML文件。

<victim-list>

</victim-list>

<process-list>

<process id="process17676e108" taskpriority="0" logused="384" waitresource="KEY: 7:72057594048479232 (0ca7b7436f59)" waittime="379" ownerId="46635671" transactionname="user_transaction" lasttranstarted="2019-04-02T23:26:21.150" XDES="0x17f0511f0" lockMode="S" schedulerid="1" kpid="13440" status="suspended" spid="61" sbid="0" ecid="0" priority="0" trancount="1" lastbatchstarted="2019-04-02T23:26:21.147" lastbatchcompleted="2019-04-02T23:26:09.343" lastattention="1900-01-01T00:00:00.343" clientapp="Microsoft SQL Server Management Studio - Query" hostname="MyNB00021" hostpid="9728" loginname="test" isolationlevel="read committed (2)" xactid="46635671" currentdb="7" lockTimeout="4294967295" clientoption1="671090784" clientoption2="390200">

unknown    </frame>

</executionStack>

BEGIN TRAN

UPDATE [Sales].[SalesOrderHeader] SET SubTotal = SubTotal + 10

WHERE SalesOrderID=43659;

WAITFOR DELAY '00:00:10';

SELECT  TOP 10 * FROM Sales.SalesOrderDetail   </inputbuf>

</process>

<process id="process175603c28" taskpriority="0" logused="436" waitresource="KEY: 7:72057594048544768 (6a8a6db47ef5)" waittime="4420" ownerId="46635065" transactionname="user_transaction" lasttranstarted="2019-04-02T23:25:36.807" XDES="0x1762fa9f0" lockMode="S" schedulerid="1" kpid="51760" status="suspended" spid="63" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2019-04-02T23:26:26.450" lastbatchcompleted="2019-04-02T23:25:36.807" lastattention="1900-01-01T00:00:00.807" clientapp=".Net SqlClient Data Provider" hostname="MyNB00021" hostpid="1700" loginname="kkk" isolationlevel="serializable (4)" xactid="46635065" currentdb="7" lockTimeout="4294967295" clientoption1="673316896" clientoption2="128056">

INSERT INTO [Production].[TransactionHistory]

([ProductID]

,[ReferenceOrderID]

,[ReferenceOrderLineID]

,[TransactionType]

,[TransactionDate]

,[Quantity]

,[ActualCost])

SELECT

inserted.[ProductID]

,inserted.[SalesOrderID]

,inserted.[SalesOrderDetailID]

,'S'

,GETDATE()

,inserted.[OrderQty]

,inserted.[UnitPrice]

FROM inserted

INNER JOIN [Sales].[SalesOrderHeader]

ON inserted.[SalesOrderID] = [Sales].[SalesOrderHeader].[SalesOrderID    </frame>

unknown    </frame>

unknown    </frame>

</executionStack>

UPDATE Sales.SalesOrderDetail SET OrderQty=2 WHERE SalesOrderID=43659 AND SalesOrderDetailID=1;   </inputbuf>

</process>

</process-list>

<resource-list>

<owner-list>

</owner-list>

<waiter-list>

</waiter-list>

</keylock>

<owner-list>

</owner-list>

<waiter-list>

</waiter-list>

</keylock>

</resource-list>

</deadlock>

我们也可以使用下面SQL语句来捕获会话的的隔离级别（根据实际情况调整），在运行上面C#代码期间捕获会话信息，如下截图所示：

DECLARE @end_time DATETIME;

SET @end_time = DATEADD(SECOND, 10, GETDATE());

WHILE GETDATE() < @end_time

BEGIN

INSERT INTO mintor_isolcation_level

SELECT  session_id ,

start_time ,

status ,

total_elapsed_time ,

CASE transaction_isolation_level

WHEN 1 THEN 'ReadUncomitted'

WHEN 2 THEN 'ReadCommitted'

WHEN 3 THEN 'Repeatable'

WHEN 4 THEN 'Serializable'

WHEN 5 THEN 'Snapshot'

ELSE 'Unspecified'

END AS transaction_isolation_level ,

sh.text ,

ph.query_plan

FROM    sys.dm_exec_requests

CROSS APPLY sys.dm_exec_sql_text(sql_handle) sh

CROSS APPLY sys.dm_exec_query_plan(plan_handle) ph

END

因为上面的脚本执行时间太短，所以有可能捕获到的是相关SQL运行期间的触发器脚本。如果要清晰的捕获相关SQL，可以构造一个执行时间较长的SQL

是否有点意外，其实官方文档已有详细介绍（详见参考资料），摘抄部分信息如下，TransactionScope如果不指定隔离级别，默认情况下，事务隔离级别为Serializable

设置 TransactionScope 隔离级别

除超时值之外，TransactionScope 的有些重载构造函数还接受 TransactionOptions 类型的结构，用于指定隔离级别。默认情况下，事务在隔离级别设置为 Serializable 的情况下执行。通常对频繁执行读取的系统选择 Serializable 之外的隔离级别。这需要全面地了解事务处理理论、事务本身的语义、所涉及的并发问题以及系统一致性的结果。

总结

这里只是一个案例，仅仅说明应用程序的驱动程序或API函数，有可能会需要（或默认）设定事务的隔离级别，这个一定要当心，避免由于人为失误导(不了解技术细节）致不小心提高事务隔离级别，造成不必要的死锁出现。另外，这里总结这篇文章，也仅仅是对这种案例感到有意思。

本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。