图片 3

处理死锁

——-node:2 部分显示的几个关键信息

 PAGE 7:1:5692366  (所在数据库ID 7, 1分区,5692366行数)

 Mode:U 锁的模式  更新锁

 RPC Event: Proc 远程调用

 SPID: 64  进程ID

Victim Resource Owner:
ResType:LockOwner Stype:'OR'Xdes:0x0000000C7A905D30 Mode: U SPID:64 BatchID:0 ECID:59 TaskProxy:(0x0000000E440AAFE0) Value:0x8d160240 Cost:(0/0)
deadlock-list
deadlock victim=process956f4c8
process-list
process id=process956f4c8 taskpriority=0 logused=0 waitresource=PAGE: 7:1:6229275 waittime=2034 ownerId=2988267079 transactionname=UPDATE 
lasttranstarted=2018-04-19T13:54:00.360 XDES=0xc7a905d30 lockMode=U schedulerid=24 kpid=1308 status=suspended spid=64 sbid=0 ecid=59 priority=0 trancount=0 
lastbatchstarted=2018-04-19T13:53:58.033 lastbatchcompleted=2018-04-19T13:53:58.033 clientapp=.Net SqlClient Data Provider hostname=VMSERVER76 hostpid=16328 
isolationlevel=read committed (2) xactid=2988267079 currentdb=7 lockTimeout=4294967295 clientoption1=671088672 clientoption2=128056
executionStack
frame procname=Test.dbo.proc_CnofStock line=108 stmtstart=9068 stmtend=9336 sqlhandle=0x03000700c503123601ba25019ca800000100000000000000
update dbo.pub_stock
set UpdateTime=GETDATE()
from pub_stock a
join PUB_PlatfromStocktemp b on a.GUID=b.StockGuid

   从上面的信息能看到kill 掉的是进程id是process956f4c8,

    进程spid=64

    lockMode=U 获取更新锁

    isolationlevel=read committed

    executionStack 执行的堆信息:

                  存储名  procname=Test.dbo.proc_CnofStock

                  语句    update dbo.pub_stock set UpdateTime=GETDATE()
  ..

    clientapp   发起事件的来源

   

 

–转  
/********************************************************  
//   创建 :   
//   日期 :  
//   修改 :   
//     
//   说明 : 查看数据库里阻塞和死锁情况  
********************************************************/ 
 
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

    GO  

    呵呵,解决死锁,光查出来没有多大用处,我原来也是用这个存储过程来清理死锁的  
      我解决死锁的方式主要用了:  
      1  优化索引  
      2  对所有的报表,非事务性的select  语句  在from  后都加了  with  (nolock)  语句  
      3  对所有的事务性更新尽量使用相同的更新顺序来执行  
      现在已解决了死锁的问题,希望能对你有帮助

    with  (nolock)的用法很灵活  可以说只要有  from的地方都可以加  with  (nolock)  标记来取消产生意象锁,这里  可以用在  delete  update,select  以及  inner  join  后面的from里,对整个系统的性能提高都很有帮助

    use master –必须在master数据库中创建
    go
    if exists (select * from dbo.sysobjects where id = object_id(N
    [dbo].[p_lockinfo] ) and OBJECTPROPERTY(id, N IsProcedure ) = 1)
    drop procedure [dbo].[p_lockinfo]
    GO
    /*–处理死锁
    查看当前进程,或死锁进程,并能自动杀掉死进程
    因为是针对死的,所以如果有死锁进程,只能查看死锁进程
    当然,你可以通过参数控制,不管有没有死锁,都只查看死锁进程
    –邹建 2004.4–*/
    /*–调用示例
    exec p_lockinfo
    –*/
    create proc p_lockinfo
    @kill_lock_spid bit=1, –是否杀掉死锁的进程,1 杀掉, 0 仅显示
    @show_spid_if_nolock bit=1
    –如果没有死锁的进程,是否显示正常进程信息,1 显示,0 不显示
    as
    declare @count int,@s nvarchar(1000),@i int
    select id=identity(int,1,1),标志,
    进程ID=spid,线程ID=kpid,块进程ID=blocked,数据库ID=dbid,
    数据库名=db_name(dbid),用户ID=uid,用户名=loginame,累计CPU时间=cpu,
    登陆时间=login_time,打开事务数=open_tran, 进程状态=status,
    工作站名=hostname,应用程序名=program_name,工作站进程ID=hostprocess,
    域名=nt_domain,网卡地址=net_address
    into #t from(
    select 标志=’死锁的进程’,
    spid,kpid,a.blocked,dbid,uid,loginame,cpu,login_time,open_tran,
    status,hostname,program_name,hostprocess,nt_domain,net_address,
    s1=a.spid,s2=0
    from master..sysprocesses a join (
    select blocked from master..sysprocesses group by blocked
    )b on a.spid=b.blocked where a.blocked=0
    union all
    select ‘|_牺牲品_>’,
    spid,kpid,blocked,dbid,uid,loginame,cpu,login_time,open_tran,
    status,hostname,program_name,hostprocess,nt_domain,net_address,
    s1=blocked,s2=1
    from master..sysprocesses a where blocked<>0
    )a order by s1,s2
    select @count=@@rowcount,@i=1
    if @count=0 and @show_spid_if_nolock=1
    begin
    insert #t
    select 标志=’正常的进程’,
    spid,kpid,blocked,dbid,db_name(dbid),uid,loginame,cpu,login_time,
    open_tran,status,hostname,program_name,hostprocess,nt_domain,net_address
    from master..sysprocesses
    set @count=@@rowcount
    end
    if @count>0
    begin
    create table #t1(id int identity(1,1),a nvarchar(30),b Int,EventInfo
    nvarchar(255))
    if @kill_lock_spid=1
    begin
    declare @spid varchar(10),@标志 varchar(10)
    while @i<[email==@count]=@count[/email]
    begin
       select @spid=进程ID,@标志=标志 from #t where
    [email=id=@i]id=@i[/email]
       insert #t1 exec(‘dbcc inputbuffer([email=]’+@spid+’)'[/email])
       if @标志=’死锁的进程’ exec(‘kill [email=]’+@spid[/email])
       set @i=@i+1
    end
    end
    else
    while @i<[email==@count]=@count[/email]
    begin
       select @s=’dbcc inputbuffer(‘+cast(进程ID as varchar)+’)’ from #t
    where [email=id=@i]id=@i[/email]
       insert #t1 exec(@s)
       set @i=@i+1
    end
    select a.*,进程的SQL语句=b.EventInfo
    from #t a join #t1 b on a.id=b.id
    end
    GO

