Deadlock 总结

死锁是由于并发进程只能按互斥方式访问临界资源等多种因素引起的，并且是一种与执行时间和速度密切相关的错误现象。

    死锁的定义：若在一个进程集合中，每一个进程都在等待一个永远不会发生的事件而形成一个永久的阻塞状态，这种阻塞状态就是死锁。

      死锁产生的必要条件：

      1.互斥mutual exclusion)：系统存在着临界资源；

      2.占有并等待(hold and wait)：已经得到某些资源的进程还可以申请其他新资源；

      3.不可剥夺(no preemption)：已经分配的资源在其宿主没有释放之前不允许被剥夺；

      4.循环等待(circular waiting)：系统中存在多个（大于2个）进程形成的封闭的进程链，链中的每个进程都在等待它的下一个进程所占有的资源；

死锁的示例和解决方法

      首先我们在数据库tempdb中创建两个表DlTable1和DlTable2，它们都包含两个字段分别是Id和Name，接着我们往这两个表中插入数据，具体SQL代码如下：

-- Note we use tempdb for testing.
USEtempdb

-- Create datatable in tempdb.
CREATE TABLEDlTable1(DL1IdINT,DL1NameVARCHAR(20))
CREATE TABLEDlTable2(DL2IdINT,DL2NameVARCHAR(20))


-- Insert multiple data into DlTable1 and DlTable2 in SQL Server 2005.
INSERT INTODlTable1
SELECT1,'Deadlock'
UNION ALL
SELECT2,'JKhuang'
UNION ALL
SELECT3,'Test'
GO

INSERT INTODlTable2
SELECT1,'Deadlock'
UNION ALL
SELECT2,'JacksonHuang'
UNION ALL
SELECT3,'Test'
GO

  接着我们打开两个查询窗口分别创建两个独立的事务A和B如下：

-- In query window 1.USEtempdb GO-- Create transaction A.BEGIN TRANSACTION UPDATEDlTable1SETDL1Name='Uplock'WHEREDL1Id=2-- Delay 23 second.WAITFOR DELAY'00:00:23'UPDATEDlTable2SETDL2Name='Downlock'WHEREDL2Id=2ROLLBACK TRANSACTION-- In query window 2.USEtempdb GO-- Create transaction B.BEGIN TRANSACTION UPDATEDlTable2SETDL2Name='Downlock'WHEREDL2Id=2-- Delay 23 second.WAITFOR DELAY'00:00:23'UPDATEDlTable1SETDL1Name='Uplock'WHEREDL1Id=2ROLLBACK TRANSACTION

     上面我们定义了两个独立的事务A和B，为了测试死锁这里我们使用WAITFOR DELAY使事务执行产生延时。

-- Insert multiple data into DlTable1 and DlTable2 in SQL Server 2008.
INSERT INTODlTable1VALUES(1,'Deadlock'), (2,'JKhuang'), (3,'Test')
INSERT INTODlTable2VALUES(1,'Deadlock'), (2,'JacksonHuang'), (3,'Test')

    现在我们执行以上SQL代码成功创建了DlTable1和DlTable2并且插入了数据。

现在我们使用SQL Server Profiler分析死锁

      在本节中，我们将看到如何使用SQL Server Profiler来捕获死锁跟踪。

      1.启动SQL Server事件探查器和连接所需的SQL Server实例

      2.创建一个新的跟踪

      3.在事件选择页中，取消默认事件选项，我们选择“死锁图形”事件、 “锁定：死锁”和“锁定：死锁链”如下图所示：

图8事件选择设置

     4. 启动一个新的跟踪

     5.在SSMS中，开两个查询窗口#1和#2，我们重新执行前面两个事务

     6.事务执行结束，一个执行成为，另一个发生死锁错误

     7.我们打开事件探查器，如下图所示：

图9 Deadlock graph

     8.选择Deadlock graph，我们可以直观查看到两个事务之间发生死锁的原因

图10 事务进程A

      上图的椭圆形有一个叉，表示事务A被SQL Server选择为死锁牺牲品，如果我们把鼠标指针移动到椭圆中会出现一个提示。

图11 事务进程B

      上图的椭圆形表示进程执行成功，我们把鼠标指针移动到椭圆中也会出现一个提示。

      中间的两个矩形框称为资源节点，它们代表的数据库对象，如表，行或索引。由于事务A和B在拥有各自资源时试图获得对方资源的一个独占锁，使得进程相互等待对方释放资源从而导致死锁。

