SQL Server 性能排查（5）- 锁排查

Java进阶八股文

于 2024-12-22 17:14:52 发布

阅读量1.1k

点赞数 9

文章标签： oracle 数据库

本文链接：https://blog.csdn.net/2401_89221445/article/details/144649119

版权

在锁阻塞问题的排查中，我们已经铺垫了相关的基础知识。接下来，我们将进入实战环节。通常情况下，如果已经推测出是锁阻塞导致的问题，可以通过以下两种方式快速入手解决：

查询会话状态：利用系统视图获取当前会话的详细信息，例如哪些会话正在等待资源。
检查锁阻塞相关的等待事件视图：分析等待事件的类型及其关联的资源，进一步定位阻塞源。

事实上，网络上有大量现成的SQL脚本可以直接用于排查问题。然而，我们的目标并不仅限于执行某段SQL脚本后发现问题并简单地KILL掉阻塞进程，而是研究如何一步步深入排查锁相关的问题，从根源解决问题并优化系统性能。

1. 概述

关于锁阻塞，一般关心如下三个问题：

SQL Server里有没有阻塞发生？是什么时候开始的？在哪个数据库上？阻塞发生在哪个或哪些表格上？哪些资源上？
和阻塞有关的连接是从哪些客户端应用发来的？
为什么会发生阻塞？

在做问题初步定位的时候，必须搞清楚以下问题：

是哪些应用出了问题？这些应用是在使用同一个数据库还是不同的数据库
应用端问题的现象是什么
什么因素让你认为阻塞是问题的主要原因
阻塞发生的特征。

如果能够确认阻塞和当前的问题相关，就继续下一步排查：

SQL Server里有没有阻塞发生？是什么时候发生的？在哪个数据库上？阻塞发生在哪个或哪些表格上？哪些资源上？
和阻塞有关的连接是从哪些客户应用来的？
为什么阻塞会发生？这个问题包括：

1. 现在阻塞发生在哪个或哪些资源上？
2. 阻塞的源头是在做什么事情的时候申请了这些锁？为什么会申请这些锁？
3. 阻塞的源头当前的状态是什么？是一直在执行，还是已经进入空闲状态？
4. 如果它一直在执行，为什么要执行这么久？
5. 如果已经进入空闲状态，那为什么没有释放锁资源？
6. 其他被阻塞的连接它们想要做什么？为什么也要申请这些锁资源？

2. 锁阻塞定位

连接1：

ini

代码解读

复制代码

BEGIN TRAN UPDATE [HumanResources].[Employee] SET jobTitle = 'aaa' WHERE BusinessEntityID = 70

连接2：

css

代码解读

复制代码

SELECT BusinessEntityID, LoginID, JobTitle FROM [HumanResources].[Employee] WHERE BusinessEntityID IN (70);

连接3：

sql

代码解读

复制代码

SELECT * FROM master.sys.sysprocesses where blocked<>0 ;

sysprocesses是比较重要的系统视图，重要的字段介绍如下图所示：

sysprocesses比较重要的字段

我们开始一一回答上面的问题：

是否有阻塞发生？哪些连接发生了阻塞？谁阻塞了谁？

检查方法

查询 blocked 字段

- 值为 0：表示未被阻塞。
- 值为 -2、-3、-4：表示特殊情况，通常无需关注。
- 值为其他 SPID：表示被该 SPID 阻塞。

判断阻塞链

- 阻塞链的源头通常 blocked 字段为 NULL。
- 若某连接的 blocked 字段值等于自身 SPID，表示等待自身 I/O 完成，无需处理。

什么时候开始的阻塞？

检查方法

通过查看waittime字段判断阻塞持续时间：

- waittime表示当前连接被阻塞的时长（单位：毫秒）。
- 如果多次查询发现waittime始终较短，说明单个阻塞持续时间不长，问题可能不严重（但仍会影响响应速度）。
- 如果waittime较长，说明阻塞持续时间较久，特别是在生产环境中，这种阻塞可能带来严重影响。

