SQL Server的死锁是什么及怎么结束

蜗牛互联网技术资讯2023-05-11390

本文小编为大家详细介绍“SQL Server的死锁是什么及怎么结束”，内容详细，步骤清晰，细节处理妥当，希望这篇“SQL Server的死锁是什么及怎么结束”文章能帮助大家解决疑惑，下面跟着小编的思路慢慢深入，一起来学习新知识吧。

一、了解死锁

死锁是导致数据库中的竞争性并发锁，通常在多步骤事务中。
当两个或多个任务永久相互阻止时，每个任务都锁定了其他任务尝试锁定的资源，就会发生死锁。例如：

事务 A 获取第 1 行上的共享锁。
事务 B 获取第 2 行上的共享锁。
事务 A 现在请求第 2 行上的独占锁，并被阻止，直到事务 B 完成并释放第 2 行上的共享锁。
事务 B 现在请求第 1 行上的独占锁，并被阻止，直到事务 A 完成并释放它在第 1 行上的共享锁。

事务 A 在事务 B 完成之前无法完成，但事务 B 被事务 A 阻止。此条件也称为循环依赖关系：事务 A 依赖于事务 B，事务 B 通过对事务 A 的依赖来关闭循环。

死锁中的两个事务将永远等待，除非死锁被外部进程打破。SQL Server 数据库引擎死锁监视器定期检查处于死锁状态的任务。如果监视器检测到循环依赖关系，它将选择其中一个任务作为受害者，并在出现错误的情况下终止其事务。这允许其他任务完成其事务。具有因错误而终止的事务的应用程序可以重试该事务，该事务通常在另一个死锁事务完成后完成。

死锁经常与正常阻塞混淆。当一个事务请求锁定另一个事务锁定的资源时，请求事务将等待，直到锁定被释放。默认情况下，除非设置了LOCK_TIMEOUT，否则 SQL Server 事务不会超时。请求事务被阻止，而不是死锁，因为请求事务没有执行任何操作来阻止拥有锁的事务。最终，拥有事务将完成并释放锁，然后请求事务将被授予锁定并继续。死锁几乎可以立即解决，而阻塞理论上可以无限期地持续存在。僵局有时被称为致命的拥抱。

死锁是可能发生在具有多个线程的任何系统上的情况，而不仅仅是在关系数据库管理系统上，并且可能发生在数据库对象上的锁以外的资源上。例如，多线程操作系统中的线程可能会获取一个或多个资源，例如内存块。如果要获取的资源当前由另一个线程拥有，则第一个线程可能必须等待拥有该线程释放目标资源。据说等待线程依赖于该特定资源的拥有线程。在 SQL Server 数据库引擎实例中，会话在获取非数据库资源（如内存或线程）时可能会死锁。

SQL Server的死锁是什么及怎么结束 sql server 第1张

在图中，事务 T1 依赖于表锁资源的事务 T2。同样，事务 T2 依赖于表锁资源的事务 T1。由于这些依赖项形成一个循环，因此事务 T1 和 T2 之间存在死锁。

当对表进行分区并将的设置设置为 AUTO 时，也会发生死锁。设置为 AUTO 时，通过允许 SQL Server 数据库引擎在 HoBT 级别而不是表级别锁定表分区，并发性会增加。但是，当单独的事务在表中持有分区锁并希望在其他事务分区上的某个位置使用锁时，这会导致死锁。这种类型的死锁可以通过设置为 ;尽管此设置将通过强制对分区进行大量更新以等待表锁定来降低并发性。

二、检测并结束死锁

当两个或多个任务永久相互阻止时，每个任务都锁定了其他任务尝试锁定的资源，就会发生死锁。下图显示了死锁状态的高级视图，其中：

任务 T1 锁定了资源 R1（由从 R1 到 T1 的箭头指示），并请求锁定资源 R2（由从 T1 到 R2 的箭头指示）。
任务 T2 锁定了资源 R2（由从 R2 到 T2 的箭头指示），并请求锁定资源 R1（由从 T2 到 R1 的箭头指示）。
由于在资源可用之前，这两个任务都无法继续，并且在任务继续之前无法释放这两个资源，因此存在死锁状态。

SQL Server的死锁是什么及怎么结束 sql server 第2张

SQL Server 数据库引擎会自动检测 SQL Server 中的死锁周期。SQL Server 数据库引擎选择其中一个会话作为死锁受害者，当前事务将终止，并显示错误以打破死锁。

2.1、可能死锁的资源

每个用户会话可能代表其运行一个或多个任务，其中每个任务可能获取或等待获取各种资源。以下类型的资源可能会导致阻塞，从而导致死锁。

