SQLSERVER 语句交错引发的死锁问题案例详解

2024-08-08 09:26:14

一：背景

1. 讲故事

相信大家在使用 SQLSERVER 的过程中经常会遇到 阻塞 和 死锁，尤其是 死锁，比如下面的输出：

(1 row affected) Msg 1205, Level 13, State 51, Line 5 Transaction (Process ID 62) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

要解决死锁问题，个人感觉需要非常熟知各种隔离级别，尤其是 可提交读 模式下的 CURD 加解锁过程，这一篇我们就来好好聊一聊。

二：死锁简析

1. 一个测试案例

开启两个会话 65 和 66 ，分别使用如下查询。

-- 会话 65 --
BEGIN TRAN
UPDATE dbo.Employees SET Title='Dr.' WHERE EmployeeID=1;
WAITFOR DELAY '00:00:10'
SELECT * FROM dbo.Orders WHERE OrderID=10258
ROLLBACK

-- 会话 66 --
BEGIN TRAN
UPDATE  dbo.Orders SET  ShipAddress='上海' WHERE OrderID=10258
WAITFOR DELAY '00:00:10'
SELECT * FROM dbo.Employees WHERE EmployeeID=1;
ROLLBACK

两个会话非常简单，交错的对 Employees 和 Orders 进行 SELECT 和 UPDATE 操作，稍等几秒后就会出现死锁。

2. 寻找死锁源头

当我们的应用程序拿到了这样的输出其实作用是不大的，要想溯源最好就是通过不断的对 SQLSERVER 进行监视来捕获死锁时的上下文信息，手段也有很多：

SQL Server Profile
DBCC TRACEON(1222)
DMV VIEW

这里我们就用第一种方式，一定要勾选 TextData 项，因为这里面会有死锁上下文信息的xml表示，截图如下：

将 profile 开启后，重新执行刚才的两个查询，一旦出现死锁，profile 就会成功捕获，然后 copy 出 TextData 项，截图如下：

<deadlock-list>
 <deadlock victim="process2d69c9748c8">
  <process-list>
   <process id="process2d69c9748c8" taskpriority="0" logused="324" waitresource="KEY: 7:72057594043170816 (8194443284a0)" waittime="1304" ownerId="70740" transactionname="user_transaction" lasttranstarted="2023-02-19T22:11:26.413" XDES="0x2d6a0200428" lockMode="S" schedulerid="5" kpid="13816" status="suspended" spid="66" sbid="0" ecid="0" priority="0" trancount="1" lastbatchstarted="2023-02-19T22:11:26.413" lastbatchcompleted="2023-02-19T22:11:26.410" lastattention="1900-01-01T00:00:00.410" clientapp="Microsoft SQL Server Management Studio - Query" hostname="DESKTOP-STS8TPB" hostpid="1696" loginname="DESKTOP-STS8TPB\Administrator" isolationlevel="read committed (2)" xactid="70740" currentdb="7" currentdbname="Northwind" lockTimeout="4294967295" clientoption1="671090784" clientoption2="390200">
    <executionStack>
     <frame procname="adhoc" line="5" stmtstart="24" stmtend="128" sqlhandle="0x020000007383d935b349bc173c0f104de14945e9a526322b0000000000000000000000000000000000000000">
unknown     </frame>
     <frame procname="adhoc" line="5" stmtstart="204" stmtend="294" sqlhandle="0x020000002c3b203105961d63d10b17e54ed6ac081105f9450000000000000000000000000000000000000000">
unknown     </frame>
    </executionStack>
    <inputbuf>

BEGIN TRAN
UPDATE  dbo.Orders SET  ShipAddress=&apos;上海&apos; WHERE OrderID=10258
WAITFOR DELAY &apos;00:00:10&apos;
SELECT * FROM dbo.Employees WHERE EmployeeID=1;
ROLLBACK
    </inputbuf>
   </process>
   <process id="process2d6ae694ca8" taskpriority="0" logused="368" waitresource="KEY: 7:72057594044088320 (59ce0997f9b8)" waittime="3468" ownerId="70716" transactionname="user_transaction" lasttranstarted="2023-02-19T22:11:24.247" XDES="0x2d6a7284428" lockMode="S" schedulerid="9" kpid="7124" status="suspended" spid="65" sbid="0" ecid="0" priority="0" trancount="1" lastbatchstarted="2023-02-19T22:11:24.247" lastbatchcompleted="2023-02-19T22:11:24.247" lastattention="1900-01-01T00:00:00.247" clientapp="Microsoft SQL Server Management Studio - Query" hostname="DESKTOP-STS8TPB" hostpid="1696" loginname="DESKTOP-STS8TPB\Administrator" isolationlevel="read committed (2)" xactid="70716" currentdb="7" currentdbname="Northwind" lockTimeout="4294967295" clientoption1="671090784" clientoption2="390200">
    <executionStack>
     <frame procname="adhoc" line="5" stmtstart="26" stmtend="118" sqlhandle="0x02000000dd7720067e0519b8a368501716c04b4b50cfe6be0000000000000000000000000000000000000000">
