`
xufei0110
  • 浏览: 110923 次
  • 性别: Icon_minigender_1
  • 来自: 大连
社区版块
存档分类
最新评论

数据库 死锁的解决(转)

阅读更多

http://www.waydu.com/blog/5589 

 

这个是我转过来的,之前也不知道 查询也会出现死锁。

 

其实所有的死锁最深层的原因就是一个:资源竞争 表现一:
    一个用户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系统时,应该根据应用系统的具体情况,依据上述原则对系统分别优化,为开发一套高效、可靠的应用系统打下良好的基础。   

============
--转 
  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里,对整个系统的性能提高都很有帮助

==========================
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

/*--处理死锁

查看当前进程,或死锁进程,并能自动杀掉死进程

因为是针对死的,所以如果有死锁进程,只能查看死锁进程
当然,你可以通过参数控制,不管有没有死锁,都只查看死锁进程

感谢: caiyunxia,jiangopen 两位提供的参考信息

--邹建 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<=@count
  begin
  select @spid=进程ID,@标志=标志 from #t where id=@i
  insert #t1 exec( dbcc inputbuffer( +@spid+ ) )
  if @标志= 死锁的进程  exec( kill  +@spid)
  set @i=@i+1
  end
end
else
  while @i<=@count
  begin
  select @s= dbcc inputbuffer( +cast(进程ID as varchar)+ )  from #t where id=@i
  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

分享到:
评论

相关推荐

    数据库死锁-解决死锁问题的三种办法

    总之,解决数据库死锁问题的三种方法各有优缺点,实际应用中应根据具体情况和需求选择最适合的策略。预防死锁虽能有效避免死锁的发生,但可能限制了资源的利用率;检测死锁虽然灵活性较高,但可能会增加系统的复杂性...

    sql数据库死锁查询工具

    本文将深入探讨“sql数据库死锁查询工具”及其在解决数据库死锁和阻塞过程中的作用。 **1. SQL死锁** SQL死锁是指两个或多个事务在执行过程中,因争夺资源而造成的一种相互等待的现象,若无外力干涉,它们都将无法...

    数据库 死锁的解决

    ### 数据库死锁解决方案详解 #### 一、死锁的本质及常见表现形式 在数据库管理领域,死锁问题一直是困扰开发者的重要难题之一。死锁的根本原因在于资源竞争,特别是当两个或多个进程试图以不同的顺序锁定同一组...

    查看数据库死锁信息

    标题所提及的"查看数据库死锁信息"是一个至关重要的任务,因为死锁可能导致数据库性能下降,甚至影响到服务的正常运行。当出现"Deadlock found when trying to get to lock; try restarting transaction"这样的错误...

    oracle数据库解决死锁

    4. 使用 Oracle 提供的死锁解决工具:Oracle提供了多种工具来解决死锁问题,例如Oracle Enterprise Manager、Oracle SQL Developer等。 在使用PL/SQL语句解决死锁问题时,需要注意以下几点: * 需要了解死锁的原因...

    较实用的ORACLE数据库死锁查杀

    ### ORACLE数据库死锁查杀方法详解 #### 一、引言 在Oracle数据库系统中,当两个或多个事务在等待对方释放资源时,就会出现死锁现象。死锁不仅会降低系统的整体性能,还可能导致某些重要事务长时间无法完成,严重...

    HIS系统SQL Server数据库死锁问题的分析与解决.pdf

    HIS系统SQL Server数据库死锁问题的分析与解决 SQL Server数据库是HIS系统中非常重要的一部分,负责存储和管理大量的医疗数据。然而,在实际应用中,SQL Server数据库可能会出现死锁问题,导致系统性能下降, thậm...

    解决Sybase数据库死锁

    ### 解决Sybase数据库死锁 #### Sybase数据库死锁概述 死锁是数据库管理系统中一个常见的问题,尤其是在高并发的环境下更为突出。Sybase数据库中的死锁通常发生在两个或多个事务试图锁定相同的资源(如表或记录)时...

    MYSQL 数据库死锁

    MySQL数据库死锁是数据库系统中常见的问题,它发生在两个或多个事务在执行过程中,因争夺资源而造成的一种相互等待的现象,若无外力干涉它们将无法继续执行。了解和处理死锁是数据库管理员和开发人员必备的技能。...

    数据库死锁检测工具

    为了解决这个问题,"数据库死锁检测工具"应运而生,它能够帮助数据库管理员识别并解决这种现象,确保数据库系统的稳定运行。 死锁的四个必要条件: 1. 互斥:一个资源在任何时候只能被一个事务使用,其他事务必须...

    Oracle数据库死锁查询语句

    Oracle数据库死锁是数据库系统中常见的问题,它发生在两个或多个事务在执行过程中,因争夺资源而造成的一种相互等待的现象。当这种情况发生时,如果没有外力干预,这些事务都将无法继续执行,形成一种僵局。了解如何...

    查询SQL server数据库死锁存储过程

    ### SQL Server 数据库死锁查询存储过程解析 #### 标题与描述理解 标题与描述均指出本篇讨论的主题是“查询SQL Server数据库死锁存储过程”。这表明文章旨在介绍一个用于检测SQL Server环境中发生的死锁现象的...

    mysql死锁的一些案例

    MySQL数据库在处理并发事务时,可能会遇到一种特殊的情况,即死锁。死锁是指两个或多个事务在执行过程中,因争夺资源而造成的一种相互等待的现象,若无外力干涉它们将无法继续执行。死锁是数据库系统中常见的问题,...

    各种类型数据库的死锁问题

    数据库死锁问题解决方案 在实际开发中,数据库死锁问题是一个常见的问题,会对系统的性能和吞吐量产生明显的影响。为了解决这个问题,需要从多方面进行考虑。本文将从数据库死锁的定义、类型、解决方案等方面进行...

    并发访问ORACLE数据库的数据死锁分析和解决措施.pdf

    本文将深入探讨并发访问Oracle数据库时数据死锁的原因,并提出有效的分析和解决措施,以期为相关技术人员提供专业指导和参考文献。 首先,要理解数据死锁的概念。在关系型数据库的多个事务中,若每个事务都在等待...

    数据库死锁案例

    ### 数据库死锁案例 #### 一、数据库死锁概述 数据库死锁是数据库系统中一个常见的问题,尤其是在并发环境中,多个事务同时访问共享资源时容易出现。死锁发生时,涉及的事务会陷入互相等待的状态,即每个事务都在...

    数据库死锁

    ### 数据库死锁详解 #### 一、数据库死锁概念 数据库死锁是数据库系统中一个重要的并发控制问题。在多用户共享数据的环境中,为了保证数据的一致性和准确性,通常会采用锁定机制来实现对数据资源的访问控制。然而...

    Mybatis update数据库死锁之获取数据库连接池等待

    【Mybatis更新数据库死锁与获取数据库连接池等待】是一个常见的技术问题,涉及到数据库事务处理、并发控制以及数据库连接管理。以下将详细解释这两个问题及其解决方案。 **1. MySQL数据库死锁** 数据库死锁通常...

    db2死锁问题分析及解决方案

    ### DB2死锁问题分析及解决方案 #### 一、引言 在数据库管理与维护过程中,死锁问题是一个常见的挑战,特别是在使用IBM DB2这样的大型关系型数据库管理系统时。本文将详细探讨DB2中死锁问题的分析方法及有效的解决...

Global site tag (gtag.js) - Google Analytics