经验首页 前端设计 程序设计 Java相关 移动开发 数据库/运维 软件/图像 大数据/云计算 其他经验
当前位置：技术经验 » 数据库/运维 » MS SQL Server » 查看文章
SQL Server死锁中的会话隔离级别为序列化（Serializable）实验测试
来源：cnblogs　　作者：潇湘隐者　　时间：2019/4/3 16:21:16　　对本文有异议

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

 

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

 

  1. try
  1.        {
  1.            using (TransactionScope scope = new TransactionScope())
  1.            {
  1.                using (SqlConnection conn = new SqlConnection(connString))
  1.                {
  1.                    string cmdText = "UPDATE Sales.SalesOrderDetail SET OrderQty=2 WHERE SalesOrderID=43659 AND SalesOrderDetailID=1;";
  1.  
  1.                    SqlCommand cmd = new SqlCommand(cmdText, conn);
  1.  
  1.                    conn.Open();
  1.                    cmd.ExecuteNonQuery();
  1.  
  1.                }
  1.                using (SqlConnection conn = new SqlConnection(connString))
  1.                {
  1.                    DataSet sqldataset = new DataSet(); 
  1.                    string cmdText = "SELECT * FROM [Sales].[SalesOrderHeader] WHERE SalesOrderID=43659;";
  1.  
  1.                    SqlCommand cmd = new SqlCommand(cmdText, conn);
  1.  
  1.                    SqlDataAdapter sqladapter = new SqlDataAdapter(cmdText, conn);
  1.  
  1.                    sqladapter.Fill(sqldataset, "spt_values");
  1.                    gvData.DataSource = sqldataset;
  1.                   gvData.DataBind();
  1.  
  1.                }
  1.                scope.Complete();
  1.            }
  1.        }
  1.        catch (TransactionAbortedException exc)
  1.        {
  1.            log.Error("错误", exc);
  1.        }

 

 

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

 

  1. BEGIN TRAN
  1. UPDATE [Sales].[SalesOrderHeader] SET SubTotal = SubTotal + 10 
  1. WHERE SalesOrderID=43659;
  1.  
  1.  
  1. WAITFOR DELAY '00:00:10';
  1.  
  1. SELECT  TOP 10 * FROM Sales.SalesOrderDetail
  1.  
  1. --ROLLBACK TRAN;

 

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

 

 

clip_image001

 

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

 

 

clip_image002

 

