sql server的死锁及处理方法.docx
- 文档编号:14715133
- 上传时间:2023-06-26
- 格式:DOCX
- 页数:39
- 大小:28.82KB
sql server的死锁及处理方法.docx
《sql server的死锁及处理方法.docx》由会员分享,可在线阅读,更多相关《sql server的死锁及处理方法.docx(39页珍藏版)》请在冰点文库上搜索。
sqlserver的死锁及处理方法
【转】处理sqlserver的死锁
--第一篇
--检测死锁
--如果发生死锁了,我们怎么去检测具体发生死锁的是哪条SQL语句或存储过程?
--这时我们可以使用以下存储过程来检测,就可以查出引起死锁的进程和SQL语句。
SQLServer自带的系统存储过程sp_who和sp_lock也可以用来查找阻塞和死锁,但没有这里介绍的方法好用。
usemaster
go
createproceduresp_who_lock
as
begin
declare@spidint,@blint,
@intTransactionCountOnEntry int,
@intRowcount int,
@intCountProperties int,
@intCounter int
createtable#tmp_lock_who(
idintidentity(1,1),
spidsmallint,
blsmallint)
IF@@ERROR<>0RETURN@@ERROR
insertinto#tmp_lock_who(spid,bl)select 0,blocked
from(select*fromsysprocesseswhere blocked>0)a
wherenotexists(select*from(select*fromsysprocesseswhere blocked>0)b
wherea.blocked=spid)
unionselectspid,blockedfromsysprocesseswhere blocked>0
IF@@ERROR<>0RETURN@@ERROR
--找到临时表的记录数
select @intCountProperties=Count(*),@intCounter=1
from#tmp_lock_who
IF@@ERROR<>0RETURN@@ERROR
if@intCountProperties=0
select'现在没有阻塞和死锁信息'asmessage
--循环开始
while@intCounter<=@intCountProperties
begin
--取第一条记录
select @spid=spid,@bl=bl
from#tmp_lock_whowhereId=@intCounter
begin
if@spid=0
select'引起数据库死锁的是:
'+CAST(@blASVARCHAR(10))+'进程号,其执行的SQL语法如下'
else
select'进程号SPID:
'+CAST(@spidASVARCHAR(10))+'被'+'进程号SPID:
'+CAST(@blASVARCHAR(10))+'阻塞,其当前进程执行的SQL语法如下'
DBCCINPUTBUFFER(@bl)
end
--循环指针下移
set@intCounter=@intCounter+1
end
droptable#tmp_lock_who
return0
end
--杀死锁和进程
--如何去手动的杀死进程和锁?
最简单的办法,重新启动服务。
但是这里要介绍一个存储过程,通过显式的调用,可以杀死进程和锁。
usemaster
go
ifexists(select*fromdbo.sysobjectswhereid=object_id(N'[dbo].[p_killspid]')andOBJECTPROPERTY(id,N'IsProcedure')=1)
dropprocedure[dbo].[p_killspid]
GO
createprocp_killspid
@dbnamevarchar(200) --要关闭进程的数据库名
as
declare@sql nvarchar(500)
declare@spidnvarchar(20)
declare#tbcursorfor
selectspid=cast(spidasvarchar(20))frommaster..sysprocesseswheredbid=db_id(@dbname)
open#tb
fetchnextfrom#tbinto@spid
while@@fetch_status=0
begin
exec('kill'+@spid)
fetchnextfrom#tbinto@spid
end
close#tb
deallocate#tb
go
--用法
execp_killspid 'newdbpy'
--查看锁信息
--如何查看系统中所有锁的详细信息?
在企业管理管理器中,我们可以看到一些进程和锁的信息,这里介绍另外一种方法。
--查看锁信息
createtable#t(req_spidint,obj_namesysname)
declare@snvarchar(4000)
,@ridint,@dbnamesysname,@idint,@objnamesysname
declaretbcursorfor
selectdistinctreq_spid,dbname=db_name(rsc_dbid),rsc_objid
frommaster..syslockinfowherersc_typein(4,5)
opentb
fetchnextfromtbinto@rid,@dbname,@id
while@@fetch_status=0
begin
set@s='select@objname=namefrom['+@dbname+']..sysobjectswhereid=@id'
execsp_executesql@s,N'@objnamesysnameout,@idint',@objnameout,@id
insertinto#tvalues(@rid,@objname)
fetchnextfromtbinto@rid,@dbname,@id
end
closetb
deallocatetb
select进程id=a.req_spid
,数据库=db_name(rsc_dbid)
,类型=casersc_typewhen1then'NULL资源(未使用)'
when2then'数据库'
when3then'文件'
when4then'索引'
when5then'表'
when6then'页'
when7then'键'
when8then'扩展盘区'
when9then'RID(行ID)'
when10then'应用程序'
end
,对象id=rsc_objid
,对象名=b.obj_name
,rsc_indid
frommaster..syslockinfoaleftjoin#tbona.req_spid=b.req_spid
go
droptable#t
--第二篇
------------------版本1----------------------------------
--
sqlserver解除死锁
ifexists(select*fromdbo.sysobjectswhereid=object_id(N'[dbo].[p_lockinfo]')andOBJECTPROPERTY(id,N'IsProcedure')=1)
dropprocedure[dbo].[p_lockinfo]
GO
SETQUOTED_IDENTIFIERON
GO
SETANSI_NULLSON
GO
/*--处理死锁
查看当前进程,或死锁进程,并能自动杀掉死进程
因为是针对死的,所以如果有死锁进程,只能查看死锁进程
当然,你可以通过参数控制,不管有没有死锁,都只查看死锁进程
感谢:
caiyunxia,jiangopen两位提供的参考信息
--邹建2004.4--*/
/*--调用示例
execp_lockinfo
--*/
createprocp_lockinfo
@kill_lock_spidbit=1,--是否杀掉死锁的进程,1杀掉,0仅显示
@show_spid_if_nolockbit=1--如果没有死锁的进程,是否显示正常进程信息,1显示,0不显示
as
declare@countint,@snvarchar(1000),@iint
selectid=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#tfrom(
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
frommaster..sysprocessesajoin(
selectblockedfrommaster..sysprocessesgroupbyblocked
)bona.spid=b.blockedwherea.blocked=0
unionall
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
frommaster..sysprocessesawhereblocked<>0
)aorderbys1,s2
select@count=@@rowcount,@i=1
if@count=0and@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
frommaster..sysprocesses
set@count=@@rowcount
end
if@count>0
begin
createtable#t1(idintidentity(1,1),anvarchar(30),bInt,EventInfonvarchar(255))
if@kill_lock_spid=1
begin
declare@spidvarchar(10),@标志varchar(10)
while@i<=@count
begin
select@spid=进程ID,@标志=标志from#twhereid=@i
insert#t1exec('dbccinputbuffer('+@spid+')')
if@标志='死锁的进程'exec('kill'+@spid)
set@i=@i+1
end
end
else
while@i<=@count
begin
select@s='dbccinputbuffer('+cast(进程IDasvarchar)+')'from#twhereid=@i
insert#t1exec(@s)
set@i=@i+1
end
selecta.*,进程的SQL语句=b.EventInfo
from#tajoin#t1bona.id=b.id
end
GO
SETQUOTED_IDENTIFIEROFF
GO
SETANSI_NULLSON
GO
----------版本2:
我改写的(KILL死锁时间超过15秒的死锁进程---------------
/*
execp_lockinfo0,1;
*/
alterprocp_lockinfo
@kill_lock_spidbit=1,--是否杀掉死锁的进程,1杀掉,0仅显示
@show_spid_if_nolockbit=1--如果没有死锁的进程,是否显示正常进程信息,1显示,0不显示
as
declare@countint,@snvarchar(1000),@iint
selectid=identity(int,1,1),标志,
进程ID=spid,线程ID=kpid,块进程ID=blocked,数据库ID=dbid,
数据库名=db_name(dbid),用户ID=uid,用户名=loginame,累计CPU时间=cpu,等待时间=waittime,
登陆时间=login_time,打开事务数=open_tran,进程状态=status,
工作站名=hostname,应用程序名=program_name,工作站进程ID=hostprocess,
域名=nt_domain,网卡地址=net_address
into#tfrom(
select标志='死锁的进程',
spid,kpid,a.blocked,dbid,uid,loginame,cpu,waittime,login_time,open_tran,
status,hostname,program_name,hostprocess,nt_domain,net_address,
s1=a.spid,s2=0
frommaster..sysprocessesajoin(
selectblockedfrommaster..sysprocessesgroupbyblocked
)bona.spid=b.blockedwherea.blocked=0
unionall
select'|_牺牲品_>',
spid,kpid,blocked,dbid,uid,loginame,cpu,waittime,login_time,open_tran,
status,hostname,program_name,hostprocess,nt_domain,net_address,
s1=blocked,s2=1
frommaster..sysprocessesawhereblocked<>0
)aorderbys1,s2
select@count=@@rowcount,@i=1
if@count=0and@show_spid_if_nolock=1
begin
insert#t
select标志='正常的进程',
spid,kpid,blocked,dbid,db_name(dbid),uid,loginame,cpu,waittime,login_time,
open_tran,status,hostname,program_name,hostprocess,nt_domain,net_address
frommaster..sysprocesses
set@count=@@rowcount
end
if@count>0
begin
createtable#t1(idintidentity(1,1),anvarchar(30),bInt,EventInfonvarchar(4000))
if@kill_lock_spid=1
begin
declare@spidvarchar(10),@标志varchar(10),@等待时间int
while@i<=@count
begin
select@spid=进程ID,@标志=标志,@等待时间=等待时间from#twhereid=@i
insert#t1exec('dbccinputbuffer('+@spid+')')
if@标志='死锁的进程'and@等待时间>=15000exec('kill'+@spid)
set@i=@i+1
end
end
else
while@i<=@count
begin
select@s='dbccinputbuffer('+cast(进程IDasvarchar)+')'from#twhereid=@i
insert#t1exec(@s)
set@i=@i+1
end
selecta.*,进程的SQL语句=b.EventInfo
from#tajoin#t1bona.id=b.id
end
GO
SETQUOTED_IDENTIFIEROFF
GO
SETANSI_NULLSON
GO
====================================================================================================
锁知识
未整理。
--1
虽然不能完全避免死锁,但可以使死锁的数量减至最少。
将死锁减至最少可以增加事务的吞吐量并减少系统开销,因为只有很少的事务:
回滚,而回滚会取消事务执行的所有工作。
由于死锁时回滚而由应用程序重新提交。
下列方法有助于最大限度地降低死锁:
按同一顺序访问对象。
避免事务中的用户交互。
保持事务简短并在一个批处理中。
使用低隔离级别。
使用绑定连接。
按同一顺序访问对象
如果所有并发事务按同一顺序访问对象,则发生死锁的可能性会降低。
例如,如果两个并发事务获得Supplier表上的锁,然后获得Part表上的锁,则在其中一个事务完成之前,另一个事务被阻塞在Supplier表上。
第一个事务提交或回滚后,第二个事务继续进行。
不发生死锁。
将存储过程用于所有的数据修改可以标准化访问对象的顺序。
避免事务中的用户交互
避免编写包含用户交互的事务,因为运行没有用户交互的批处理的速度要远远快于用户手动响应查询的速度,例如答复应用程序请求参数的提示。
例如,如果事务正在等待用户输入,而用户去吃午餐了或者甚至回家过周末了,则用户将此事务挂起使之不能完成。
这样将降低系统的吞吐量,因为事务持有的任何锁只有在事务提交或回滚时才会释放。
即使不出现死锁的情况,访问同一资源的其它事务也会被阻塞,等待该事务完成。
保持事务简短并在一个批处理中
在同一数据库中并发执行多个需要长时间运行的事务时通常发生死锁。
事务运行时间越长,其持有排它锁或更新锁的时间也就越长,从而堵塞了其它活动并可能导致死锁。
保持事务在一个批处理中,可以最小化事务的网络通信往返量,减少完成事务可能的延迟并释放锁。
使用低隔离级别
确定事务是否能在更低的隔离级别上运行。
执行提交读允许事务读取另一个事务已读取(未修改)的数据,而不必等待第一个事务完成。
使用较低的隔离级别(例如提交读)而不使用较高的隔离级别(例如可串行读)可以缩短持有共享锁的时间,从而降低了锁定争夺。
使用绑定连接
使用绑定连接使同一应用程序所打开的两个或多个连接可以相互合作。
次级连接所获得的任何锁可以象由主连接获得的锁那样持有,反之亦然,因此不会相互阻塞
检测死锁
如果发生死锁了,我们怎么去检测具体发生死锁的是哪条SQL语句或存储过程?
这时我们可以使用以下存储过程来检测,就可以查出引起死锁的进程和SQL语句。
SQLServer自带的系统存储过程sp_who和sp_lock也可以用来查找阻塞和死锁,但没有这里介绍的方法好用。
usemaster
go
createproceduresp_who_lock
as
begin
declare@spidint,@blint,
@intTransactionCountOnEntry int,
@intRowcount int,
@intCountProperties int,
@intCounter int
createtable#tmp_lock_who(
idintidentity(1,1),
spidsmallint,
blsmallint)
IF@@ERROR<>0RETURN@@ERROR
insertinto#tmp_lock_who(spid,bl)select 0,blocked
from(select*fromsysprocesseswhere blocked>0)a
wherenotexists(select*from(select*fromsysprocesseswhere blocked>0)b
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- sql server的死锁及处理方法 server 死锁 处理 方法
![提示](https://static.bingdoc.com/images/bang_tan.gif)