unknown     </frame>
     <frame procname="adhoc" line="5" stmtstart="196" stmtend="282" sqlhandle="0x0200000093f01512208755a056f5f28930fbd3dedf58a2850000000000000000000000000000000000000000">
unknown     </frame>
    </executionStack>
    <inputbuf>

BEGIN TRAN
UPDATE dbo.Employees SET Title=&apos;Dr.&apos; WHERE EmployeeID=1;
WAITFOR DELAY &apos;00:00:10&apos;
SELECT * FROM dbo.Orders WHERE OrderID=10258
ROLLBACK
    </inputbuf>
   </process>
  </process-list>
  <resource-list>
   <keylock hobtid="72057594043170816" dbid="7" objectname="Northwind.dbo.Employees" indexname="PK_Employees" id="lock2d69ccbbb80" mode="X" associatedObjectId="72057594043170816">
    <owner-list>
     <owner id="process2d6ae694ca8" mode="X"/>
    </owner-list>
    <waiter-list>
     <waiter id="process2d69c9748c8" mode="S" requestType="wait"/>
    </waiter-list>
   </keylock>
   <keylock hobtid="72057594044088320" dbid="7" objectname="Northwind.dbo.Orders" indexname="PK_Orders" id="lock2d69ccbbf80" mode="X" associatedObjectId="72057594044088320">
    <owner-list>
     <owner id="process2d69c9748c8" mode="X"/>
    </owner-list>
    <waiter-list>
     <waiter id="process2d6ae694ca8" mode="S" requestType="wait"/>
    </waiter-list>
   </keylock>
  </resource-list>
 </deadlock>
</deadlock-list>

虽然上面有图形化表示，但在生产环境下参考价值并不多，因为这张图蕴含的信息比较少，熟读和整理 xml 的内容就非常必要了，截图如下：

仔细观察上面的这张图可以清晰的看到，spid=66 持有了 Orders.PK_Orders 索引上哈希码为 59ce0997f9b8 键值的 X 锁，之后需要再次获取 Employees.PK_Employees 索引上哈希码为 8194443284a0 键值上的 S 锁，很不巧的是，此时的 Employees.PK_Employees 索引上哈希码为 8194443284a0 的键值已经被 spid=65 的会话附加了 X 锁，这是一种典型的相互等待造成的死锁。

同时也可以观察到，我们的语句是一个 adhoc 即时查询，其外层也没有 存储过程 之类的包围语句。

3. 寻找解决方案

知道了是什么语句和什么语句之间的冲突之后，后面的问题就比较简单了，常见措施如下：

使用 nolock 脏读

由于冲突中涉及到了 S 锁，其实绝大多数系统对脏读不是特别敏感，所以使用 nolock 无锁提示是一个好办法。

BEGIN TRAN
UPDATE  dbo.Orders SET  ShipAddress='上海' WHERE OrderID=10258
WAITFOR DELAY '00:00:10'
SELECT * FROM dbo.Employees WITH(NOLOCK) WHERE EmployeeID=1;
ROLLBACK

BEGIN TRAN
UPDATE dbo.Employees SET Title='Dr.' WHERE EmployeeID=1;
WAITFOR DELAY '00:00:10'
SELECT * FROM dbo.Orders WITH(NOLOCK) WHERE OrderID=10258
ROLLBACK

使用 MVCC 多版本控制

现代化的关系型数据库都支持 快照读 来解决 并发读写 的冲突，同时又能保证不脏读，简而言之就是在事务修改时将修改前的数据存到 tempdb 中来形成字段的版本化。

首先需要从 数据库 级别开启它。

ALTER DATABASE Northwind SET ALLOW_SNAPSHOT_ISOLATION ON

然后在各自事务中显式使用 SNAPSHOT 隔离级别查询，参考sql如下：

-- 会话 65 --
SET TRAN ISOLATION LEVEL SNAPSHOT
BEGIN TRAN
UPDATE dbo.Employees SET Title='Dr.' WHERE EmployeeID=1;
WAITFOR DELAY '00:00:10'
SELECT * FROM dbo.Orders WHERE OrderID=10258
ROLLBACK

-- 会话 66 --
SET TRAN ISOLATION LEVEL SNAPSHOT
BEGIN TRAN
UPDATE  dbo.Orders SET  ShipAddress='上海' WHERE OrderID=10258
WAITFOR DELAY '00:00:10'
SELECT * FROM dbo.Employees  WHERE EmployeeID=1;
ROLLBACK

三：总结

