SQLSERVER 死锁标志

最开始做DBA的时候，整天死锁到头痛1222，至今都能回想到这个错误窗口；

死锁定义：死锁是指在一组进程中的各个进程均占有不会释放的资源，但因互相申请被其他进程所站用不会释放的资源而处于的一种永久等待状态。

四个必要条件：

互斥条件(Mutual exclusion)：资源不能被共享，只能由一个进程使用。
请求与保持条件(Hold and wait)：已经得到资源的进程可以再次申请新的资源。
非剥夺条件(No pre-emption)：已经分配的资源不能从相应的进程中被强制地剥夺。
循环等待条件(Circular wait)：系统中若干进程组成环路，该环路中每个进程都在等待相邻进程正占用的资源。

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

遇到死锁，先看下sp_who 和 sp_lock

Trace Flag  1204 和1222

这两个跟踪标记都是将死锁写到错误日志中，不过1204是以文本格式进行，而1222是以XML格式保存。可以通过

sp_readerrorlog查看日志

DBCC TRACEON (3605,1204,1222,-1)

3605 将DBCC结果输出到错误日志；

1204、1222  返回锁资源和类型；

-1    全局方式打开指定追踪标记；

重启后失效，需重新创建；

也可以在引擎启动参数中加进去

演示下错误日志报告：

首先创建两个表：

脚本很简单：

CREATE TABLE A
(
ID INT,
NAME CHAR(4)
);
CREATE TABLE B
(
ID INT,
NAME CHAR(4)
);

INSERT INTO A VALUES (1,'KING')
INSERT INTO A VALUES (2,'KING')
INSERT INTO A VALUES (3,'KING')

INSERT INTO B VALUES (1,'KING')
INSERT INTO B VALUES (2,'KING')
INSERT INTO B VALUES (3,'KING')

分别开两个窗口：

开启两个事物（51）

BEGIN TRAN T1
UPDATE A SET NAME='QUE' WHERE ID=1
WAITFOR DELAY '00:00:15'
SELECT * FROM B 

ROLLBACK TRAN T1

开启第二个窗口：（56）

BEGIN TRAN T2
UPDATE B SET NAME='QUE' WHERE ID=2
WAITFOR DELAY '00:00:15'
SELECT * FROM A

ROLLBACK TRAN T2

 

SP_READERRORLOG读取错误编号：

 

解决亦可以加with (nolock)隔离级别，不过会产生脏读；