——– node:1 部分显示的几个关键信息:

 PAGE 7:1:6229275  (所在数据库ID 7, 1分区, 6229275行数)

 Mode: IX  锁的模式  意向排它锁

 SPID: 219  进程ID

 Event: exec proc_PUB_StockDataImport  执行的存储过程名

其实所有的死锁最深层的原因就是一个:资源竞争 表现一:
    一个用户A 访问表A(锁住了表A),然后又访问表B
    另一个用户B 访问表B(锁住了表B),然后企图访问表A
   
这时用户A由于用户B已经锁住表B,它必须等待用户B释放表B,才能继续,好了他老人家就只好老老实实在这等了
    同样用户B要等用户A释放表A才能继续这就死锁了
解决方法:
    这种死锁是由于你的程序的BUG产生的,除了调整你的程序的逻辑别无他法
    仔细分析你程序的逻辑,
    1:尽量避免同时锁定两个资源
    2:
必须同时锁定两个资源时,要保证在任何时刻都应该按照相同的顺序来锁定资源.
  
表现二:
    用户A读一条纪录,然后修改该条纪录
    这是用户B修改该条纪录
    这里用户A的事务里锁的性质由共享锁企图上升到独占锁(for
update),而用户B里的独占锁由于A有共享锁存在所以必须等A释
放掉共享锁,而A由于B的独占锁而无法上升的独占锁也就不可能释放共享锁,于是出现了死锁。
    这种死锁比较隐蔽,但其实在稍大点的项目中经常发生。
解决方法:
    让用户A的事务(即先读后写类型的操作),在select 时就是用Update lock
    语法如下:

       
 记得以前客户在使用软件时,有偶发出现死锁问题,因为发生的时间不确定,不好做问题的重现,当时解决问题有点棘手了。

    select * from table1 with(updlock) where ….

