sql server 死锁总结

昨天去面试，考官问了一个问题：如何处理存储过程中的死锁？结果没答上来，工资减500...

deadlocks（死锁）

所谓死锁<DeadLock>: 是指两个或两个以上的进程在执行过程中,因争夺资源而造成的一种互相等待的现象,若无外力作用,它们都将无法推进下去.此时称系统处于死锁状态或系统产生了死锁,这些永远在互相等待的进程称为死锁进程.

由于资源占用是互斥的，当某个进程提出申请资源后，使得有关进程在无外力协助下，永远分配不到必需的资源而无法继续运行，这就产生了一种特殊现象死锁。

一种情形，此时执行程序中两个或多个线程发生永久堵塞（等待），每个线程都在等待被其他线程占用并堵塞了的资源。例如，如果线程A锁住了记录1并等待记录2，而线程B锁住了记录2并等待记录1，这样两个线程就发生了死锁现象。

计算机系统中,如果系统的资源分配策略不当，更常见的可能是程序员写的程序有错误等，则会导致进程因竞争资源不当而产生死锁的现象。

产生死锁的原因主要是：

（1）因为系统资源不足。

（2）进程运行推进的顺序不合适。

（3）资源分配不当等。

如果系统资源充足，进程的资源请求都能够得到满足，死锁出现的可能性就很低，否则就会因争夺有限的资源而陷入死锁。其次，进程运行推进顺序与速度不同，也可能产生死锁

产生死锁的四个必要条件：

（1）互斥条件：一个资源每次只能被一个进程使用。

（2）请求与保持条件：一个进程因请求资源而阻塞时，对已获得的资源保持不放。

（3）不剥夺条件:进程已获得的资源，在末使用完之前，不能强行剥夺。

（4）循环等待条件:若干进程之间形成一种头尾相接的循环等待资源关系。这四个条件是死锁的必要条件，只要系统发生死锁，这些条件必然成立，而只要上述条件之一不满足，就不会发生死锁。

死锁的解决：预防和解除

1。死锁的预防：1）一次封锁法：就是要求每个事务必须一次将所有要使用的数据全部加锁，否则就不能继续执行。但是出现这样的问题：因为一次给全部数据加锁，从而降低系统的并发度，同时数据库中数据是不断变化的，原来不要求封锁的数据在执行过程中可能会变成封锁对象。2）顺序封锁法，预先对数据对象规定一个封锁顺序，所有事务都按这个顺序实行封锁，问题：数据库中数据对象多，且不断的变化，维护困难，成本高；事务的封锁请求可以随着事务的执行而动态的决定。

2。检测死锁

企业管理器－>可以在[管理]->[当前活动] 里看到阻塞信息(有时SQL Server企业管理器会因为锁太多而没有响应).

设定跟踪1204:

USE MASTER
DBCC TRACEON (1204,-1)

显示当前启用的所有跟踪标记的状态:

DBCC TRACESTATUS(-1)

取消跟踪1204:

DBCC TRACEOFF (1204,-1)

在设定跟踪1204后,会在数据库的日志文件里显示SQL Server数据库死锁时一些信息。但那些信息很难看懂,需要对照SQL Server联机丛书仔细来看。根据PAG锁要找到相关数据库表的方法:

DBCC TRACEON (3604)
DBCC PAGE (db_id,file_id,page_no)
DBCC TRACEOFF (3604)

可以使用以下存储过程来检测，就可以查出引起死锁的进程和SQL语句。

写了一个系统存储过程sp_who_lock.sql。代码如下：

if exists ( select * from dbo.sysobjects

where id = object_id (N ' [dbo].[sp_who_lock] ' )

and OBJECTPROPERTY (id, N ' IsProcedure ' ) = 1 )

drop procedure [ dbo ] . [ sp_who_lock ]

/********************************************************

// 说明 : 查看数据库里阻塞和死锁情况

********************************************************/

use master

create procedure sp_who_lock

begin

declare @spid int , @bl int ,

@intTransactionCountOnEntry int ,

@intRowcount int ,

@intCountProperties int ,

@intCounter int

create table #tmp_lock_who (

id int identity ( 1 , 1 ),

spid smallint ,

bl smallint )

IF @@ERROR <> 0 RETURN @@ERROR

insert into #tmp_lock_who(spid,bl) select 0 ,blocked

from ( select * from sysprocesses where blocked > 0 ) a

where not exists ( select * from ( select * from sysprocesses

where blocked > 0 ) b

where a.blocked = spid)

union select spid,blocked from sysprocesses where blocked > 0

IF @@ERROR <> 0 RETURN @@ERROR

-- 找到临时表的记录数

select @intCountProperties = Count ( * ), @intCounter = 1

from #tmp_lock_who