关键点

- 短时阻塞：通常问题较轻微，但需评估影响范围。
- 长时阻塞：需尽快排查并解决阻塞原因。

在哪个数据库上

一般检查dbid即可。得到dbid以后，可以运行以下查询得到数据库的名字。

sql

代码解读

复制代码

SELECT name, dbid FROM master.sys.sysdatabases

4. 阻塞在哪个或哪些表格上？哪些资源上

可以运行sp_lock，在结果集中寻找状态是wait的锁资源。或者直接运行下面的查询，得到一样的效果。

vbnet

代码解读

复制代码

SELECT CONVERT(smallint, req_spid) AS spid, rsc_dbid AS dbid, rsc_objid AS ObjId, rsc_indid AS IndId, SUBSTRING(v.name, 1, 4) AS Type, SUBSTRING(rsc_text, 1, 32) AS Resource, SUBSTRING(u.name, 1, 8) AS Mode, SUBSTRING(x.name, 1, 5) AS Status FROM master.dbo.syslockinfo, master.dbo.spt_values v, master.dbo.spt_values x, master.dbo.spt_values u WHERE master.dbo.syslockinfo.rsc_type = v.number AND v.type = 'LR' AND master.dbo.syslockinfo.req_status = x.number AND x.type = 'LS' AND master.dbo.syslockinfo.req_mode + 1 = u.number AND u.type = 'L' AND SUBSTRING(x.name, 1, 5) = 'WAIT' ORDER BY spid

运行SELECT OBJECT_NAME(＜objid＞)得到表格的名字，查询sys.indexes得到索引的名字。

sql

代码解读

复制代码

USE AdventureWorks2012 GO SELECT OBJECT_NAME(1237579447) GO SELECT * FROM sys.indexes WHERE object_id = 1237579447 GO

执行结果如下

锁类型Type解释：

RID：格式为fileid:pagenumber:rid的标识符，其中fileid标识包含页的文件，pagenumber标识包含行的页， rid标识页上的特定行。fileid与sys.database_files目录视图中的file_id列相匹配。

例如，例子中有个正处于WAIT状态的RID Resource是1:6681:26。它的意思是在第一个数据文件上的第6681页上的第26个行上的锁资源。如果要查看这个页面上到底有哪些数据，可以考虑使用DBCC PAGE

KEY：数据库引擎内部使用的十六进制数。这个值和sys.partitions.hobt_id相对应。出现这种资源说明锁是在一个索引上面的。通过查询sys.partitions视图里相应的object_id和index_id就能找到这个索引。
PAG：格式为fileid:pagenumber的数字，其中fileid标识包含页的文件，pagenumber标识页。
EXT：标识区中的第一页的数字。该数字的格式为fileid:pagenumber。
TAB：没有提供信息，因为已在ObjId列中标识了表。
DB：没有提供信息，因为已在dbid列中标识了数据库。
FIL：文件的标识符，与sys.database_files目录视图中的file_id列相匹配。

和阻塞有关的连接是从哪些应用来的

sysprocesses的结果里提供了很多信息告诉用户一个连接是从什么客户端连接过来的

hostname：建立连接的那台机器叫什么名字。
program_name：建立的程序叫什么名字。这里是SQL Server的Management Studio。
hostprocess：运行程序的进程在它的机器上ID是多少。program_name是应用告诉SQL Server的名字。有些应用如果自己没有特别的名字，那很可能会是.Net SqlClient Data Provider之类的通用的名字。需要到任务理器里通过进程ID找到应用程序到底是哪一个。
loginame、nt_domain、nt_username：从这些字段可以知道连接是用什么账号连入SQL Server的。
net_address：客户端机器的MAC地址。有时候应用程序没有告诉SQL Server自己的信息，hostname、program_name之类的字段会是空的。这时候只能通过MAC地址来找是哪台客户机连上来了。
net_library：连接是使用哪个网络协议连上来的。一般为TCP、Named Pipe和LPC（Local Procedure Call）。