锁。等待获取资源（如对象、页面、行、元数据和应用程序）的锁可能会导致死锁。例如，事务 T1 在行 r1 上具有共享（S）锁，并且正在等待在 r2 上获取独占（X）锁。事务 T2 在 r2 上具有共享（S）锁，并且正在等待在行 r1 上获取独占（X）锁。这会导致锁定循环，其中 T1 和 T2 相互等待对方释放锁定的资源。
工作线程。等待可用工作线程的排队任务可能会导致死锁。如果排队的任务拥有阻止所有工作线程的资源，则会导致死锁。例如，会话 S1 启动事务并在行 r1 上获取共享（S）锁，然后进入睡眠状态。在所有可用工作线程上运行的活动会话正在尝试获取行 r1 上的独占（X）锁。由于会话 S1 无法获取工作线程，因此它无法提交事务并释放行 r1 上的锁。这会导致死锁。
内存。当并发请求正在等待可用内存无法满足的内存授予时，可能会发生死锁。例如，两个并发查询 Q1 和 Q2 作为用户定义的函数执行，分别获取 10 MB 和 20 MB 的内存。如果每个查询需要 30 MB，总可用内存为 20 MB，则 Q1 和 Q2 必须等待对方释放内存，这会导致死锁。
与并行查询执行相关的资源。与交换端口关联的协调器、生产者或使用者线程可能会相互阻塞，从而导致死锁，通常是在包含至少一个不属于并行查询一部分的其他进程时。此外，当并行查询开始执行时，SQL Server 会根据当前工作负荷确定并行度或工作线程数。如果系统工作负荷意外更改（例如，新查询开始在服务器上运行或系统工作线程不足），则可能会发生死锁。
多个活动结果集（MARS）资源。用户资源、会话互斥锁、事务互斥锁等这些资源用于控制 MARS 下多个活动请求的交错。

为了使任务在 MARS 下运行，它必须获取会话互斥锁。如果任务在事务下运行，则必须获取事务互斥锁。这保证在给定会话和给定事务中一次只有一个任务处于活动状态。获取所需的互斥锁后，任务就可以执行。当任务完成或在请求中间产生时，它将首先释放事务互斥锁，然后以相反的获取顺序释放会话互斥锁。但是，这些资源可能会发生死锁。在以下伪代码中，两个任务（用户请求 U1 和用户请求 U2）在同一会话中运行。

从用户请求 U1 执行的存储过程已获取会话互斥锁。如果存储过程需要很长时间才能执行，则 SQL Server 数据库引擎假定存储过程正在等待用户的输入。用户请求 U2 正在等待会话互斥锁，而用户正在等待来自 U2 的结果集，U1 正在等待用户资源。这是死锁状态，逻辑上说明为：

SQL Server的死锁是什么及怎么结束 sql server 第3张

三、处理死锁

当 SQL Server 数据库引擎实例选择某个事务作为死锁受害者时，它将终止当前批处理，回滚该事务，并向应用程序返回错误消息 1205。

由于提交 Transact-SQL 查询的任何应用程序都可以被选为死锁受害者，因此应用程序应具有可以捕获错误消息 1205 的错误处理程序。如果应用程序未捕获错误，则应用程序可以在不知道其事务已回滚并可能发生错误的情况下继续操作。

实现捕获错误消息 1205 的错误处理程序允许应用程序处理死锁情况并采取补救措施（例如，自动重新提交死锁中涉及的查询）。通过自动重新提交查询，用户无需知道发生了死锁。

在重新提交查询之前，应用程序应短暂暂停。这使死锁中涉及的其他事务有机会完成并释放构成死锁循环一部分的锁。这样可以最大程度地降低在重新提交的查询请求其锁时再次发生死锁的可能性。

四、最大限度地减少死锁

尽管无法完全避免死锁，但遵循某些编码约定可以最大程度地减少生成死锁的可能性。最小化死锁可以提高事务吞吐量并减少系统开销，因为事务较少：

回滚，撤消事务执行的所有工作。
由应用程序重新提交，因为它们在死锁时已回滚。

要帮助最大程度地减少死锁，请执行以下操作：

以相同的顺序访问对象。
避免事务中的用户交互；保持交易简短且批量。
使用较低的隔离级别。
使用基于行版本控制的隔离级别。将数据库选项设置为启用已提交的读取事务以使用行版本控；使用快照隔离。
使用绑定连接。

4.1、以相同的顺序访问对象

如果所有并发事务都以相同的顺序访问对象，则不太可能发生死锁。例如，如果两个并发事务在表上获取锁，然后在表上获得锁，则一个事务在表上被阻止，直到另一个事务完成。第一个事务提交或回滚后，第二个事务将继续，并且不会发生死锁。对所有数据修改使用存储过程可以标准化访问对象的顺序。

SQL Server的死锁是什么及怎么结束 sql server 第4张

4.2、避免事务中的用户交互

避免编写包含用户交互的事务，因为无需用户干预即可运行的批处理的速度比用户必须手动响应查询（例如回复应用程序请求的参数提示）的速度快得多。例如，如果事务正在等待用户输入，并且用户周末去吃午饭甚至回家，则用户会延迟事务完成。这会降低系统吞吐量，因为事务持有的任何锁仅在提交或回滚事务时释放。即使没有出现死锁情况，访问相同资源的其他事务也会在等待事务完成时被阻止。