SQL中的共享锁分析及如何解锁

1.1.1 摘要

      在系统设计过程中，系统的稳定性、响应速度和读写速度至关重要，就像12306.cn那样，当然我们可以通过提高系统并发能力来提高系统性能总体性能，但在并发作用下也会出现一些问题，例如死锁。

     今天的博文将着重介绍死锁的原因和解决方法。

1.1.2 正文

      定义：

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

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

      死锁产生的必要条件：

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

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

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

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

图1死锁产生条件

      我们知道哲学家就餐问题是在计算机科学中的一个经典问题（并发和死锁），用来演示在并行计算中多线程同步(Synchronization)时产生的问题，其中一个问题就是存在死锁风险。

图2哲学家就餐问题（图片源于wiki）

     而对应到数据库中，当两个或多个任务中，如果每个任务锁定了其他任务试图锁定的资源，此时会造成这些任务阻塞，从而出现死锁；这些资源可能是：单行(RID，堆中的单行)、索引中的键(KEY，行锁)、页(PAG，8KB)、区结构(EXT，连续的8页)、堆或B树(HOBT) 、表(TAB，包括数据和索引)、文件(File，数据库文件)、应用程序专用资源(APP)、元数据(METADATA)、分配单元(Allocation_Unit)、整个数据库(DB)。

     假设我们定义两个进程P1和P2，它们分别拥有资源R2和R1，但P1需要额外的资源R1恰好P2也需要R2资源，而且它们都不释放自己拥有的资源，这时资源和进程之间形成了一个环从而形成死锁。

 

图3死锁（图片源于wiki）

SQL Server中死锁排查：

      1.使用SQL Server中系统存储过程sp_who和sp_lock，可以查看当前数据库中阻塞进程的情况；

      首先我们在数据库中创建两个表Who和Lock分别用来存放阻塞和锁定的数据，SQL代码如下：

CREATE Table Who(spid int,
    ecid int,
    status nvarchar(50),
    loginname nvarchar(50),
    hostname nvarchar(50),
    blk int,
    dbname nvarchar(50),
    cmd nvarchar(50),
    request_ID int);

CREATE Table Lock(spid int,
    dpid int,
    objid int,
    indld int,
    [Type] nvarchar(20),
    Resource nvarchar(50),
    Mode nvarchar(10),
    Status nvarchar(10)
);

      接着我们要把阻塞和锁定数据分别存放到Who和Lock表中，SQL代码如下：

INSERT INTO Who
    -- Diagnose which process causing the block.
    EXEC sp_who active
INSERT INTO Lock
    -- Check which source has been locked.
    EXEC sp_lock

DECLARE @DBName nvarchar(20);
SET @DBName='YourDatabaseName'

SELECT Who.* FROM Who WHERE dbname=@DBName
SELECT Lock.* FROM Lock
    JOIN Who
        ON Who.spid=Lock.spid
            AND dbname=@DBName;

-- Displays the last statement sent from 
-- a client to an instance of Microsoft SQL Server.
DECLARE crsr Cursor FOR
    SELECT blk FROM Who WHERE dbname=@DBName AND blk<>0;
DECLARE @blk int;
open crsr;
FETCH NEXT FROM crsr INTO @blk;
WHILE (@@FETCH_STATUS = 0)
BEGIN;
    dbcc inputbuffer(@blk);
    FETCH NEXT FROM crsr INTO @blk;
END;
close crsr;
DEALLOCATE crsr;

-- Get the locked source.
SELECT Who.spid,hostname,objid,[type],mode,object_name(objid) as objName FROM Lock
    JOIN Who
        ON Who.spid=Lock.spid
            AND dbname=@DBName
    WHERE objid<>0;

      2.使用SQL Server Profiler分析死锁，将Deadlock graph事件类添加到跟踪。此事件类使用死锁涉及到的进程和对象的XML数据填充跟踪中的TextData数据列。SQL Server 事件探查器可以将XML文档提取到死锁XML(.xdl) 文件中，以后可在SQL Server Management Studio中查看该文件(下面将给出详细介绍)。

