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

本文涉及的产品
RDS SQL Server Serverless,2-4RCU 50GB 3个月
推荐场景:
云数据库 RDS SQL Server,基础系列 2核4GB
简介: SQL Server死锁中的会话隔离级别为序列化(Serializable)实验测试最近在分析SQL Server的死锁时,发现一个比较有意思的现象,发现死锁当中一个会话的隔离级别为序列化(Serializable),这个是让人比较奇怪的地方,我们知道SQL Server数据库的默认隔离级别为已提交读(READ COMMITTED),除非人为设置事务隔离级别(TRANSACTION ISOLATION LEVEL),否则事务隔离级别会使用数据库的默认隔离级别。

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


<victimProcess id="process17676e108" />


<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

  </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

</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 procname="adhoc" line="1" stmtend="186" sqlhandle="0x02000000b0cd40243d43ed1a51b1baa9cbf70d1628eae7880000000000000000000000000000000000000000">

unknown

  </executionStack>
  <inputbuf>

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

</process>


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


我们也可以使用下面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

作者:潇湘隐者
出处:http://www.cnblogs.com/kerrycode/

相关实践学习
使用SQL语句管理索引
本次实验主要介绍如何在RDS-SQLServer数据库中,使用SQL语句管理索引。
SQL Server on Linux入门教程
SQL Server数据库一直只提供Windows下的版本。2016年微软宣布推出可运行在Linux系统下的SQL Server数据库,该版本目前还是早期预览版本。本课程主要介绍SQLServer On Linux的基本知识。 相关的阿里云产品:云数据库RDS&nbsp;SQL Server版 RDS SQL Server不仅拥有高可用架构和任意时间点的数据恢复功能,强力支撑各种企业应用,同时也包含了微软的License费用,减少额外支出。 了解产品详情:&nbsp;https://www.aliyun.com/product/rds/sqlserver
相关文章
|
2月前
|
关系型数据库 MySQL 网络安全
5-10Can't connect to MySQL server on 'sh-cynosl-grp-fcs50xoa.sql.tencentcdb.com' (110)")
5-10Can't connect to MySQL server on 'sh-cynosl-grp-fcs50xoa.sql.tencentcdb.com' (110)")
|
3月前
|
NoSQL Linux Android开发
内核实验(三):编写简单Linux内核模块,使用Qemu加载ko做测试
本文介绍了如何在QEMU中挂载虚拟分区、创建和编译简单的Linux内核模块,并在QEMU虚拟机中加载和测试这些内核模块,包括创建虚拟分区、编写内核模块代码、编译、部署以及在QEMU中的加载和测试过程。
174 0
内核实验(三):编写简单Linux内核模块,使用Qemu加载ko做测试
|
3月前
|
存储 安全 Java
揭秘Java序列化神器Serializable:一键解锁对象穿越时空的超能力,你的数据旅行不再受限,震撼登场!
【8月更文挑战第4天】Serializable是Java中的魔术钥匙,开启对象穿越时空的能力。作为序列化的核心,它让复杂对象的复制与传输变得简单。通过实现此接口,对象能被序列化成字节流,实现本地存储或网络传输,再通过反序列化恢复原状。尽管使用方便,但序列化过程耗时且存在安全风险,需谨慎使用。
48 7
|
3月前
|
SQL 监控 供应链
|
4月前
|
SQL 存储 监控
SQL Server的并行实施如何优化?
【7月更文挑战第23天】SQL Server的并行实施如何优化?
92 13
|
4月前
|
SQL
解锁 SQL Server 2022的时间序列数据功能
【7月更文挑战第14天】要解锁SQL Server 2022的时间序列数据功能,可使用`generate_series`函数生成整数序列,例如:`SELECT value FROM generate_series(1, 10)。此外,`date_bucket`函数能按指定间隔(如周)对日期时间值分组,这些工具结合窗口函数和其他时间日期函数,能高效处理和分析时间序列数据。更多信息请参考官方文档和技术资料。
|
4月前
|
SQL 存储 网络安全
关系数据库SQLserver 安装 SQL Server
【7月更文挑战第26天】
58 6
|
3月前
|
Linux C#
【Azure App Service】C#下制作的网站,所有网页本地测试运行无误,发布至Azure之后,包含CHART(图表)的网页打开报错,错误消息为 Runtime Error: Server Error in '/' Application
【Azure App Service】C#下制作的网站,所有网页本地测试运行无误,发布至Azure之后,包含CHART(图表)的网页打开报错,错误消息为 Runtime Error: Server Error in '/' Application
|
4月前
|
存储 SQL C++
对比 SQL Server中的VARCHAR(max) 与VARCHAR(n) 数据类型
【7月更文挑战7天】SQL Server 中的 VARCHAR(max) vs VARCHAR(n): - VARCHAR(n) 存储最多 n 个字符(1-8000),适合短文本。 - VARCHAR(max) 可存储约 21 亿个字符,适合大量文本。 - VARCHAR(n) 在处理小数据时性能更好,空间固定。 - VARCHAR(max) 对于大文本更合适,但可能影响性能。 - 选择取决于数据长度预期和业务需求。
278 1
|
3月前
|
SQL 安全 Java
驱动程序无法通过使用安全套接字层(SSL)加密与 SQL Server 建立安全连接。错误:“The server selected protocol version TLS10 is not accepted by client
驱动程序无法通过使用安全套接字层(SSL)加密与 SQL Server 建立安全连接。错误:“The server selected protocol version TLS10 is not accepted by client
403 0