- 回滚,而回滚会取消事务执行的所有工作。
- 由于死锁时回滚而由应用程序重新提交。
- 按同一顺序访问对象。
- 避免事务中的用户交互。
- 保持事务简短并在一个批处理中。
- 使用低隔离级别。
- 使用绑定连接。
go
createproceduresp_who_lock
as
begin
declare@spidint,@blint,
@intTransactionCountOnEntryint,
@intRowcountint,
@intCountPropertiesint,
@intCounterint
createtable#tmp_lock_who(
idintidentity(1,1),
spidsmallint,
blsmallint)
IF@@ERROR<>0RETURN@@ERROR
insertinto#tmp_lock_who(spid,bl)select0,blocked
from(select*fromsysprocesseswhereblocked>0)a
wherenotexists(select*from(select*fromsysprocesseswhereblocked>0)b
wherea.blocked=spid)
unionselectspid,blockedfromsysprocesseswhereblocked>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
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@sqlnvarchar(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