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视图显示了当前正在执行的所有请求信息。
  
• 1. SELECT * FROM sys.dm_tran_locks
  2. SELECT * FROM sys.dm_exec_requests

  复制代码

你可以通过观察这两个视图的内容来判定是否存在死锁情况。例如，你可以查询sys.dm_tran_locks来查察当前的锁状态，并联合sys.dm_exec_requests来查察请求的状态和等候的资源。 
（2）、利用扩展事件检测死锁

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

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

利用扩展事件（Extended Events）来检测死锁在SQL Server中是一种非常有效的方法。以下是怎样利用扩展事件来捕获死锁图（Deadlock Graph）的具体步骤和示例代码：
1. 创建会话（Session）
首先，你需要创建一个扩展事件会话来捕获死锁图。你可以利用SQL Server Management Studio (SSMS)的图形界面来创建，但在这里我将给出T-SQL代码示例。

1. CREATE EVENT SESSION [Deadlock_Graph_Capture]  
2. ON SERVER   
3. ADD EVENT sqlserver.xml_deadlock_report  
4. 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))  
5. 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.启动会话 
创建会话后，你需要启动它以开始捕获事件。

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

复制代码

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

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

复制代码

4. 查察死锁图
死锁图将被捕获到你在步骤1中指定的文件路径中。你可以利用SQL Server Management Studio (SSMS)或其他能够打开.xel文件的工具来查察死锁图。在SSMS中，你可以通过“管理”>“扩展事件”>“会话”来查察和管理你的扩展事件会话，并可以直接从SSMS中打开捕获的事件文件来查察死锁图。
5. 注意事项

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

（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的性能计数器来获取死锁的数量。例如，你可以利用以下查询来获取自前次重启以来发生的死锁次数

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

复制代码

但请注意，这个查询返回的是每秒的死锁数量，而不是总数量。
（6）、 注意事项

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

3.分析死锁 

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

4.办理死锁： 

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

首先查询死锁的表和历程

1. select   
2.     request_session_id spid,   
3.     OBJECT_NAME(resource_associated_entity_id) tableName   
4. from   
5.     sys.dm_tran_locks   
6. where   
7.     resource_type='OBJECT'

复制代码

 然后杀死想要杀死的历程

1. kill spid--spid为进程号

复制代码

 5.避免未来死锁：

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

6.监控和调优： 

• 定期监控数据库的性能和死锁情况。
  
• 根据监控效果，对数据库和应用步调举行调优，以减少死锁的发生。
  

 7.注意

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

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。