SQL-Update引起的死锁

死锁的形成与避免很早就知道了，但是一直知其然不知其依然，这两天使用语句自己制造死锁与分析，更加深刻的理解死锁的形成。PS：可能有理解有误的地方，希望指出

前期

• SQL语句准备

CREATE TABLE [dbo].[Table_A](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[Name] [nvarchar](8) NULL,
 CONSTRAINT [PK_Table_A] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
CREATE TABLE [dbo].[Table_B](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[Name] [nvarchar](8) NULL,
 CONSTRAINT [PK_Table_B] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
insert into [dbo].[Table_A](Name) values('测试数据A');
insert into [dbo].[Table_A](Name) values('测试数据A');
insert into [dbo].[Table_A](Name) values('测试数据A');
insert into [dbo].[Table_A](Name) values('测试数据A');
insert into [dbo].[Table_A](Name) values('测试数据A');
insert into [dbo].[Table_A](Name) values('测试数据A');
insert into [dbo].[Table_A](Name) values('测试数据A');
insert into [dbo].[Table_A](Name) values('测试数据A');

insert into [dbo].[Table_B](Name) values('测试数据B');
insert into [dbo].[Table_B](Name) values('测试数据B');
insert into [dbo].[Table_B](Name) values('测试数据B');
insert into [dbo].[Table_B](Name) values('测试数据B');
insert into [dbo].[Table_B](Name) values('测试数据B');
insert into [dbo].[Table_B](Name) values('测试数据B');
insert into [dbo].[Table_B](Name) values('测试数据B');
insert into [dbo].[Table_B](Name) values('测试数据B');

好了，基础数据准备完了，现在我们先把配置一下SQL Profiler来检测如何形成的死锁。

• SQL Profiler准备

基础用法https://blog.youkuaiyun.com/u011791378/article/details/83011220这里面有简绍，这里就不多说了，直接上图

• 死锁形成

会话一：

begin tran
--one
update [Table_A] set Name='测试修改AAA' where ID=2
waitfor delay '00:00:30'
--two
update [Table_B] set Name='测试修改BBB' where ID=2
commit tran

会话二：

begin tran
--three
update [Table_B] set Name='测试修改BB' where ID=2
waitfor delay '00:00:30'
--four
update [Table_A] set Name='测试修改AA' where ID=2
commit tran

我们先来执行会话一的事务，再执行会话二的事务。等待一段时间后我们发现会话二提示成功。

会话一提示:

(1 行受影响)
消息 1205，级别 13，状态 51，第 5 行
事务(进程 ID 55)与另一个进程被死锁在 锁 资源上，并且已被选作死锁牺牲品。请重新运行该事务。

根据提示我们发现数据库引擎把会话一的事务作为牺牲品。

为什么会这样呢？因为【数据库引擎会定期检索死锁，一旦发现问题，会选取其中一个事务作为牺牲品，终止事务的运行，从而释放资源。】

• 分析Profiler

1. Deadlock Graph 事件类提供了有关死锁的 XML 描述。 该类与 Lock:Deadlock 事件类同时发生
2. 当尝试获取锁的操作因属于死锁的一部分且已被选为死锁牺牲品而被取消时，将发生 Lock:Deadlock 事件类。
3. 使用 Lock:Deadlock Chain 事件类可以监视何时出现死锁情况。 此信息有助于确定死锁是否会对应用程序的性能造成重大影响，以及会涉及哪些对象。 可以检查用于修改这些对象的应用程序代码，以便确定是否可以做出更改以便将死锁的情况减到最少。

 

如上图所示

第一行：开始检测

第二行：执行会话一语句，SPID=55

第三行：执行会话二语句，SPID=56

。。。。。。

第七行：    选中第七行会显示以下信息

                                                                                                                                                                                                            第一点： 鼠标移动到左侧的椭圆形时会发现 内部执行的代码为【会话一的事务】，说明会话一的事务已经被 数据库引擎 牺牲掉。鼠标移动到右侧椭圆形时会发现内部执行代码为【会话二的事务】，没有画×号，证明执行成功。

第二点：执行会话一的时候，第一步 one 对A表进行修改，此时A产生排它锁（IX）。 由于有等待时间30秒 所以Two 暂时还未执行。而此时会话二 three 执行了B表的修改，故B表产生了排它锁。这时候会话一的等待时间已经到了，要执行 Two语句 ，发现B表的排它锁已经有了，所以就在等待B表排它锁的释放。此时会话二的等待时间已经到了 要执行 four 语句 的时候 发现A表也有排它锁，所以 four 语句也在等待A表排它锁的释放。 此时就陷入了僵局。当会话一事务提交之后，A表的排它锁才会释放，同理 当会话二的事务提交以后 B表的排它锁也会释放。但是会话一的事务在等待会话二中事务的提交，而会话二中的事务也在等待会话一中的事务的提交。 如果不干涉的情况下，就会出现会话一的事务和会话二的事务无限等待中。所以数据库引擎会定期检索死锁，一旦发现问题，会选取其中一个事务作为牺牲品，终止事务的运行，从而释放资源。

注：此时说的锁指的是行锁，并不是说update一定引起行锁而不会引起表锁！主要得看表的索引。

 

总结

• 死锁的形成（百度百科是这么解释的）

• 死锁的避免

1）统一顺序访问对象

2）避免事务的交互

3）尽量保持事务的简短性

4）隔离级别的使用