在真实的死锁案例集锦中，相对来说 语句顺序交错 引发的死锁会相对多一些，其次就是 书签查找，这个放到后面的文章中来聊，面对 语句顺序交错 的场景尽量的收集整理死锁的 xml数据，或许有很多意想不到的发现。

到此这篇关于SQLSERVER 语句交错引发的死锁研究的文章就介绍到这了,更多相关sqlserver语句交错引发的死锁内容请搜索我们以前的文章或继续浏览下面的相关文章希望大家以后多多支持我们！

SqlServer查询和Kill进程死锁的语句

查询死锁进程语句 select request_session_id spid, OBJECT_NAME(resource_associated_entity_id) tableName from sys.dm_tran_locks where resource_type='OBJECT' 杀死死锁进程语句 kill spid 下面再给大家分享一段关于sqlserver检测死锁;杀死锁和进程;查看锁信息 --检测死锁 --如果发生死锁了,我们怎么去检测具体发生死锁的是哪条SQL语句或存储过程?
SQLServer 中的死锁说明

两个进程发生死锁的典型例子是:进程T1中获取锁A,申请锁B:进程T2中获取锁B,申请锁A,我们下面动手来演示一下这种情况: 1. 创建一个Database,名为InvDB. 2. 执行下面脚本创建person表并填充两条数据: 3. 在SQL Server Management Studio的两个窗口中同时执行下面的查询: 这段代码在默认的READ COMMITTED隔离级别下运行,两个进程分别在获取一个排它锁的情况下,申请对方的共享锁从而造成死锁. 可见一个进程可以正常更新并显示结果,而另一个
查找sqlserver查询死锁源头的方法 sqlserver死锁监控

