锋利的SQL2014：处理死锁，sql2014
在两个或多个任务中，如果每个任务锁定了其他任务试图锁定的资源，此时会造成这些任务永久阻塞，从而出现死锁。例如：
事务A获取了行1的共享锁。
事务B获取了行2的共享锁。
现在，事务A请求行2的排他锁，但在事务B完成并释放其对行2持有的共享锁之前被阻塞。
现在，事务B请求行1的排他锁，但在事务A完成并释放其对行1持有的共享锁之前被阻塞。
事务A必须在事务B完成之后才能完成，但事务B被事务A阻塞。这种情况也称为循环依赖关系：事务A依赖于事务B，而事务B又依赖于事务A，从而形成了一个循环。
除非某个外部进程断开死锁，否则死锁中的两个事务都将无限期等待下去。数据库引擎死锁监视器定期检查陷入死锁的任务。如果检测死锁，将选择其中一个任务作为牺牲品，然后终止其事务并提示错误。这样，其他任务就可以完成其事务，从而解除死锁。对于事务以错误终止的应用程序，它还可以重试该事务，但通常要等到与它一起陷入死锁的其他事务完成后执行。
17.6.1 防止死锁的方法
1．按同一顺序访问对象
如果所有并发事务按同一顺序访问对象，则发生死锁的可能性会降低。例如，如果两个并发事务先获取Supplier表上的锁，然后获取Part表上的锁。在其中一个事务完成之前，另一个事务将在Supplier表上被阻塞。当第1个事务提交或回滚之后，第2个事务将继续执行，这样就不会发生死锁。如果使用存储过程进行数据修改，则可以使对象的访问顺序标准化。
2．避免事务中的用户交互
避免编写包含用户交互的事务，因为没有用户干预的批处理的运行速度远快于用户必须手动响应查询时的速度。例如，如果事务正在等待用户输入，而用户去吃午餐了，那么用户就耽误了事务的完成。这将降低系统的吞吐量，因为事务持有的任何锁只有在事务提交或回滚后才会释放。即使不出现死锁的情况，在占用资源的事务完成之前，访问同一资源的其他事务也会被阻塞。
3．保持事务简短并处于一个批处理中
在同一数据库中，并发执行多个需要长时间运行的事务时通常会发生死锁。事务的运行时间越长，它持有排他锁或更新锁的时间也就越长，从而会阻塞其他活动并可能导致死锁。
4．使用较低的隔离级别
确定事务是否能在较低的隔离级别上运行。实现已提交读允许事务读取另一个事务已读取（未修改）的数据，而不必等待第1个事务完成。使用较低的隔离级别（例如已提交读）比使用较高的隔离级别（例如可序列化）持有共享锁的时间更短。这样就减少了锁争用。
5．使用基于行版本控制的隔离级别
如果将READ_COMMITTED_SNAPSHOT数据库选项设置为ON，则在已提交读隔离级别下运行的事务在读操作期间将使用行版本控制而不是共享锁。
17.6.2 使用TRY...CATCH处理死锁
在TRY...CATCH构造的CATCH块可以捕获1205死锁错误，发生错误后，可以通过回滚事务来解除锁定。下面的语句创建了用于说明死锁状态的表和用于打印错误信息的存储过程。
USE AdventureWorks;
GO
-- 验证表是否已经存在
IF OBJECT_ID(N'my_sales',N'U') IS NOT NULL
DROP TABLE my_sales;
GO
-- 创建表并插入数据
CREATE TABLE my_sales
(
Itemid INT PRIMARY KEY,
Sales INT not null
);
GO
INSERT my_sales (itemid,sales) VALUES (1, 1);
INSERT my_sales (itemid,sales) VALUES (2, 1);
GO
-- 验证存储过程是否已经存在
IF OBJECT_ID(N'usp_MyErrorLog',N'P') IS NOT NULL
DROP PROCEDURE usp_MyErrorLog;
GO
-- 创建存储过程，用于输出错误消息
CREATE PROCEDURE usp_MyErrorLog
AS
N'错误 ' +CONVERT(VARCHAR(50), ERROR_NUMBER()) +
N', 严重级别 ' +CONVERT(VARCHAR(5), ERROR_SEVERITY()) +
N', 状态 ' +CONVERT(VARCHAR(5), ERROR_STATE()) +
N', 行 ' +CONVERT(VARCHAR(5), ERROR_LINE());
ERROR_MESSAGE();
下面的会话1和会话2代码脚本在两个单独的SQL Server Management Studio连接下同时运行。两个会话都尝试更新表中的相同行。在第一次尝试过程中，其中一个会话将成功完成更新操作，而另一个会话将被选择为死锁牺牲品。死锁牺牲品错误将使执行跳至CATCH块，事务将进入无法提交状态。在CATCH块中，死锁牺牲品会回滚事务并重试更新此表，直到更新成功或达到了重试限制。
会话1 | 会话2 |
USE AdventureWorks; GO
-- 定义并设置变量，指定尝试提交更新的次数 DECLARE @retry INT; SET @retry = 5;
-- 如果被作为了死锁牺牲品，保持尝试更新 WHILE (@retry > 0) BEGIN BEGIN TRY BEGIN TRANSACTION;
UPDATE my_sales SET sales = sales + 1 WHERE itemid = 1;
-- 延时等待，此时itemid为1和2的行 -- 在没有提交前，都无法释放锁 WAITFOR DELAY '00:00:13';
UPDATE my_sales SET sales = sales + 1 WHERE itemid = 2;
SET @retry = 0;
COMMIT TRANSACTION; END TRY BEGIN CATCH -- 检测错误编号，如果是死锁牺牲品， -- 则减少重新尝试计数。如果是其他 -- 错误，则退出WHILE循环 IF (ERROR_NUMBER() = 1205) SET @retry = @retry - 1; ELSE SET @retry = -1;
-- 输出错误消息 EXECUTE usp_MyErrorLog;
-- 会话中包含无法提交的事务 -- XACT_STATE将返回 -1 IF XACT_STATE() <> 0 ROLLBACK TRANSACTION; END CATCH; END; -- 结束WHILE循环 | USE AdventureWorks; GO
-- 定义并设置变量，指定尝试提交更新的次数 DECLARE @retry INT; SET @retry = 5;
-- 如果被作为了死锁牺牲品，保持尝试更新 WHILE (@retry > 0) BEGIN BEGIN TRY BEGIN TRANSACTION;
UPDATE my_sales SET sales = sales + 1 WHERE itemid = 2;
-- 延时等待，此时itemid为1和2的行 -- 在没有提交前，都无法释放锁 WAITFOR DELAY '00:00:07';
UPDATE my_sales SET sales = sales + 1 WHERE itemid = 1;
SET @retry = 0;
COMMIT TRANSACTION; END TRY BEGIN CATCH -- 检测错误编号，如果是死锁牺牲品， -- 则减少重新尝试计数。如果是其他 -- 错误，则退出WHILE循环 IF (ERROR_NUMBER() = 1205) SET @retry = @retry - 1; ELSE SET @retry = -1;
-- 输出错误消息 EXECUTE usp_MyErrorLog;
-- 会话中包含无法提交的事务 -- XACT_STATE将返回 -1 IF XACT_STATE() <> 0 ROLLBACK TRANSACTION; END CATCH; END; -- 结束WHILE循环 |
下面是会话1中返回的消息，表示两行都已经被更新。
(1 行受影响)
(1 行受影响)
下面是会话2中返回的消息，会话2被作为了死锁牺牲品。
(1 行受影响) -- 由于死锁，会话2事务中只成功更新1行，这时会发生回滚操作
错误 1205, 严重级别 13, 状态 51, 行18 -- 由存储过程usp_MyErrorLog输出的错误消息
事务(进程 ID52)与另一个进程被死锁在锁资源上，并且已被选作死锁牺牲品。请重新运行该事务。 -- SQL Server的提示
(1 行受影响) -- 本行和下行消息是重新尝试更新后得到的提示消息
(1 行受影响)