sql server 怎么模拟死锁 sql server死锁原因

转载

mob6454cc747bda 2023-10-02 09:09:26

文章标签 sql server 怎么模拟死锁 sql 批处理 snapshot 竞争 文章分类 SQL Server 数据库

其实所有的死锁最深层的原因就是一个：资源竞争

表现一：

　　一个用户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中，最小锁为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系统时，应该根据应用系统的具体情况，依据上述原则对系统分别优化，为开发一套高效、可靠的应用系统打下良好的基础。
========================================================================

--查看数据库里阻塞和死锁情况   
   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  
   /***************************************************************************  
   //    创建  :  fengyu    邮件  :  maggiefengyu@tom.com    日期  :2004-04-30  
   //    修改  :  从http://www.csdn.net/develop/Read_Article.asp?id=26566学习到并改写      
   //    说明  :  查看数据库里阻塞和死锁情况  
   ***************************************************************************/  
   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

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

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

将死锁减至最少即可。

       . 按同一顺序访问对象。
      . 避免事务中的用户交互。
       . 保持事务简短并处于一个批处理中。
       . 使用较低的隔离级别。
       . 使用基于行版本控制的隔离级别。
       . 将 READ_COMMITTED_SNAPSHOT 数据库选项设置为 ON，使得已提交读事务使用行版本控制。
       . 使用快照隔离。
      . 使用绑定连接。

本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。