比尔云BierYun--阿里云最新优惠活动
阿里云优惠码丨阿里云代金券

SQL Server死锁中的会话隔离级别为序列化(Serializable)实验测试

SQL Server死锁中的会话隔离级别为序列化(Serializable)实验测试

 

最近在分析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 TRANUPDATE [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文件。

 

 

 

<deadlock>  <victim-list>    <victimProcess id=”process17676e108″ />  </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″>      <executionStack>        <frame procname=”adhoc” line=”8″ stmtstart=”282″ stmtend=”368″ sqlhandle=”0x020000002a285923f5e38f7347b53337195c56a4a1bc33080000000000000000000000000000000000000000″>unknown    </frame>      </executionStack>      <inputbuf>BEGIN TRANUPDATE [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″>      <executionStack>        <frame procname=”AdventureWorks2014.Sales.iduSalesOrderDetail” line=”18″ stmtstart=”982″ stmtend=”2448″ sqlhandle=”0x0300070076146e6c18e00a016ba3000000000000000000000000000000000000000000000000000000000000″>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>        <frame procname=”adhoc” line=”1″ stmtstart=”52″ stmtend=”262″ sqlhandle=”0x02000000abf4ee0ff24fea415c6f35709c721203030a173b0000000000000000000000000000000000000000″>unknown    </frame>        <frame procname=”adhoc” line=”1″ stmtend=”186″ sqlhandle=”0x02000000b0cd40243d43ed1a51b1baa9cbf70d1628eae7880000000000000000000000000000000000000000″>unknown    </frame>      </executionStack>      <inputbuf>UPDATE Sales.SalesOrderDetail SET OrderQty=2 WHERE SalesOrderID=43659 AND SalesOrderDetailID=1;   </inputbuf>    </process>  </process-list>  <resource-list>    <keylock hobtid=”72057594048479232″ dbid=”7″ objectname=”AdventureWorks2014.Sales.SalesOrderDetail” indexname=”PK_SalesOrderDetail_SalesOrderID_SalesOrderDetailID_old” id=”lock154ffb300″ mode=”X” associatedObjectId=”72057594048479232″>      <owner-list>        <owner id=”process175603c28″ mode=”X” />      </owner-list>      <waiter-list>        <waiter id=”process17676e108″ mode=”S” requestType=”wait” />      </waiter-list>    </keylock>    <keylock hobtid=”72057594048544768″ dbid=”7″ objectname=”AdventureWorks2014.Sales.SalesOrderHeader” indexname=”PK_SalesOrderHeader_SalesOrderID” id=”lock155a8fa00″ mode=”X” associatedObjectId=”72057594048544768″>      <owner-list>        <owner id=”process17676e108″ mode=”X” />      </owner-list>      <waiter-list>        <waiter id=”process175603c28″ mode=”S” requestType=”wait” />      </waiter-list>    </keylock>  </resource-list></deadlock>

 

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

 

 

DECLARE @end_time DATETIME;SET @end_time = DATEADD(SECOND, 10, GETDATE()); WHILE GETDATE() < @end_timeBEGIN INSERT INTO mintor_isolcation_levelSELECT  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) phEND

 

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

 

 

 

 

 

 

是否有点意外,其实官方文档已有详细介绍(详见参考资料),摘抄部分信息如下,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/

评论 抢沙发

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址

强烈推荐

高性能SSD云服务器ECS抗攻击,高可用云数据库RDS