SQL Server 数据库死锁处理

一、死锁简介

SQL Server数据库死锁是指在多个事务同时访问数据库资源时，发生了互相等待对方所持有资源的情况，导致所有事务无法继续执行的现象。当发生死锁时，SQL Server会自动选择一个事务进行回滚，以解除死锁并允许其他事务继续执行。这篇文章为个人学习笔记，可能会有错误。

二、死锁发生原因

1、死锁通常发生在以下情况下：

并发访问数据库资源：多个事务同时访问相同数据库资源，如表、行、页等。
锁的粒度不同：事务对数据库资源的锁定级别不同，如某个事务对某个表进行了排他锁，同时另一个事务也对该表进行了共享锁。
事务的执行顺序不同：多个事务按不同的顺序访问数据库资源，导致发生循环依赖的死锁。

2、为了避免死锁的发生，可以采取以下措施：

优化事务的设计：尽量减少事务的长度和对资源的占用时间，减少死锁的发生概率。
使用合适的锁：选择合适的锁级别，避免对资源造成过多的限制。
控制事务的并发度：限制同一时间内可以同时执行的事务数目，减少死锁的概率。
监控和调整数据库性能：定期监控数据库的性能，及时发现并解决潜在的死锁问题。

总之，SQL Server数据库死锁是一种常见的并发访问问题，可以通过优化事务设计、使用合适的锁、控制事务并发度和监控数据库性能等方式来减少死锁的发生。

三、发生死锁后的处理方法

1.了解死锁

死锁是一种状态，发生在两个或多个事务相互等待对方释放资源时，导致它们都无法继续执行。
它经常与正常阻塞混淆，但死锁几乎可以立即解决，而阻塞理论上可以无限期地持续存在。

2.检测死锁：

（1）、使用系统视图检测死锁

SQL Server提供了一些系统视图来帮助检测死锁，其中最重要的是

sys.dm_tran_locks

和

sys.dm_exec_requests

。

sys.dm_tran_locks视图显示了当前数据库中所有的锁信息。
sys.dm_exec_requests视图显示了当前正在执行的所有请求信息。
SELECT * FROM sys.dm_tran_locksSELECT * FROM sys.dm_exec_requests

你可以通过观察这两个视图的内容来判断是否存在死锁情况。例如，你可以查询

sys.dm_tran_locks

来查看当前的锁状态，并结合

sys.dm_exec_requests

来查看请求的状态和等待的资源。

（2）、使用扩展事件检测死锁

从SQL Server 2012开始，建议使用扩展事件（xEvent）来检测死锁，而不是使用SQL跟踪或SQL Profiler。你可以使用

xml_deadlock_report

扩展事件来捕获死锁信息。

方法： 1. 确保system_health会话已经启用（这是默认设置）。2. 你可以直接查询system_health会话的环形缓冲区来获取死锁事件的日志。

使用扩展事件（Extended Events）来检测死锁在SQL Server中是一种非常有效的方法。以下是如何使用扩展事件来捕获死锁图（Deadlock Graph）的具体步骤和示例代码：

创建会话（Session）

首先，你需要创建一个扩展事件会话来捕获死锁图。你可以使用SQL Server Management Studio (SSMS)的图形界面来创建，但在这里我将给出T-SQL代码示例。

CREATE EVENT SESSION [Deadlock_Graph_Capture]  
ON SERVER   
ADD EVENT sqlserver.xml_deadlock_report  
ADD TARGET package0.event_file(SET filename=N'D:\DeadlockGraphs\DeadlockGraph_$(ESCAPE_SQUOTE(FILE_NAME_FOR_DATE(GETDATE(), 'yyyyMMdd_HHmm'))).xel',max_file_size=(5),max_rollover_files=(4))  
WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=OFF);

注意：

上述代码创建了一个名为Deadlock_Graph_Capture的扩展事件会话。
它捕获sqlserver.xml_deadlock_report事件，这是当死锁发生时SQL Server生成的事件。
目标（TARGET）被设置为一个事件文件，该文件的路径和命名约定是你可以自定义的。在这个例子中，我使用了日期时间戳来命名文件，并设置了最大文件大小和滚动文件数。

2.启动会话

创建会话后，你需要启动它以开始捕获事件。

ALTER EVENT SESSION [Deadlock_Graph_Capture] ON SERVER STATE = START;

停止和删除会话（如果需要）

如果你不再需要捕获死锁图，或者想要重置会话设置，你可以停止并删除会话。

ALTER EVENT SESSION [Deadlock_Graph_Capture] ON SERVER STATE = STOP;  
DROP EVENT SESSION [Deadlock_Graph_Capture] ON SERVER;