查找出SQLServer的死锁和阻塞的源头 --查找出SQLServer死锁和阻塞的源头复制代码代码如下: use mastergodeclare @spid int,@bl intDECLARE s_cur CURSOR FORselect 0 ,blockedfrom (select * from sysprocesses where blocked>0 ) awhere not exists(select * from (select * from sysprocesses whe
查询Sqlserver数据库死锁的一个存储过程分享

使用sqlserver作为数据库的应用系统,都避免不了有时候会产生死锁, 死锁出现以后,维护人员或者开发人员大多只会通过sp_who来查找死锁的进程,然后用sp_kill杀掉.利用sp_who_lock这个存储过程,可以很方便的知道哪个进程出现了死锁,出现死锁的问题在哪里. 创建sp_who_lock存储过程 CREATE procedure sp_who_lock as begin declare @spid int declare @blk int declare @count int de
利用sys.sysprocesses检查SqlServer的阻塞和死锁

MSDN:包含正在 SQL Server 实例上运行的进程的相关信息.这些进程可以是客户端进程或系统进程. 视图中主要的字段: 1. Spid:Sql Servr 会话ID 2. Kpid:Windows 线程ID 3. Blocked:正在阻塞求情的会话 ID.如果此列为 Null,则标识请求未被阻塞 4. Waittype:当前连接的等待资源编号,标示是否等待资源,0 或 Null表示不需要等待任何资源 5. Waittime:当前等待时间,单位为毫秒,0 表示没有等待 6. DBID:当前
sqlserver进程死锁关闭的方法

1.首先我们需要判断是哪个用户锁住了哪张表. --查询被锁表 select request_session_id spid,OBJECT_NAME(resource_associated_entity_id) tableName from sys.dm_tran_locks where resource_type='OBJECT' 查询后会返回一个包含spid和tableName列的表. 其中spid是进程名,tableName是表名. 2.了解到了究竟是哪个进程锁了哪张表后,需要通过进程找到锁
SqlServer表死锁的解决方法分享

其实不光是上面描述的情况会锁住表,还有很多种场景会使表放生死锁,解锁其实很简单,下面用一个示例来讲解: 1 首先创建一个测试用的表: 复制代码代码如下: CREATE TABLE Test ( TID INT IDENTITY(1,1) ) 2 执行下面的SQL语句将此表锁住: 复制代码代码如下: SELECT * FROM Test WITH (TABLOCKX) 3 通过下面的语句可以查看当前库中有哪些表是发生死锁的: 复制代码代码如下: SELECT request_session_
SQLSERVER 语句交错引发的死锁问题案例详解

目录一:背景 1. 讲故事二:死锁简析 1. 一个测试案例 2. 寻找死锁源头 3. 寻找解决方案三:总结一:背景 1. 讲故事相信大家在使用 SQLSERVER 的过程中经常会遇到阻塞和死锁,尤其是死锁,比如下面的输出: (1 row affected) Msg 1205, Level 13, State 51, Line 5 Transaction (Process ID 62) was deadlocked on lock resources with another p
SQLSERVER 拼接含有变量字符串案例详解

一.拼接字符串(整个字符串不分割)步骤: 首先在字符串的前后加单引号: 字符串中的变量以'''+@para+'''在字符串中表示: 若在执行时存在类型转换错误,则应用相应的类型转换函数,对变量进行类型转换(如cast()函数). 示例一: 包含SQL拼接字符串的存储过程: Create Procedure Test @TestID int As Declare @s nvarchar(800) Set @s='Select * From dbo.Categories where Category
SQLServer清理日志文件方法案例详解

很多时候SQLSERVER的日志文件是不看的,但时间久了,够把磁盘撑爆,这时候就需要清理日志文件.使用以下方法,在实际环境中经过测试,400G的日志文件1秒就被清理. 操作步骤 1. 将恢复模式改成"简单" 右键数据库 - 属性,切换到选项,将恢复模式修改为简单. 2. 收缩日志右键数据库 - 任务 - 收缩 - 文件确定后会发现,日志文件被迅速清理. 3. 命令操作 USE [master] GO ALTER DATABASE 要清理的数据库名称 SET RECOVERY SIM
SQLServer日期函数总结案例详解

目录一,日期的格式化二,日期和时间的结构三,日期操作四. 日期函数 SQL Server发展至今,关于日期的格式的控制方法,有传统的方法,比如CONVERT(),也有比较便利的新方法,比如FORMAT():同样,关于日期的操作函数,也分为传统方法:DATEADD()等,也有便利的新方法:EOMonth()等. 一,日期的格式化格式化是指把日期类型(Date).日期和时间类型转化为字符类型,通常使用CONVERT()和FORMAT()函数. 1,传统的CONVERT() SQL Serv
SQL Server之SELECT INTO 和 INSERT INTO SELECT案例详解

做数据库开发的过程中难免会遇到有表数据备份的,而SELECT INTO--和INSERT INTO SELECT-- 这两种语句就是用来进行表数据复制,下面简单的介绍下: 1.INSERT INTO SELECT 语句格式:Insert Into Table2(column1,column2--) Select value1,value2,value3,value4 From Table1 或 Insert Into Table2 Select * From Table1 说明:这种方式的表复制
java之assert关键字用法案例详解

Java2在1.4中新增了一个关键字:assert.在程序开发过程中使用它创建一个断言(assertion).,它的语法形式有如下所示的两种形式: 1.assert condition; 这里condition是一个必须为真(true)的表达式.如果表达式的结果为true,那么断言为真,并且无任何行动如果表达式为false,则断言失败,则会抛出一个AssertionError对象.这个AssertionError继承于Error对象, 而Error继承于Throwable,Error是和Exc
java 异常捕获及处理案例详解

目录一.Java异常简介二.Java异常的分类三.异常的使用及执行流程四.自定义异常一.Java异常简介什么是异常? 程序运行时,发生的不被期望的事件,它阻止了程序按照程序员的预期正常执行,这就是异常.异常发生时,是任程序自生自灭,立刻退出终止.在Java中即,Java在编译或运行或者运行过程中出现的错误. Java提供了更加优秀的解决办法:异常处理机制. 异常处理机制能让程序在异常发生时,按照代码的预先设定的异常处理逻辑,针对性地处理异常,让程序尽最大可能恢复正常并继续执行,且保持
SQLserver中cube：多维数据集实例详解

1.cube:生成多维数据集,包含各维度可能组合的交叉表格,使用with 关键字连接 with cube 根据需要使用union all 拼接判断某一列的null值来自源数据还是 cube 使用GROUPING关键字 GROUPING([档案号]) = 1 : null值来自cube(代表所有的档案号) GROUPING([档案号]) = 0 : null值来自源数据举例: SELECT * INTO ##GET FROM (SELECT * FROM ( SELECT CASE WHEN
MongoDB模糊查询操作案例详解(类关系型数据库的 like 和 not like)

1.作用与语法描述作用: 正则表达式是使用指定字符串来描述.匹配一系列符合某个句法规则的字符串.许多程序设计语言都支持利用正则表达式进行字符串操作.MongoDB 使用 $regex 操作符来设置匹配字符串的正则表达式. 语法一 { <field>: { $regex: /pattern/, $options: '<options>' } } { <field>: { $regex: 'pattern', $options: '<options>' } }
Java基础之枚举Enum类案例详解

一.文章序言 Java中引用类型:数组.类.接口.枚举.注解枚举这个既熟悉又陌生的东西具体再哪里可以使用呢? 什么是枚举? 枚举是一个引用类型,枚举就是一个规定了取值范围的变量类型. 枚举变量不能使用其他的数据,只能使用枚举中常量赋值.提高程序安全性: //格式: public enum 枚举名{ //枚举的取值范围 //枚举中可以生命方法 } 枚举的使用场景介绍? 1.最常见的情况如星期,相关变量我们会在Java里面重复使用,在这里我们就可以来定义一个叫做"星期"的枚举. publ