sQL死锁及解决方法

1、死锁--官方解释

当某组资源的两个或多个线程之间有循环相关性时，将发生死锁。

死锁是一种可能发生在任何多线程系统中的状态，而不仅仅发生在关系数据库管理系统中。多线程系统中的一个线程可能获取一个或多个资源（如锁）。如果正获取的资源当前为另一线程所拥有，则第一个线程可能必须等待拥有线程释放目标资源。这时就说等待线程在那个特定资源上与拥有线程有相关性。

如果拥有线程需要获取另外一个资源，而该资源当前为等待线程所拥有，则这种情形将成为死锁：在事务提交或回滚之前两个线程都不能释放资源，而且它们因为正等待对方拥有的资源而不能提交或回滚事务。例如，运行事务 1 的线程 T1 具有 Supplier 表上的排它锁。运行事务 2 的线程 T2 具有 Part 表上的排它锁，并且之后需要 Supplier 表上的锁。事务 2 无法获得这一锁，因为事务 1 已拥有它。事务 2 被阻塞，等待事务 1。然后，事务 1 需要 Part 表的锁，但无法获得锁，因为事务 2 将它锁定了。事务在提交或回滚之前不能释放持有的锁。因为事务需要对方控制的锁才能继续操作，所以它们不能提交或回滚。

说明  死锁经常与正常阻塞混淆。当一个事务锁定了另一个事务需要的资源，第二个事务等待锁被释放。默认情况下，SQL Server 事务不会超时（除非设置了 LOCK_TIMEOUT）。第二个事务被阻塞，而不是被死锁。有关更多信息，请参见自定义锁超时。

 

在该插图中，对于 Part 表锁资源，线程 T1 在线程 T2 上具有相关性。同样，对于 Supplier 表锁资源，线程 T2 在线程 T1 上具有相关性。因为这些相关性形成了一个循环，所以在线程 T1 和线程 T2 之间存在死锁。

2、如何检测死锁

如果发生死锁了，我们怎么去检测具体发生死锁的是哪条SQL语句或存储过程？

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

SQL Server自带的系统存储过程sp_who和sp_lock也可以用来查找阻塞和死锁

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

3、如何杀死锁和进程

（1）. 批处理文件重启SQL进程

@echo off

rem restartsqlserver.cmd

 

net stop mssqlserver

net start mssqlserver

 

echo 'unlocked'

 

（2）. 用T-SQL。

   一种方法是在每次执行前SET LOCK_TIMEOUT 微秒。时间结束后自动解锁。或者在sysprocesses表中找到block的spid，kill掉。

use master go if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[p_killspid]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[p_killspid] GO create proc p_killspid @dbname varchar(200)    --要关闭进程的数据库名 as       declare @sql  nvarchar(500)       declare @spid nvarchar(20)     declare #tb cursor for         select spid=cast(spid as varchar(20)) from master..sysprocesses where dbid=db_id(@dbname)     open #tb     fetch next from #tb into @spid     while @@fetch_status=0     begin           exec('kill '+@spid)         fetch next from #tb into @spid     end       close #tb     deallocate #tb go

 

总结

虽然不能完全避免死锁，但我们可以将死锁减至最少，并通过一定的方法来检测死锁。