IF @@ERROR <> 0 RETURN @@ERROR

if @intCountProperties = 0

select ' 现在没有阻塞和死锁信息 ' as message

-- 循环开始

while @intCounter <= @intCountProperties

begin

-- 取第一条记录

select @spid = spid, @bl = bl

from #tmp_lock_who where Id = @intCounter

begin

if @spid = 0

select ' 引起数据库死锁的是: ' + CAST ( @bl AS VARCHAR ( 10 ))

+ ' 进程号,其执行的SQL语法如下 '

else

select ' 进程号SPID： ' + CAST ( @spid AS VARCHAR ( 10 )) + ' 被 '

+ ' 进程号SPID： ' + CAST ( @bl AS VARCHAR ( 10 )) + ' 阻塞,其当前进程执行的SQL语法如下 '

DBCC INPUTBUFFER ( @bl )

end

-- 循环指针下移

set @intCounter = @intCounter + 1

end

drop table #tmp_lock_who

return 0

end

需要的时候直接调用:

sp_who_lock

就可以查出引起死锁的进程和SQL语句.

我们还可以设置锁的超时时间(单位是毫秒), 来缩短死锁可能影响的时间范围:

例如:

use master
seelct @@lock_timeout
set lock_timeout 900000
-- 15分钟
seelct @@lock_timeout

*********************************************************************************************************

常看到死锁的问题,一般都是KILL进程,但如果不查出引起死锁的原因,死锁会时常发生
可以通过查找引起死锁的的操作,就可以方便的解决死锁,现将日常解决问题的方法总结,也许对大家有帮助

1/死锁发生时,通过如下语法,查询出引起死锁的操作

use master
go
declare @spid int,@bl int
DECLARE s_cur CURSOR FOR
select 0 ,blocked
from (select * from sysprocesses where blocked>0 ) a
where not exists(select * from (select * from sysprocesses where blocked>0 ) b
where a.blocked=spid)
union select spid,blocked from sysprocesses where blocked>0
OPEN s_cur
FETCH NEXT FROM s_cur INTO @spid,@bl
WHILE @@FETCH_STATUS = 0
begin
if @spid =0
            select '引起数据库死锁的是: '+ CAST(@bl AS VARCHAR(10)) + '进程号,其执行的SQL语法如下'
else
            select '进程号SPID：'+ CAST(@spid AS VARCHAR(10))+ '被' + '进程号SPID：'+ CAST(@bl AS VARCHAR(10)) +'阻塞,其当前进程执行的SQL语法如下'
DBCC INPUTBUFFER (@bl )
FETCH NEXT FROM s_cur INTO @spid,@bl
end
CLOSE s_cur
DEALLOCATE s_cur

exec sp_who2
2/查找程序/数据库,此t_sql语法在什么地方使用
3/分析找到的,并解决问题

EG：

/*
-------------------------------------------------------
引起数据库死锁的是: 71进程号,其执行的SQL语法如下

EventType      Parameters EventInfo
-------------- ---------- ------------------------------------------------
Language Event 0
select * from test
insert test values(1,2)

（所影响的行数为 1 行）

DBCC 执行完毕。如果 DBCC 输出了错误信息，请与系统管理员联系。

------------------------------------------------------------------------------
进程号SPID：64被进程号SPID：71阻塞,其当前进程执行的SQL语法如下

EventType      Parameters EventInfo
-------------- ---------- ------------------------------------------------
Language Event 0
select * from test
insert test values(1,2)

（所影响的行数为 1 行）

DBCC 执行完毕。如果 DBCC 输出了错误信息，请与系统管理员联系。

------------------------------------------------------------------------------
进程号SPID：65被进程号SPID：64阻塞,其当前进程执行的SQL语法如下

EventType      Parameters EventInfo
-------------- ---------- --------------------------------------------------------------------------------------------------
Language Event 0          begin tran
select * from test with (holdlock)
waitfor time '12:00'
select * from test
commit

（所影响的行数为 1 行）

DBCC 执行完毕。如果 DBCC 输出了错误信息，请与系统管理员联系。

------------------------------------------------------------------------------
进程号SPID：73被进程号SPID：64阻塞,其当前进程执行的SQL语法如下

EventType      Parameters EventInfo
-------------- ---------- --------------------------------------------------------------------------------------------------
Language Event 0          begin tran
select * from test with (holdlock)
waitfor time '12:00'
select * from test
commit

（所影响的行数为 1 行）

DBCC 执行完毕。如果 DBCC 输出了错误信息，请与系统管理员联系。
*/

***************************************************************************************************

解决方法：

让用户（即先读后写类型的操作),在select 时就是用Update lock

语法如下：

 
  select 
  *
   from table1 with(updlock) where ....

原文链接：https://blog.csdn.net/mangodd/article/details/2489670