文档章节

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

o
 osc_w9s1w4o0
发布于 2019/04/03 09:36
字数 1424
阅读 23
收藏 0

精选30+云产品,助力企业轻松上云!>>>

最近在分析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信息查出

 

 

clip_image001

 

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

 

 

clip_image002

 

image

<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 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">
      <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_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

 

 

 

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

o
粉丝 0
博文 500
码字总数 0
作品 0
私信 提问
加载中
请先登录后再评论。
验证mysql事务隔离级别机制加锁情况与MVCC机制

道路越深,即越孤独。  大家都知道,mysql innodb引擎支持事务,而事务具有ACID四大特性,分别是原子性,一致性,隔离性及持久性。其中事务的隔离性,指的是当多条事务并发时,对事务中的s...

osc_gd4rlfym
2019/05/06
5
0
事务隔离级别概念,事例以及分析.

from: http://marasuzifk.blog.51cto.com/24580/59591 事务的四个属性:原子性(atomicity)、一致性(consistency)、隔离性(isolation)和持久性(durability)。 1.原子性(Atomic) 最重要...

被风遗忘
2012/03/31
413
1
sql server 锁与事务拨云见日(下)

  在锁与事务系列里已经写完了上篇中篇,这次写完下篇。这个系列俺自认为是有条不紊的进行,但感觉锁与事务还是有多很细节没有讲到,温故而知新可以为师矣,也算是一次自我提高总结吧,也谢...

osc_8gtzj75i
2018/06/09
1
0
事务和锁

本文并不全是原创,我参考的原文在这里 背景: 当用户并发尝试访问同一数据的时,SQL Server尝试用锁来隔离不一致的数据和使用隔离级别查询数据时控制一致性(数据该如何读取),说起锁就会联想...

osc_13a0punx
2019/05/21
2
0
Innodb中常见SQL语句设置的锁类型

1、select … from 除了serializable隔离级别,这种SQL都是一致性非锁定读,不加锁;在serializable级别,这种SQL加next-key锁。 2、select … from … lock in share mode 这种SQL加S类型的...

未央&amp;
2019/04/14
0
0

没有更多内容

加载失败,请刷新页面

加载更多

Eclipse_JavaEE_Tomcat_MySQL环境配置

安装java环境,配置系统变量(JAVA_HOME,绝对路径) 下载eclipse+Tomcat+mysql window——》preference——》server——》runtime——》tomcat环境 项目右键build path 配mysql jar ,libra...

愿有时光可回首
31分钟前
20
0
MySQL原理 - InnoDB引擎 - 行记录存储 - Redundant行格式

本文基于 MySQL 8 在上一篇:MySQL原理 - InnoDB引擎 - 行记录存储 - Compact格式 中,我们介绍了什么是 InnoDB 行记录存储以及 Compact 行格式,在这一篇中,我们继续介绍其他三种行格式。 ...

zhxhash
52分钟前
17
0
leetcode面试题 17.13(恢复空格)--Java语言实现

求: 哦,不!你不小心把一个长篇文章中的空格、标点都删掉了,并且大写也弄成了小写。像句子"I reset the computer. It still didn’t boot!"已经变成了"iresetthecomputeritstilldidntboo...

拓拔北海
今天
19
0
B站跨年晚会究竟做对了什么?

燃财经(ID:rancaijing)原创 作者 | 赵磊 编辑 | 周昶帆 “补课”是《bilibili晚会 二零一九最美的夜》这个视频中,观众在前两分钟刷得最多的弹幕,寓意着观众是在元旦之后回来补看跨年晚会...

子乾建建_Jeff
01/07
45
0
关于Scrapy爬虫项目运行和调试的小技巧(上篇)

点击上方“Python爬虫与数据挖掘”,进行关注 回复“书籍”即可获赠Python从入门到进阶共10本电子书 今 日 鸡 汤 迟日江山丽,春风花草香。泥融飞燕子,沙暖睡鸳鸯。 扫除运行Scrapy爬虫程序...

yuhan336
04/02
26
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部