为什么阻塞会发生

问题分析

阻塞的发生涉及以下几个方面：

1. 阻塞源头正在做什么？为什么申请了这些锁？

- 检查方法：

- - 锁可能由会话运行的语句申请，也可能是会话先前开启的事务申请的。
  - 如果是前者，可抓取连接的最后一句语句进行分析。
  - 如果是后者，需要在阻塞发生前启用 SQL Trace，跟踪事务开启和锁申请的来源。

- 解决方法：

- - 可运行 DBCC INPUTBUFFER(<spid>) 查看连接发送的最后一个批处理语句。
  - 若发现锁与当前语句无关，则可能是由先前未提交的事务导致。

1. 阻塞源头的状态是什么？

- 检查方法：

- - 查看 sysprocesses 中的 kpid 和 waittype 字段：

- - - 两者均为 0：连接处于空闲状态。
    - 不为 0：连接正在运行或因资源等待挂起。

- 状态说明：

- - kpid ≠ 0 且 waittype = 0：状态为 runnable 或 running，说明连接正在执行任务。
  - kpid ≠ 0 且 waittype ≠ 0：状态为 suspended，说明连接在等待资源。

1. 连接长时间执行的原因

- 检查方法：

- - 查看 kpid 和 waittype 的值：

- - - kpid ≠ 0， waittype = 0：说明任务在执行过程中没有资源阻塞。
    - kpid ≠ 0， waittype ≠ 0：说明连接需要等待资源才能继续。

- 状态说明：

- - 长时间执行可能是由于复杂语句、缺乏索引或高并发引发的资源争用。

1. 空闲状态未释放锁资源的原因

- 检查方法：

- - 查看 sysprocesses 中的 open_tran 字段：

- - - 若值大于 0，说明连接有未提交的事务。

- 解决方法：

- - 运行 DBCC INPUTBUFFER 检查最后发送的语句，确定是否需要提交或回滚事务。

1. 被阻塞的连接在做什么？为什么申请这些锁？

- 检查方法：

- - 使用脚本查看被阻塞连接的当前语句。
  - 比对 sp_lock 结果，评估申请的锁数量是否合理。

- 解决方法：

- - 若锁申请不合理，可通过优化语句或添加合适的索引解决。

正在运行中的语句

css

代码解读

复制代码

SELECT p.session_id, p.request_id, p.start_time, p.status, p.command, p.blocking_session_id, p.wait_type, p.Wait_time, p.wait_resource, p.total_elapsed_time, p.open_transaction_count, p.transaction_isolation_level, SUBSTRING(qt.text, p.statement_start_offset / 2, (CASE WHEN p.statement_end_offset = -1 THEN LEN(CONVERT(nvarchar(MAX), qt.text)) * 2 ELSE p.statement_end_offset END - p.statement_start_offset) / 2) AS "SQL statement", p.statement_start_offset, p.statement_end_offset, batch=qt.text FROM master.sys.dm_exec_requests p CROSS APPLY sys.dm_exec_sql_text(p.sql_handle) AS qt WHERE p.session_id > 50

3. 如何捕捉不定时出现的阻塞

可以用下面的脚本来主动监测系统的阻塞信息。脚本每隔10 s执行一次。执行的语句简单，不会影响系统的性能。只要有足够的磁盘空间，就可以一直让这个脚本执行下去，直到问题再次发生为止。管理员可以每隔几天关闭它并重新运行，使得日志输出文件不要太大。

sql

代码解读

复制代码