查看死锁图

死锁图将被捕获到你在步骤1中指定的文件路径中。你可以使用SQL Server Management Studio (SSMS)或其他能够打开

.xel

文件的工具来查看死锁图。在SSMS中，你可以通过“管理”>“扩展事件”>“会话”来查看和管理你的扩展事件会话，并可以直接从SSMS中打开捕获的事件文件来查看死锁图。

注意事项

确保你有足够的磁盘空间来存储捕获的死锁图文件。
根据你的需求调整事件文件的最大大小和滚动文件数。
定期检查并管理你的扩展事件会话和文件，以避免不必要的磁盘空间占用。

（3）、使用DBCC TRACEON检测死锁

你可以使用

DBCC TRACEON

命令来开启特定的跟踪标志（trace flag），如1204或1222，以捕获死锁信息。这些跟踪标志会将死锁信息输出到SQL Server的错误日志中。

开启跟踪：DBCC TRACEON (1204,-1) 或 DBCC TRACEON (1222,-1)
关闭跟踪：DBCC TRACEOFF (1204,-1) 或 DBCC TRACEOFF (1222,-1)

（4）、使用SQL Server Profiler检测死锁

虽然SQL Server Profiler在2016年已被弃用，但在早期版本中，你可以使用它来检测死锁。在Profiler中，你可以配置特定的事件（如Deadlock graph）来捕获死锁信息，这里就不多介绍了。

（5）、查询性能计数器

你还可以查询SQL Server的性能计数器来获取死锁的数量。例如，你可以使用以下查询来获取自上次重启以来发生的死锁次数

SELECT cntr_value AS NumOfDeadLocks   
FROM sys.dm_os_performance_counters   
WHERE object_name = 'SQLServer:Locks'   
AND counter_name = 'Number of Deadlocks/sec'   
AND instance_name = '_Total';

但请注意，这个查询返回的是每秒的死锁数量，而不是总数量。

（6）、注意事项

在检测死锁时，请确保你有足够的权限来访问上述的系统视图和扩展事件。
死锁是一个复杂的问题，可能需要你深入分析数据库的操作和事务逻辑来找到根本原因。
在解决死锁问题时，务必考虑到数据的完整性和一致性，避免为了解决死锁而牺牲这些重要的数据库特性。

3.分析死锁

确定导致死锁的具体事务和资源。
分析这些事务的逻辑，找出为何它们会相互等待对方释放资源。

4.解决死锁：

优化锁的粒度：使用较低级别的锁（如行锁ROWLOCK）代替页面锁或表锁，以减少锁定范围，提高并发性。
使用READ COMMITTED SNAPSHOT或SNAPSHOT事务隔离级别：这可以将读取操作与其他事务隔离，减少锁定冲突。
保持事务简短并减少锁定时间：缩短事务持续时间，减少锁定资源的时间，有助于减少死锁风险。
按照相同的顺序访问资源：这样可以避免死锁，因为任何给定时刻，事务只需要等待另一个事务释放资源。
使用TRY...CATCH语句监视死锁错误：如果死锁发生，事务将被捕获并显示错误信息，可以选择重试事务。
使用NOLOCK选项（注意风险）：对于只读查询，使用NOLOCK可以避免发生死锁，但可能会导致脏读。
当死锁发生时直接杀进程：

首先查询死锁的表和进程

select    
    request_session_id spid,   
    OBJECT_NAME(resource_associated_entity_id) tableName    
from    
    sys.dm_tran_locks   
where    
    resource_type='OBJECT'

然后杀死想要杀死的进程

kill spid--spid为进程号

5.避免未来死锁：

避免事务中的用户交互：减少事务中等待用户输入的时间，以降低死锁的风险。
使用较低的隔离级别：如READ COMMITTED，这可以减少锁定的时间。
使用基于行版本控制的隔离级别：如SNAPSHOT，这可以消除许多锁定冲突。
使用绑定连接：确保相关的会话以相同的顺序访问资源。

6.监控和调优：

定期监控数据库的性能和死锁情况。
根据监控结果，对数据库和应用程序进行调优，以减少死锁的发生。

7.注意

在处理死锁时，务必考虑到数据的完整性和一致性，避免为了解决死锁而牺牲这些重要的数据库特性。
不同的应用场景可能需要采用不同的死锁处理策略，因此需要根据实际情况进行选择。

标签：数据库 sqlserver

本文转载自: https://blog.csdn.net/m0_74824913/article/details/138187702
版权归原作者 小仙女会魔法 所有，如有侵权，请联系我们删除。