在联机事务处理(OLTP)的数据库应用系统中,多用户、多任务的并发性是系统最重要的技术指标之一。为了提高并发性,目前大部分RDBMS都采用加锁技术。然而由于现实环境的复杂性,使用加锁技术又不可避免地产生了死锁问题。因此如何合理有效地使用加锁技术,最小化死锁是开发联机事务处理系统的关键。   
  死锁产生的原因   
  在联机事务处理系统中,造成死机主要有两方面原因。一方面,由于多用户、多任务的并发性和事务的完整性要求,当多个事务处理对多个资源同时访问时,若双方已锁定一部分资源但也都需要对方已锁定的资源时,无法在有限的时间内完全获得所需的资源,就会处于无限的等待状态,从而造成其对资源需求的死锁。 
 
  另一方面,数据库本身加锁机制的实现方法不同,各数据库系统也会产生其特殊的死锁情况。如在Sybase  SQL  Server  11中,最小锁为2K一页的加锁方法,而非行级锁。如果某张表的记录数少且记录的长度较短(即记录密度高,如应用系统中的系统配置表或系统参数表就属于此类表),被访问的频率高,就容易在该页上产生死锁。 
 
  几种死锁情况及解决方法   
  清算应用系统中,容易发生死锁的几种情况如下:      
  ●  不同的存储过程、触发器、动态SQL语句段按照不同的顺序同时访问多张表; 
    
  ●  在交换期间添加记录频繁的表,但在该表上使用了非群集索引(non-clustered); 
    
  ●  表中的记录少,且单条记录较短,被访问的频率较高;   
  ●  整张表被访问的频率高(如代码对照表的查询等)。   
  以上死锁情况的对应处理方法如下:   
  ●  在系统实现时应规定所有存储过程、触发器、动态SQL语句段中,对多张表的操作总是使用同一顺序。如:有两个存储过程proc1、proc2,都需要访问三张表zltab、z2tab和z3tab,如果proc1按照zltab、z2tab和z3tab的顺序进行访问,那么,proc2也应该按照以上顺序访问这三张表。 
 
  ●  对在交换期间添加记录频繁的表,使用群集索引(clustered),以减少多个用户添加记录到该表的最后一页上,在表尾产生热点,造成死锁。这类表多为往来账的流水表,其特点是在交换期间需要在表尾追加大量的记录,并且对已添加的记录不做或较少做删除操作。 
 
  ●  对单张表中记录数不太多,且在交换期间select或updata较频繁的表可使用设置每页最大行的办法,减少数据在表中存放的密度,模拟行级锁,减少在该表上死锁情况的发生。这类表多为信息繁杂且记录条数少的表。 
 
  如:系统配置表或系统参数表。在定义该表时添加如下语句:   
  with  max_rows_per_page=1   
  ●  在存储过程、触发器、动态SQL语句段中,若对某些整张表select操作较频繁,则可能在该表上与其他访问该表的用户产生死锁。对于检查账号是否存在,但被检查的字段在检查期间不会被更新等非关键语句,可以采用在select命令中使用at  isolation  read  uncommitted子句的方法解决。该方法实际上降低了select语句对整张表的锁级别,提高了其他用户对该表操作的并发性。在系统高负荷运行时,该方法的效果尤为显著。 
 
  例如:   
  select*from  titles  at  isolation  read  uncommitted   
  ●  对流水号一类的顺序数生成器字段,可以先执行updata流水号字段+1,然后再执行select获取流水号的方法进行操作。   
  小结   
  笔者对同城清算系统进行压力测试时,分别对采用上述优化方法和不采用优化方法的两套系统进行测试。在其他条件相同的情况下,相同业务笔数、相同时间内,死锁发生的情况如下: 
 
  采用优化方法的系统:  0次/万笔业务;      
  不采用优化方法的系统:50~200次/万笔业务。   
  所以,使用上述优化方法后,特别是在系统高负荷运行时效果尤为显著。总之,在设计、开发数据库应用系统,尤其是OLTP系统时,应该根据应用系统的具体情况,依据上述原则对系统分别优化,为开发一套高效、可靠的应用系统打下良好的基础。 

第一种是图形化监听:

  sqlserver –>工具–> sql server profiler  
登录后在跟踪属性中选择如下图:

 
 图片 1

   监听到的死锁图形如下图

 
  图片 2图片 3

        这里的描述大致是:有二个进程 一个进程ID是96, 另一个ID是348.  
系统自动kill 掉了进程ID:96,保留了进程ID:348 的事务Commit。

   上面死锁是由于批量更新出现PAG范围锁,
双方进程在同一分区索引资源上。ID96,348都请求想获取更新锁(U),各占排它锁(x)不释放,直到锁超时。

 

 

第二种是使用日志跟踪(errorlog)

      以全局方式打开指定的跟踪标记

        DBCC TRACEON(1222,-1)

       DBCC TRACEON(1204,-1) 

    使用  EXEC master..xp_readerrorlog 查看日志。
由于记录的死锁信息太多,贴出几个重点说下(红色加粗表示)

Deadlock encountered .... Printing deadlock information
Wait-for graph
NULL
Node:1 
PAGE: 7:1:6229275 CleanCnt:2 Mode:IX Flags: 0x3
Grant List 3:
Owner:0x00000004E99B7880 Mode: IX Flg:0x40 Ref:1 Life:02000000 SPID:219 ECID:0 XactLockInfo: 0x0000000575C7E970
SPID: 219 ECID: 0 Statement Type: UPDATE Line #: 84
Input Buf: Language Event: exec proc_PUB_StockDataImport
Requested by: 
ResType:LockOwner Stype:'OR'Xdes:0x0000000C7A905D30 Mode: U SPID:64 BatchID:0 ECID:59 TaskProxy:(0x0000000E440AAFE0) Value:0x8d160240 Cost:(0/0)
NULL

Node:2 
PAGE: 7:1:5692366 CleanCnt:2 Mode:U Flags: 0x3
Grant List 3:
Owner:0x0000000D12099B80 Mode: U Flg:0x40 Ref:0 Life:00000001 SPID:64 ECID:0 XactLockInfo: 0x000000136B4758F0
SPID: 64 ECID: 0 Statement Type: UPDATE Line #: 108
Input Buf: RPC Event: Proc [Database Id = 7 Object Id = 907150277]

现总结下查看死锁的常用二种方式:

最后总结   避免死锁的解决方法

         按同一顺序访问对象。

        优化索引,避免全表扫描,减少锁的申请数目.

        避免事务中的用户交互。

        使用基于行版本控制的隔离级别。

         将事务默认隔离级别的已提交读改成快照

         SET TRANSACTION ISOLATION LEVEL SNAPSHOT

       使用nolock去掉共享锁,但死锁发生在u锁或x锁上,则nolock不起作用

       升级锁颗粒度(页锁,表锁), 以阻塞还代替死锁