use master go while 1 =1 begin print 'Start time: ' + convert(varchar(26), getdate(), 121) Print 'Running processes' select spid, blocked, waittype, waittime, lastwaittype, waitresource, dbid, uid, cpu, physical_io, memusage, login_time, last_batch, open_tran, status, hostname, program_name, cmd, net_library, loginame from sysprocesses --where (kpid ＜＞ 0 ) or (spid ＜ 51) -- Change it if you only want to see the working processes print '***********lockinfor**********' select convert (smallint, req_spid) As spid, rsc_dbid As dbid, rsc_objid As ObjId, rsc_indid As IndId, substring (v.name, 1, 4) As Type, substring (rsc_text, 1, 16) as Resource, substring (u.name, 1, 8) As Mode, substring (x.name, 1, 5) As Status from master.dbo.syslockinfo, master.dbo.spt_values v, master.dbo.spt_values x, master.dbo.spt_values u where master.dbo.syslockinfo.rsc_type = v.number and v.type = 'LR' and master.dbo.syslockinfo.req_status = x.number and x.type = 'LS' and master.dbo.syslockinfo.req_mode + 1 = u.number and u.type = 'L' order by spid print 'inputbuffer for running processes' declare @spid varchar(6) declare ibuffer cursor fast_forward for select cast (spid as varchar(6)) as spid from sysprocesses where spid >50 open ibuffer fetch next from ibuffer into @spid while (@@fetch_status != -1) begin print '' print 'DBCC INPUTBUFFER FOR SPID ' + @spid exec ('dbcc inputbuffer (' + @spid + ')') fetch next from ibuffer into @spid end deallocate ibuffer waitfor delay '0:0:10' End

由于输出会比较大，请不要直接在Management Studio里运行。比较严谨的运行方法如下：把上面的脚本存为一个文件如blocking.sql，然后在客户端或者服务器端执行。-E表明我们用当前Windows登录账户去连接数据库，-S后面是数据库服务名，-i后面是我们的脚本，-w指明我们的输出最宽为2000个字符，-o后面加的是日志名。如果要停止运行，我们按Ctrl+C组合键就可以了。阻塞的信息会存放在当前目录里log.out这个文件中。

4. 常见阻塞原因与解决方案

通过 sysprocesses 表中字段值的组合，可以分析阻塞源头并将阻塞归类为五种常见类型，如下所述：

字段来源

- waittype：表示连接当前的等待类型。
- open_tran：表示连接当前未提交的事务数量。
- status：表示连接的当前状态，如 runnable、sleeping 等。

阻塞分类
根据 waittype、open_tran 和 status 的值组合，可将阻塞分为以下类型：

1. 运行中但等待资源的阻塞。
2. 休眠状态的阻塞。
3. 长时间运行且客户端未及时读取结果的阻塞。
4. 回滚中的阻塞。
5. 死锁导致的阻塞。

“自我修复？” 列含义
该列表示阻塞是否能自动消失：

- 如果连接运行完成、资源释放后阻塞自动消失，则为“是”。
- 如果需人工干预（如执行 KILL 命令或客户端主动断开连接），则为“不能”。

相关说明
通过这些信息，可以快速定位阻塞的源头，并根据类型采取相应措施，如分析是否需要优化语句、添加索引或调整事务管理策略等。

5种常见的阻塞类型

类型	waittype	open_tran	status	自我修复？	原因/其他特征
1	不为 0	≥ 0	runnable	是的，当语句执行结束后修复	语句运行时间较长，可能在等待某些系统资源（如硬盘读写、CPU 或内存等）。
2	0x0000	> 0	sleeping	不能，但可运行 KILL 修复	客户端可能遇到超时或主动取消执行。在 SQL Trace 中可看到 Attention 事件。
3	0x0800/0x0063	≥ 0	runnable	不能，需客户端主动断开或运行 KILL	客户端未及时取走所有结果，可能 open_tran = 0，事务隔离级别默认为 READ COMMITTED，但连接仍持有锁资源。
4	0x0000	> 0	rollback	是的	在 SQL Trace 中可看到 SPID 发起 Attention 事件，表明客户端因超时或主动回滚事务。
5	各种值都有可能	> 0	runnable	不能，需客户端取消语句或断开	可能是应用程序运行中产生死锁。`sysprocesses`里阻塞和被阻塞连接的 `hostname`值通常相同，可运行 KILL 修复，但可能耗时较长（30 秒）。

