SQL Server事务执行过程中中释放锁导致的死锁问题 - 排查与分析

这篇具有很好参考价值的文章主要介绍了SQL Server事务执行过程中中释放锁导致的死锁问题 - 排查与分析。希望对大家有所帮助。如果存在错误或未考虑完全的地方,请大家不吝赐教,您也可以点击"举报违法"按钮提交疑问。

0. 前情提要

系统的某个用来上报数据的接口存在死锁的问题。这个接口内部对多张表进行了Update操作,执行顺序为A表、B表、C表、D表、A表。死锁发生的SQL,一条是第一次更新A表的SQL,另一条是第二次更新A表的SQL。整个更新都处在一个事务内,理论上讲,只要第一个Session开始执行事务,第二个Session就会由于无法获取到A表的锁而被阻塞,直到第一个Session执行完毕释放锁,那为什么对A表的更新还会产生死锁呢?

1. 准备工作

用准备来做测试的表

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Test](
	[Id] [int] NOT NULL,
	[Progress] [int] NOT NULL,
	[Score] [int] NOT NULL,
	[Grade] [int] NOT NULL,
	[Usn] [int] NOT NULL,
	[ProjectId] [int] NOT NULL,
	[IsDeleted] [int] NOT NULL,
 CONSTRAINT [PK_Id] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE UNIQUE NONCLUSTERED INDEX [index_usn_and_project_id] ON [dbo].[Test]
(
	[Usn] ASC,
	[ProjectId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

插入一条测试用的数据

PRINT @@spid
insert into dbo.Test (Id, Progress, Score, Grade, Usn, ProjectId, IsDeleted)
VALUES (1, 1, 1, 1, 1, 1, 1);

2. 非聚簇索引的情况下,在表上执行一次update的加锁过程

用来测试的update语句

BEGIN TRAN

PRINT @@spid
update dbo.Test
set Progress=2
WHERE Usn=1 and ProjectId=1

COMMIT TRAN

利用Sql Server Profiler追踪加锁与释放锁的过程
SQL Server事务执行过程中中释放锁导致的死锁问题 - 排查与分析
从图中我们可以得知,在锁增长阶段,存在以下加锁步骤:

  1. 根据输入条件,找到非聚簇索引所在页,并获取非聚簇索引所在页的IU锁
  2. 获取非聚簇索引的U锁
  3. 根据非聚簇索引的值,找到聚簇索引(即主键索引)所在的页,获取页的IU锁
  4. 获取聚簇索引的U锁
  5. 将聚簇索引所在页的锁提升为IX锁
  6. 将聚簇索引的锁提升为X锁
  7. 更新数据
  8. 释放非聚簇索引所在页的IU锁
  9. 释放非聚簇索引的U锁
  10. 释放聚簇索引的X锁
  11. 释放聚簇索引所在页的IX锁

所有的锁都被争取的申请与释放

2. 非聚簇索引的情况下,在表上执行两个update的加锁

BEGIN TRAN

PRINT @@spid
update dbo.Test
set Progress=2
WHERE Usn=1 and ProjectId=1

update dbo.Test
set Score=2,Grade = 2
WHERE Usn=1 and ProjectId=1

COMMIT TRAN

SQL Server事务执行过程中中释放锁导致的死锁问题 - 排查与分析
从红框中可以看到,非聚簇索引索引在第一个update时申请U锁、释放U锁,并在第二个update时再次申请U锁、释放U锁。

在这里,有意思的事情就发生了:第一个update在聚簇索引上申请的X锁,在第一个update执行完成后,并没有像非聚簇索引一样被释放。

这里猜测一个原因:由于非聚簇索引并不需要更新,它只是用来更新聚簇索引数据的一个跳板,因此在聚簇索引更新完后就被释放了。而聚簇索引上的X锁,则需要在事务commit之后才会被释放。

为了验证这一点,尝试在第一次update时,同时更新ProjectId

BEGIN TRAN

PRINT @@spid
update dbo.Test
set Progress=2,ProjectId=2
WHERE Usn=1 and ProjectId=1

update dbo.Test
set Score=2,Grade = 2
WHERE Usn=1 and ProjectId=2

COMMIT TRAN

SQL Server事务执行过程中中释放锁导致的死锁问题 - 排查与分析
红线上方的就是第一个update, 由于对非聚簇索引进行了修改,所以非聚簇索引页申请了X锁,且没有在第一次Update语句结束后释放锁。由此验证了上面的假设

3. 推测与模拟

由上面的内容,我们知道了一件事情:在Update时,会申请非聚簇索引上的U锁,并且会在Update结束后(而不是事务提交后)释放锁;当在一个事务内存在两个Update(且更新的是同一条数据)时,会在非聚簇索引上存在 获取锁 -> 释放锁 -> 获取锁 -> 释放锁 的过程。

同时,我们也知道,假如同时存在两个Session要Update同一张表的同一条数据,那么晚到的Session由于事务的原因,会等待第一个Session释放非聚簇索引上的锁(因为Update的入口就是获取非聚簇索引的锁)。

那么,我们就能推理出一种情况:Session1执行完了第一个Update,并将自己持有的非聚簇索引的U锁释放,保留着聚簇索引的X锁;Session2看到非聚簇索引的U锁被释放,于是获取了U锁;此时Session1试图再次获取非聚簇索引的U锁,但非聚簇索引的U锁已经被Session2占有。而Session2要进一步更新数据,就需要Session1手里的聚簇索引的X锁。于是,死锁就发生了
SQL Server事务执行过程中中释放锁导致的死锁问题 - 排查与分析

接下来就是验证:左侧的sql先执行,在第一个update后,延迟1分钟;左侧sql开始之后,执行右侧的sql。理论上讲,此时左右会触发死锁:
SQL Server事务执行过程中中释放锁导致的死锁问题 - 排查与分析

最终的结果,成功触发死锁,右侧的sql作为后来者被牺牲:
SQL Server事务执行过程中中释放锁导致的死锁问题 - 排查与分析

本次运行的加锁、释放锁的过程,其中白色部分为左侧sql的执行过程(spid=53),蓝色的部分是右侧的sql的执行过程(spid=57),下面以53和57代替:
SQL Server事务执行过程中中释放锁导致的死锁问题 - 排查与分析

可以看到,53释放了非聚簇索引后,57立刻获取了非聚簇索引上的U锁,并获得了聚簇索引所在页的IU锁,但由于53任然持有聚簇索引的X锁,导致57无法进一步申请聚簇索引的锁:
SQL Server事务执行过程中中释放锁导致的死锁问题 - 排查与分析

而53这时试图获取非聚簇索引所在页上的IU锁(被57持有),由此引发了死锁。经过一段时间后,数据库发现死锁的存在,并主动终结了57,让57持有的资源被释放,从而保证53可以顺利执行

4. 结论

到这里,本次死锁发生的原因其实已经明确了,原因可以归结为,在一个事务内存在对某一资源的重复申请与释放,在并发场景下,就可能会因为对该资源的争夺而产生死锁。在开发的过程中,应当尽量避免在并发情况下,对同一资源的重复申请与释放文章来源地址https://www.toymoban.com/news/detail-434207.html

到了这里,关于SQL Server事务执行过程中中释放锁导致的死锁问题 - 排查与分析的文章就介绍完了。如果您还想了解更多内容,请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章,希望大家以后多多支持TOY模板网!

本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如若转载,请注明出处: 如若内容造成侵权/违法违规/事实不符,请点击违法举报进行投诉反馈,一经查实,立即删除!

领支付宝红包 赞助服务器费用

相关文章

  • Sql Server 占用内存高,不释放内存,数据库连接不上 问题处理记录

    项目上反应生产线箱码不能上传到我们系统,我们的WMS软件退出后重新登录也一直登录失败,并且服务器上数据库占用内存过高,SSMS数据库连接不上。 好家伙SQL Server占用了34个G内存。 接口日志中连接数据库报错:System.Data.SqlClient.SqlException: 当前命令发生了严重错误。应放弃

    2024年02月14日
    浏览(74)
  • SQL Server的事务日志

    每个 SQL Server 数据库都有一个事务日志,用于记录所有事务以及每个事务所做的数据库修改。 事务日志是数据库的关键组件。如果出现系统故障,您将需要该日志才能使数据库恢复到一致状态。 事务日志支持以下操作: 单个事务恢复。 在启动 SQL Server 时恢复所有未完成的事

    2023年04月19日
    浏览(41)
  • SQL Server 事务日志文件过大 解决方案

    事务日志文件(Transaction Log File)是用来记录数据库更新情况的文件,扩展名为ldf。当出现以下情况时,就会报“ 事务日志已满 ”的错误: 日志文件没有设置了自动增长,当记录超过初始大小; 日志文件设置了自动增长,“最大文件大小”设置了具体的大小(如2000M),当

    2024年02月10日
    浏览(57)
  • Sql Server 数据库事务与锁,同一事务更新又查询锁?期望大家来解惑

    我有一个People表,有三行数据: 如果我们没详细了解数据库事务执行加锁的过程中,会不会有这样一个疑问:如下的这段 SQL 开启了事务,并且在事务中进行了更新和查询操作。 我们知道sql server数据库的默认事务级别是READ COMMITTED(已提交的读取),我们再看一下已提交读事

    2024年02月01日
    浏览(54)
  • Mybatis中执行Sql的执行过程

    MyBatis中执行SQL的过程可以分为以下几个步骤: 解析配置文件:在运行时,MyBatis会加载并解析配置文件(通常为 mybatis-config.xml ),获取数据库连接信息、映射文件等。 创建SqlSessionFactory:MyBatis使用SqlSessionFactory来创建SqlSession对象。SqlSessionFactory是一个线程安全的对象,应该

    2024年02月08日
    浏览(39)
  • SQL执行过程

    一条 select 语句的执行过程如上图所示 1、建立连接 连接器会校验你输入的用户名和密码是否正确,如果错误会返回提示,如果正确,连接器会查询当前用户对于的权限。连接器的作用就是校验用户权限 2、查询缓存 MySQL 中有个缓存的概念,当你在执行一条 SQL 查询语句时,

    2024年02月06日
    浏览(37)
  • Sql Server 数据库事务与锁,同一事务更新又查询锁的变化,期望大家来解惑!

    我有一个People表,有三行数据: 如果我们没详细了解数据库事务执行加锁的过程中,会不会有这样一个疑问:如下的这段 SQL 开启了事务,并且在事务中进行了更新和查询操作。 我们知道sql server数据库的默认事务级别是READ COMMITTED(已提交的读取),我们再看一下已提交读事

    2024年02月01日
    浏览(69)
  • DBeaver调出查询管理或事务日志,即sql执行的记录——DBeaver笔记

    问题 : 如何调出查询管理或事务日志,即sql执行的记录 答案 : 可以对日志进行刷新,筛选,清除的等等,个人试过,还是相当的好用

    2024年02月15日
    浏览(63)
  • SQL Server查询历史执行记录

    1. 使用 SQL Server Management Studio (SSMS) 的查询执行记录功能: 打开 SSMS,连接到要查看执行记录的数据库服务器。 在“对象资源管理器”窗格中,右键单击要查看执行记录的数据库,然后选择“报表” “标准报表” “执行记录”。 在“执行记录”窗口中,选择要查看的时间范

    2024年02月17日
    浏览(40)
  • SQL Server 存储过程

    Transact-SQL中的存储过程,非常类似于Java语言中的方法,它可以重复调用。当存储过程执行一次后,可以将语句缓存中,这样下次执行的时候直接使用缓存中的语句。这样就可以提高存储过程的性能。 Ø 存储过程的概念     存储过程Procedure是一组为了完成特定功能的SQL语句集

    2024年02月16日
    浏览(42)

觉得文章有用就打赏一下文章作者

支付宝扫一扫打赏

博客赞助

微信扫一扫打赏

请作者喝杯咖啡吧~博客赞助

支付宝扫一扫领取红包,优惠每天领

二维码1

领取红包

二维码2

领红包