死锁避免：

     现在让我们回顾一下上了死锁的四个必要条件：互斥,占有并等待,不可剥夺和循环等待；我们只需破坏其中的一个或多个条件就可以避免死锁发生，方法如下：

     (1).按同一顺序访问对象。(注：避免出现循环，降低了进程的并发执行能力)

     (2).避免事务中的用户交互。(注：减少持有资源的时间，减少竞争)

     (3).保持事务简短并处于一个批处理中。(注：同(2)，减少持有资源的时间)

     (4).使用较低的隔离级别。(注：使用较低的隔离级别（例如已提交读）比使用较高的隔离级别（例如可序列化）持有共享锁的时间更短，减少竞争)

     (5).使用基于行版本控制的隔离级别：2005中支持快照事务隔离和指定READ_COMMITTED隔离级别的事务使用行版本控制，可以将读与写操作之间发生的死锁几率降至最低：

     SET ALLOW_SNAPSHOT_ISOLATION ON --事务可以指定 SNAPSHOT 事务隔离级别;

     SET READ_COMMITTED_SNAPSHOT ON --指定 READ_COMMITTED 隔离级别的事务将使用行版本控制而不是锁定。默认情况下(没有开启此选项，没有加with nolock提示)，SELECT语句会对请求的资源加S锁(共享锁)；而开启了此选项后，SELECT不会对请求的资源加S锁。

      注意：设置 READ_COMMITTED_SNAPSHOT选项时，数据库中只允许存在执行 ALTER DATABASE命令的连接。在 ALTER DATABASE完成之前，数据库中决不能有其他打开的连接。数据库不必一定要处于单用户模式中。

     在数据库中设置READ COMMITTED SNAPSHOT或 ALLOW SNAPSHOT ISOLATIONON ON时，查询数据时不再使用请求共享锁，如果请求的行正被锁定(例如正在被更新)，SQL_Server会从行版本存储区返回最早的关于该行的记录(SQL_server会在更新时将之前的行数据在tempdb库中形成一个链接列表。（详细请点这里和这里）

ALTER DatabaseDATABASENAMESET READ_COMMITTED_SNAPSHOT ON

     (6).使用绑定连接。(注：绑定会话有利于在同一台服务器上的多个会话之间协调操作。绑定会话允许一个或多个会话共享相同的事务和锁(但每个回话保留其自己的事务隔离级别)，并可以使用同一数据，而不会有锁冲突。可以从同一个应用程序内的多个会话中创建绑定会话，也可以从包含不同会话的多个应用程序中创建绑定会话。在一个会话中开启事务(begin tran)后，调用exec sp_getbindtoken @Token out;来取得Token，然后传入另一个会话并执行EXEC sp_bindsession @Token来进行绑定(最后的示例中演示了绑定连接)。

解决死锁

      这里有几个方法可以帮助我们解决死锁问题。

      优化查询

      我们在写查询语句时，要考虑一下查询是否Join了没有必要的表？是否返回数据太多（太多的列或行）？查询是否执行表扫描？是否能通过调整查询次序来避免死锁？是否应该使用Join的地方使用了Left Join？Not In语句是否考虑周到？

      我们在写查询语句可以根据以上准则来考虑查询是否应该做出优化。

      慎用With(NoLock)

      默认情况下SELECT语句会对查询到的资源加S锁(共享锁)，由于S锁与X锁(排他锁)不兼容，在加上With(NoLock)后，SELECT不对查询到的资源加锁(或者加Sch-S锁，Sch-S锁可以与任何锁兼容)；从而使得查询语句可以更好和其他语句并发执行，适用于表数据更新不频繁的情况。

     也许有些人会提出质疑With(NoLock)，可能会导致脏读，首先我们要考虑查询的表是否频繁进行更新操作，而且是否要读回来的数据会被修改，所以衡量是否使用With(NoLock)还是要根据具体实际出发。

     优化索引

     是否有任何缺失或多余的索引？是否有任何重复的索引？

     处理死锁

     我们不能时刻都观察死锁的发生，但我们可以通过日志来记录系统发生的死锁，我们可以把系统的死锁错误写入到表中，从而方便分析死锁原因。

     缓存

     也许我们正在执行许多相同的查询非常频繁，如果我们把这些频繁的操作都放到Cache中，执行查询的次数将减少发生死锁的机会。我们可以在数据库的临时表或表，或内存，或磁盘上应用Cache。