类型 1：语句运行时间过长导致的阻塞

问题描述
这类阻塞的原因是语句本身在正常运行，但由于需要等待某些系统资源（如内存、硬盘读写、CPU 等），导致执行时间过长。

解决方法
数据库管理员需要与数据库应用设计人员协作，重点从以下几个方面解决：

语句优化

- 检查语句本身是否可以优化，具体包括：

- - 修改语句逻辑以降低复杂度。
  - 修改表格设计以简化查询操作。
  - 调整或增加索引，提高查询性能。

资源瓶颈分析

- 检查 SQL Server 的整体性能，判断是否存在资源瓶颈，主要包括：

- - 内存不足。
  - 磁盘读写延迟。
  - CPU 使用率过高。

- 参考性能调优相关章节，使用系统自带的工具（如性能监视器、动态管理视图 DMVs）分析和定位资源瓶颈问题，并采取针对性措施解决。

隔离复杂应用

- 如果语句本身复杂且不可优化（例如用于生成报表的数据仓库查询），应考虑隔离这类应用：

- - 将数据仓库应用从 OLTP 系统中独立出来，减少对事务处理的影响。

备注
在解决这一类阻塞问题时，需要综合考虑 SQL Server 的硬件环境、语句设计与执行效率，采取多维度优化策略。

类型 2：未按预期提交事务导致的阻塞

问题描述
这类阻塞通常发生在问题连接已经进入空闲状态，但事务未被提交或回滚。具体特征如下：

连接状态：sysprocesses.status='sleeping' 且 sysprocesses.cmd='AWAITING COMMAND'。
事务状态：sysprocesses.open_tran 不为 0，表明事务仍未完成。
原因：

- 应用端因执行超时或其他异常情况提前终止了语句，但连接未释放事务。
- 客户端未正确处理此意外，未发送事务提交或回滚指令，导致事务遗留在 SQL Server 中。

注意事项
执行超时（command timeout）是客户端行为，与 SQL Server 本身无关。默认情况下：

客户端（如 ADO 或 ADO.NET）一般设置了 30 秒的超时时间。
当超时发生时，客户端发送一个 Attention 消息给 SQL Server，指示终止当前运行的语句或批处理。
SQL Server 会终止语句，但不会自动提交或回滚事务，而是等待客户端发出明确指令。

如果客户端未发送回滚或提交指令，SQL Server 会持续保留事务，直至连接主动断开。

解决方法

排查问题连接

- 检查 sysprocesses 中状态为 sleeping 且 cmd='AWAITING COMMAND' 的连接。
- 确认其事务状态是否未完成（open_tran > 0）。

客户端优化

- 确保客户端应用程序正确处理执行超时和其他异常情况。
- 在超时或异常发生时，立即发送事务的回滚或提交指令，避免事务遗留。

数据库端监控

- 设置监控和告警，及时发现和处理长时间未提交的事务。
- 如果连接长期空闲且事务未完成，可通过手动回滚事务或断开连接解决问题。

开发人员协作

- 与开发团队沟通，优化客户端代码逻辑，避免因事务未提交导致的阻塞问题。

备注
良好的应用设计和事务管理至关重要。通过强化异常处理机制和超时设置，可以有效避免此类阻塞问题的发生。

这里可以用下面这个实验来模拟这个问题。在Management Studio里创建一个连接到SQL Server，运行下面的批处理语句：

sql

代码解读

复制代码

USE AdventureWorks2012 GO BEGIN TRAN SELECT * FROM Person.Address WITH (HOLDLOCK) SELECT * FROM sysobjects s1, sysobjects s2 COMMIT TRAN