死锁的示例和解决方法

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

-- Note we use tempdb for testing.
USE tempdb

-- Create datatable in tempdb.
CREATE TABLE DlTable1 (DL1Id INT, DL1Name VARCHAR(20))
CREATE TABLE DlTable2 (DL2Id INT, DL2Name VARCHAR(20))


-- Insert multiple data into DlTable1 and DlTable2 in SQL Server 2005.
INSERT INTO DlTable1
SELECT 1, 'Deadlock'
UNION ALL
SELECT 2, 'JKhuang'
UNION ALL
SELECT 3, 'Test'
GO

INSERT INTO DlTable2
SELECT 1, 'Deadlock'
UNION ALL
SELECT 2, 'JacksonHuang'
UNION ALL
SELECT 3, 'Test'
GO


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

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

图4插入数据到表中

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

-- In query window 1.
USE tempdb
GO

-- Create transaction A.
BEGIN     TRANSACTION
            UPDATE DlTable1 SET DL1Name = 'Uplock' WHERE DL1Id = 2
            -- Delay 23 second.
            WAITFOR DELAY '00:00:23'
            UPDATE DlTable2 SET DL2Name = 'Downlock' WHERE DL2Id = 2
ROLLBACK TRANSACTION

-- In query window 2.
USE tempdb
GO

-- Create transaction B.
BEGIN     TRANSACTION
            UPDATE DlTable2 SET DL2Name = 'Downlock' WHERE DL2Id = 2
            -- Delay 23 second.
            WAITFOR DELAY '00:00:23'
            UPDATE DlTable1 SET DL1Name = 'Uplock' WHERE DL1Id = 2
ROLLBACK TRANSACTION

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

图5事务执行结果

      运行完上面的两个查询后，我们发现其中一个事务执行失败，系统提示该进程和另一个进程发生死锁，而另一个事务执行成功，这是由于SQL Server自动选择一个事务作为死锁牺牲品。

      既然发生了死锁，那么究竟是哪个资源被锁定了呢？现在我们通过死锁排除方法一来查看具体是哪个资源被锁定。

      现在我们重新执行事务A和B，接着使用死锁排除方法一查看更新事务具体使用到的锁。

图6更新操作使用的锁

      通过上图我们知道，首先事务A给表DlTable1下了行排他锁（RID X），然后在下页意向更新锁（PAG IX），最后给整个DlTable1表下了表意向更新锁（TAB IX）；事务B的使用的锁也是一样的。

      事务A拥有DL1Id = 2行排他锁（RID X）同时去请求DL2Id = 2的行排他锁（RID X），但我们知道事务B已经拥有DL2Id = 2的行排他锁（RID X），而且去请求DL1Id = 2行排他锁（RID X），由于行排他锁和行排他锁是冲突的所以导致死锁。

图7锁的兼容性

      前面我们介绍了使用sp_lock查看更新操作时SQL Server使用的锁（行锁、页锁和表锁），现在我们在更新操作后查询操作，SQL代码如下：

      现在我们使用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 Database DATABASENAME SET 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。

1.1.3 总结

      本文主要介绍了什么是死锁、怎样导致了死锁和死锁的解决方法，正如我们可以看到，由于导致死锁的原因很多，所以死锁的解决方法不尽相同，首先我们必须明确死锁发生的地方，例如进程为了争夺哪类资源导致死锁的，这时我们可以考虑使用Profiler工具进行跟踪查询；在清楚死锁发生的地方后，我们要检查一下查询是否考虑周到了，可以根据以上的方法优化查询语句。

参考

http://msdn.microsoft.com/zh-cn/library/ms174313.aspx

http://www.simple-talk.com/sql/learn-sql-server/how-to-track-down-deadlocks-using-sql-server-2005-profiler/

http://www.cnblogs.com/happyhippy/

[作者]： JK_Rush [出处]： http://www.cnblogs.com/rush/