【数据库死锁随笔】

1.什么叫死锁

死锁指的是在两个或两个以上不同的进程或线程中，由于存在共同资源的竞争或进程（或线程）间的通讯而导致各个线程间相互挂起等待，如果没有外力作用，最终会引发整个系统崩溃。

2.死锁产生的条件

a.资源独占

指多个事务在竞争同一个资源时存在互斥性，即在一段时间内某资源只由一个事务占用，也可叫独占资源（如行锁）。

b.相互获取锁条件

指在获取死锁时，必定存在一个互相获取锁的一个过程，也就是说持有锁A的事务a在获取锁B的同时，持有锁B的事务b在获取锁A，这样互相获取造成一个事务的堵塞，造成死锁。

c.请求保持

指在事务a中已经占有了锁A，但是又提出了新的锁B请求，这时锁B已经被其他事务b所占有，此时事务a会阻塞，但是又会对以获取的锁A保持不放。

d.不剥夺条件

指一个事务a中已经获得锁A，在未提交之前，不能被剥夺，只能在使用完后提交事务再自己释放。

3.如何去避免死锁

上面列举了四个死锁产生的条件，那么由逻辑可知，避免其中一条或者多条就可以避免产生死锁

a.避免出现循环：按同一顺序进行访问

b.减少资源持有时间，减少锁竞争：避免事务中的用户交互、保持事务简短并处于一个批处理中。

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

[^1]：当多个线程同时开启操作数据库中的数据时，数据库要能进行隔离操作，这么做为了保证获取数据的准确性
有关数据库事务隔离等级：***********************

d.使用基于行版本的隔离级别

SET ALLOW_SNAPSHOT_ISOLATION ON --事务可以指定 SNAPSHOT 事务隔离级别;
SET READ_COMMITTED_SNAPSHOT ON --指定 READ_COMMITTED 隔离级别的事务将使用行版本控制而不是锁定。默认情况下(没有开启此选项，没有加with nolock提示)，SELECT语句会对请求的资源加S锁(共享锁)；而开启了此选项后，SELECT不会对请求的资源加S锁。
[^2]：设置READ_COMMITTED_SNAPSHOT 选项时，数据库中只允许纯在执行ALTER DATABASE命令的链接，在其完成之前数据库中绝不允许有其他打开的链接，数据库不比一定要处于单用户模式中。

e.保持事务简短并处于一个批处理中（为了减少持有资源的时间）

f.使用绑定链接：

[^3]：绑定会话有利于在同一台服务器上的多个会话之剑协调操作。绑定会话允许一个或者多个会话共享相同的事务和锁——但是每个会话都会保留自己的事务隔离级别

在一个会话中开始事务后，调用exec sp_sp_getbindtoken @Token out来去的Token 然后传入另一个会话并执行 EXEC sp_bindsession @Token 来进行绑定

4.死锁的处理方法以及排查死锁

--使用sql的系统存储过程sp_who和sp_lock,可以查看房钱数据库中的锁情况

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)
);

--看哪个引起的阻塞 blk 
INSERT INTO #Who
    EXEC sp_who active

--看锁住了那个资源id，objid
INSERT INTO #Lock
    EXEC sp_lock

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

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

--最后发送到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;
***********************************
--锁定的资源
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;

DROP Table #Who;
DROP Table #Lock;

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

死锁的处理方法

临时解决方案（通用暂时解决方案）上述有一个查看所有id处于wait状态的一个sql，查找到对应id后用kill spid来干掉等待状态从而解决死锁

tip：此处只能作为一个临时的解决方法，最终还是要找到死锁的原因。

使用 set LOCK_TIMOUT timeout_period来设定锁请求超时（超时时间单位为毫秒），默认情况下 ，数据库没有超时期限（timeout_period）值为-1（即无期限等待），可以用SELECT @@LOCK_TIMEOUT 来查看该值，当其值为0时表示不执行等待，一有锁就返回消息。

“返回1205错误”

SQL内部基本上都有所监视器线程执行死锁检查，锁监视器，锁监视器对特定线程启动是说搜索是，会标识线程正在等待的资源，然后查找特定资源的所有者，并递归的积蓄执行对那些线程的死锁搜索，知道找到一个构成死锁条件的循环，检测到死锁后，数据库引擎会现在回滚开销最小的事务作为牺牲品，并返回1205错误，并释放出该作为牺牲品的事务所持有的锁，使其他的事务可以请求资源并继续运行。