由于使用了HOLDLOCK参数，第一句SELECT会在运行结束后，在表格上维持一个TAB的S锁。如果批处理全部完成，这个锁会在提交事务的时候释放。但是第二句SELECT会执行很久。请在等待3～4 s以后取消执行，模拟一个客户端的超时行为。然后运行下面的语句，检查open_tran和锁的情况。

sql

代码解读

复制代码

SELECT @@TRANCOUNT GO sp_lock GO

我这里没有模拟出来：

可能是版本问题。

假设我们返回的是这种状态

批处理被取消的时候，“COMMIT TRAN”这条语句没有被执行到。SQL Server没有对“BEGIN TRAN”开启的那个事务做任何处理，只是保持其活动的状态。
第一句SELECT带来的锁由于事务没有结束，所以锁还保持着（ObjId=55671246，Type=TAB，Mode=S）。现在，如果有其他连接要修改Person.Address这张表，就会被阻塞住。

解决方法

应用程序端改进

应用程序必须具备错误处理机制，以应对任何意外终止的情况。

（1）添加错误捕捉和处理逻辑

在执行 SQL 语句时，捕捉错误信息（如超时）。
捕捉到错误后，执行以下语句回滚未提交事务：

sql

代码解读

复制代码

IF @@TRANCOUNT > 0 ROLLBACK TRAN

注意：仅依赖 T-SQL 的错误处理（如IF @@ERROR <> 0 ROLLBACK TRAN）可能不足，因为严重异常（如超时）可能会终止整个批处理，而不是单独语句。

（2）启用SET XACT_ABORT ON

当启用此选项时，任何运行时错误会自动终止事务并回滚。
默认情况下为OFF，建议在建立连接或存储过程开始时显式设置：

vbnet

代码解读

复制代码

SET XACT_ABORT ON

如果无法快速完善应用程序的错误捕捉机制，此设置是一种快速有效的方法。

连接池管理

（1）注意连接池的事务清理

如果一个连接在未提交事务的情况下被返回连接池，事务可能会持续很长时间，导致阻塞。
大部分客户端驱动（如 .NET 的连接池）会在连接重用时清理事务，但部分驱动（如部分 Java 驱动）可能无法自动处理。
如果无法快速改进客户端处理，建议关闭连接池以缩短未提交事务的持续时间。

分析连接异常终止原因

通过记录错误信息，确定阻塞的根本原因：

超时问题：参考类型 1 的阻塞处理方法。
隐式事务导致的孤儿事务：

- 隐式事务模式（SET IMPLICIT_TRANSACTIONS ON）可能会导致事务自动开启但未显式提交。
- 解决方法：

1. 1. 避免隐式事务模式，使用自动提交模式（SET IMPLICIT_TRANSACTIONS OFF）。
  2. 约束应用程序端事务的使用，优先采用显式的 T-SQL 事务管理。

补充说明：隐式事务模式的风险

某些客户端驱动或控件可能默认启用了隐式事务，用以实现事务功能。
隐式事务需要显式提交或回滚，否则事务及其所有更改将在断开连接时被回滚。
推荐直接使用 T-SQL 提供的事务机制，避免应用程序引入额外复杂性。

触发隐式事务的语句

语句类型	示例操作
DDL 操作	`ALTER TABLE`、`CREATE`
DML 操作	`INSERT`、`UPDATE`、`DELETE`
数据操作	`SELECT`、`FETCH`
权限操作	`GRANT`、`REVOKE`

事务未提交的问题往往来源于应用端的设计缺陷，需从客户端到数据库端全面优化以解决阻塞问题。

类型 3：客户端未及时取出结果集导致的阻塞

问题描述
在 SQL Server 中，语句执行的总时间不仅包括 SQL Server 内部的执行时间，还包括将结果集发送到客户端的时间。以下情况可能导致语句执行时间过长并引发阻塞：