image

  1. <deadlock>
  1.   <victim-list>
  1.     <victimProcess id="process17676e108" />
  1.   </victim-list>
  1.   <process-list>
  1.     <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">
  1.       <executionStack>
  1.         <frame procname="adhoc" line="8" stmtstart="282" stmtend="368" sqlhandle="0x020000002a285923f5e38f7347b53337195c56a4a1bc33080000000000000000000000000000000000000000">
  1. unknown    </frame>
  1.       </executionStack>
  1.       <inputbuf>
  1. BEGIN TRAN
  1. UPDATE [Sales].[SalesOrderHeader] SET SubTotal = SubTotal + 10 
  1. WHERE SalesOrderID=43659;
  1.  
  1.  
  1.   WAITFOR DELAY '00:00:10';
  1.  
  1.   SELECT  TOP 10 * FROM Sales.SalesOrderDetail   </inputbuf>
  1.     </process>
  1.     <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">
  1.       <executionStack>
  1.         <frame procname="AdventureWorks2014.Sales.iduSalesOrderDetail" line="18" stmtstart="982" stmtend="2448" sqlhandle="0x0300070076146e6c18e00a016ba3000000000000000000000000000000000000000000000000000000000000">
  1. INSERT INTO [Production].[TransactionHistory]
  1.                 ([ProductID]
  1.                 ,[ReferenceOrderID]
  1.                 ,[ReferenceOrderLineID]
  1.                 ,[TransactionType]
  1.                 ,[TransactionDate]
  1.                 ,[Quantity]
  1.                 ,[ActualCost])
  1.             SELECT 
  1.                 inserted.[ProductID]
  1.                 ,inserted.[SalesOrderID]
  1.                 ,inserted.[SalesOrderDetailID]
  1.                 ,'S'
  1.                 ,GETDATE()
  1.                 ,inserted.[OrderQty]
  1.                 ,inserted.[UnitPrice]
  1.             FROM inserted 
  1.                 INNER JOIN [Sales].[SalesOrderHeader] 
  1.                 ON inserted.[SalesOrderID] = [Sales].[SalesOrderHeader].[SalesOrderID    </frame>
  1.         <frame procname="adhoc" line="1" stmtstart="52" stmtend="262" sqlhandle="0x02000000abf4ee0ff24fea415c6f35709c721203030a173b0000000000000000000000000000000000000000">
  1. unknown    </frame>
  1.         <frame procname="adhoc" line="1" stmtend="186" sqlhandle="0x02000000b0cd40243d43ed1a51b1baa9cbf70d1628eae7880000000000000000000000000000000000000000">
  1. unknown    </frame>
  1.       </executionStack>
  1.       <inputbuf>
  1. UPDATE Sales.SalesOrderDetail SET OrderQty=2 WHERE SalesOrderID=43659 AND SalesOrderDetailID=1;   </inputbuf>
  1.     </process>
  1.   </process-list>
  1.   <resource-list>
  1.     <keylock hobtid="72057594048479232" dbid="7" objectname="AdventureWorks2014.Sales.SalesOrderDetail" indexname="PK_SalesOrderDetail_SalesOrderID_SalesOrderDetailID_old" id="lock154ffb300" mode="X" associatedObjectId="72057594048479232">
  1.       <owner-list>
  1.         <owner id="process175603c28" mode="X" />
  1.       </owner-list>
  1.       <waiter-list>
  1.         <waiter id="process17676e108" mode="S" requestType="wait" />
  1.       </waiter-list>
  1.     </keylock>
  1.     <keylock hobtid="72057594048544768" dbid="7" objectname="AdventureWorks2014.Sales.SalesOrderHeader" indexname="PK_SalesOrderHeader_SalesOrderID" id="lock155a8fa00" mode="X" associatedObjectId="72057594048544768">
  1.       <owner-list>
  1.         <owner id="process17676e108" mode="X" />
  1.       </owner-list>
  1.       <waiter-list>
  1.         <waiter id="process175603c28" mode="S" requestType="wait" />
  1.       </waiter-list>
  1.     </keylock>
  1.   </resource-list>
  1. </deadlock>

 

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

 

 

  1. DECLARE @end_time DATETIME;
  1. SET @end_time = DATEADD(SECOND, 10, GETDATE());
  1.  
  1. WHILE GETDATE() < @end_time
  1. BEGIN
  1.  
  1. INSERT INTO mintor_isolcation_level
  1. SELECT  session_id ,
  1.         start_time ,
  1.         status ,
  1.         total_elapsed_time ,
  1.         CASE transaction_isolation_level
  1.           WHEN 1 THEN 'ReadUncomitted'
  1.           WHEN 2 THEN 'ReadCommitted'
  1.           WHEN 3 THEN 'Repeatable'
  1.           WHEN 4 THEN 'Serializable'
  1.           WHEN 5 THEN 'Snapshot'
  1.           ELSE 'Unspecified'
  1.         END AS transaction_isolation_level ,
  1.         sh.text ,
  1.         ph.query_plan 
  1. FROM    sys.dm_exec_requests
  1.         CROSS APPLY sys.dm_exec_sql_text(sql_handle) sh
  1.         CROSS APPLY sys.dm_exec_query_plan(plan_handle) ph
  1. END

 

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

 

 

 

clip_image003

 

 

 

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

 

 

设置 TransactionScope 隔离级别

 

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

 

 

总结

 

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

 

 

 

参考资料：

 

https://docs.microsoft.com/en-us/dotnet/framework/data/transactions/implementing-an-implicit-transaction-using-transaction-scope

原文链接：http://www.cnblogs.com/kerrycode/p/10646837.html

 友情链接：直通硅谷 　点职佳 　北美留学生论坛

MS SQL Server热门文章

  • [sql Server]除非另外还指定了TOP 或 FOR XML，否则，ORDER BY 子句在视图、内联函数、派生表、子查询和公用表表达式中无效
  • SQL 拼接字符串 使用IN查询方法
  • Microsoft SQL Server 双机热备份，实时同步
  • SQL Server 2016 + AlwaysOn 无域集群
  • Microsoft SQL Server 2012安装说明
  • mssql sqlserver 不固定行转列数据(动态列)
  • 解釋 SQL Server 的狀態值 Runnable、Running、Suspended、Sleeping
  • sql server中format函数的yyyyMMddHHmmssffff时间格式兼容旧版sql写法
  • The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION
  • SQL去除数据库表中tab、空格、回车符等特殊字符的解决方法

    • MS SQL Server推荐文章

  • 拯救SQL Server数据库事务日志文件损坏的终极大招
  • SQL Server实战七：自定义数据类型、标量值、内嵌表值、多语句表值函数的操作
  • SQL Server实战六：T-SQL、游标、存储过程的操作
  • SQL Server实战四：查询数据库的数据
  • 模拟SQLserver死锁现象
  • SqlServer的执行计划如何分析？
  • SQL Server实例间同步登录用户
  • SQL Server 2022新功能概览
  • SQL Server 2022 AlwaysOn新特性之包含可用性组介绍
  • 当 SQL Server（mssql-jdbc） 遇上 BigDecimal → 精度丢失，真坑！

    • 本站QQ群：前端 618073944 | Java 606181507 | Python 626812652 | C/C++ 612253063 | 微信 634508462 | 苹果 692586424 | C#/.net 182808419 | PHP 305140648 | 运维 608723728

    W3xue 的所有内容仅供测试，对任何法律问题及风险不承担任何责任。通过使用本站内容随之而来的风险与本站无关。
    关于我们  |  意见建议  |  捐助我们  |  报错有奖  |  广告合作、友情链接（目前9元/月）请联系QQ：27243702 沸活量
    皖ICP备17017327号-2　皖公网安备34020702000426号