sql server 关于死锁的总结

deadlocks（死锁）

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

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

一种情形，此时执行程序中两个或多个线程发生永久堵塞（等待），每个线程都在等待被其他线程占用并堵塞了的资源。例如，如果线程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]
GO
/**//********************************************************
//  说明 : 查看数据库里阻塞和死锁情况
********************************************************/
use master
go
create procedure sp_who_lock
as
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 ....