大结果集：SQL Server 需要将结果集分批打包发送，并等待客户端确认后再发送下一批。
客户端响应慢：如果客户端处理结果集的速度过慢，或不响应 SQL Server 的数据传输请求，SQL Server 会一直等待，延迟资源释放（包括锁资源）。

解决方法

减少返回结果集的大小

在设计程序时，应尽量避免返回大规模结果集：

需求驱动：根据最终用户的实际需求，筛选所需的部分数据。
精确查询：在 SQL 查询中明确指定所需字段和条件，避免返回无关数据。
示例：

sql

代码解读

复制代码

SELECT TOP 100 Column1, Column2 FROM Table WHERE Condition ORDER BY Column1;

避免查询语句默认返回全部数据，而最终只取用部分数据进行展示。

分离报表数据库与生产数据库

对于确实需要返回大结果集的应用（如报表系统）：

独立报表数据库：将报表相关的数据迁移到单独的数据库中运行。
读负载分离：通过数据同步工具（如 SQL Server 的复制或 CDC）分担生产数据库的压力。

使用更低的事务隔离级别

如果无法在短期内优化应用设计：

设置 READ UNCOMMITTED 隔离级别：

- 查询时避免申请共享锁（S 锁）。
- 允许读取未提交的数据，虽然会有脏读风险，但可以提高查询效率。
- 示例：

sql

代码解读

复制代码

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; SELECT * FROM LargeResultSetTable;

注意事项：仅在最终用户对数据一致性要求不高的情况下使用。

补充建议

优化客户端处理

- 确保客户端能够快速处理 SQL Server 返回的结果集。
- 避免客户端因设计或性能问题导致对结果集响应缓慢。

监控和诊断工具

- 使用 SQL Server 提供的 DMV（动态管理视图）或 Profiler 检查哪些查询返回了大量结果集：

ini

代码解读

复制代码

SELECT TOP 10 * FROM sys.dm_exec_requests WHERE status = 'suspended' AND command = 'SELECT';

- 记录客户端响应时间及网络延迟，定位性能瓶颈。

通过减少返回结果集的大小、分离报表数据库、调整事务隔离级别等方法，可以有效缓解客户端未及时取出结果集导致的阻塞问题。

类型 4：阻塞源头连接处于回滚状态

问题描述
这种阻塞情况通常由类型 1（长时间运行的语句）衍生而来。当数据库管理员为了解除阻塞，采取以下措施时，可能会引发此问题：

强制退出连接：包括关闭客户端应用或在 SQL Server 端通过 KILL 命令终止连接。

尽管这些操作可以终止阻塞连接，但 SQL Server 为了维护数据库事务的一致性，会启动事务回滚过程。这一过程需要：

找到事务已修改的所有记录，并将其恢复为原始状态。
回滚耗时与事务已运行的时间成正比（例如，一个运行了1小时的事务，其回滚也可能需要1小时）。

典型现象

回滚过程中，阻塞依然存在。
如果数据库管理员重启 SQL Server，回滚动作会在 SQL Server 重启后继续执行。
在回滚未完成前，数据库可能处于不可用状态，造成更严重的影响。

解决方法

减少大事务在高负载时的运行

为了避免因回滚阻塞带来的进退两难情况，建议：

避免高峰期运行大事务：

- 将诸如 DELETE、UPDATE、INSERT 的批量操作安排在非工作时间（如半夜或周末）。
- 通过任务调度器或脚本自动化此类任务。

将大事务分解为小事务

分步执行：将大范围操作拆分为多个小范围操作。例如：

sql

代码解读

复制代码

WHILE EXISTS (SELECT TOP 1 * FROM LargeTable WHERE Condition) BEGIN DELETE TOP (1000) FROM LargeTable WHERE Condition; END;

- 每次仅处理一小部分数据，以减少单次事务的影响。
- 小事务的回滚时间也相对较短。

避免重启 SQL Server

不要因回滚耗时而选择直接重启 SQL Server。
重启只会中断回滚过程，导致回滚在下次启动时重新执行，可能进一步延长数据库不可用的时间。

补充建议

提前评估操作影响

- 在执行大范围修改操作前，使用查询分析工具（如 EXPLAIN 或 SET STATISTICS IO/TIME ON）评估操作对资源的占用情况。
- 如果可能，先在测试环境验证操作的时间和影响，再在生产环境实施。

监控回滚进度

- 使用以下命令监控回滚的进度：

ini

代码解读

复制代码

SELECT percent_complete, estimated_completion_time, status FROM sys.dm_exec_requests WHERE command = 'KILLED/ROLLBACK';

3. 优化大事务执行的资源环境

- 在执行大事务前，确保数据库资源（如内存、I/O 和 CPU）处于健康状态，减少执行时间和潜在的回滚耗时。

通过提前规划、优化事务执行方式和避免错误处理，能够有效防止因回滚状态引起的长时间阻塞问题。

类型5：应用程序运行中产生死锁，在SQL Server中以阻塞形式体现。

举例：

在应用的一个线程中开启不止一个数据库连接而产生的死锁

① 开始运行。

② 建立数据库连接A，调用存储过程ProcA。打开结果集A。

③ 建立数据库连接B，调用存储过程ProcB。打开结果集B。

④ 轮流读取结果集A、B，整合输出最终结果。

⑤ 关闭结果集A、B，关闭连接A、B。

⑥ 结束运行。

两个线程间的死锁

线程A：建立数据库连接A，不断读取表格A，按条取出记录，做一定处理后发给线程B的输入缓存。
线程B：建立数据库连接B，从输入缓存读取数据，依据收到的记录对表格A进行修改。

这一类问题只能由应用设计以及代码逻辑中解决。

5. 死锁

在SQL Server的两个或多个任务中，如果某个任务锁定了其他任务试图锁定的资源，会造成这些任务永久阻塞，从而出现死锁。

死锁跟阻塞不同，阻塞是个过程，死锁是个结果。对于业务危害，偶尔出现的死锁可能没有阻塞那么大，但是当业务系统经常出现死锁时就需要关注死锁了。

像MySQL数据库，可以通过show engine innodb status排查最后一次出现的死锁，也可以通过参数将所有死锁输出到错误日志中，SQL Server好像没有这种机制，而且MySQL死锁检测是主动的，也就是每次上锁前都会判断当前的操作会不会产生死锁，而SQL Server是轮训检测机制，默认间隔是5秒。所以在MySQL中发现死锁时会立即返回，SQL Server需要等个几秒。

SQL Server发生死锁时，应用端会收到1205的错误，所以当发现应用端日志经常受到1205的错误时，就可以着手排查死锁，主要排查手段如下：

开启1222跟踪
开启SQL Trace

继续看死锁相关的一些原理及概念：

会发生死锁的资源：需要说明的是，死锁不是只发生在锁资源上。以下类型的资源都可能会造成阻塞，并最终导致死锁。

- 锁。例如页、行、元数据和应用程序上的锁。
- 工作线程。如果排队等待线程的任务拥有阻塞所有其他工作线程的资源，也会导致死锁。例如，会话S1启动事务并获取行R1的共享锁（S锁）后，进入睡眠状态。在所有可用工作线程上运行的活动会话正尝试获取行R1的排他锁（X锁）。因为会话S1无法获取工作线程，所以无法提交事务并释放行R1的锁，这将导致死锁
- 内存。当并发请求等待获得内存，而当前的可用内存无法满足其需要时，可能发生死锁。例如，两个并发查询（Q1和Q2）作为用户定义函数执行，分别获取10MB和20 MB内存。如果每个查询需要30 MB而可用总内存为20 MB，则Q1和Q2必须等待对方释放内存，这将导致死锁。
- 并行查询执行的相关资源。当一条语句用多个线程运行时，线程和线程之间可能会发生死锁。