--version 2005
--捕捉在 read committed 情况下的死锁 防止牺牲死锁或无限等待
create proc deadlock_error_info(@sqltext varchar(8000)/*,@errorinfo varchar(1000) output*/) ---大语句容量建议用max
as
declare @sql varchar(8000),@rowcount int
select b.text,c.*,d.* from sys.dm_exec_connections as a
cross apply sys.dm_exec_sql_text(most_recent_sql_handle) as b
inner join sys.dm_tran_locks as c on a.session_id=c.request_session_id and request_status='wait'
inner join sys.dm_tran_locks as d on c.resource_database_id=d.resource_database_id
and c.resource_associated_entity_id=d.resource_associated_entity_id and d.request_status='GRANT'
and c.resource_description=d.resource_description
select @rowcount= @@rowcount
begin try
if @rowcount>0
begin
set lock_timeout 5000
exec(@sqltext)
end
else
begin
exec(@sqltext)
end
end try
begin catch
declare @error_message varchar(800)
if @rowcount>0
begin
select @error_message=error_message()
select @error_message
select b.text,c.*,d.* from sys.dm_exec_connections as a
cross apply sys.dm_exec_sql_text(most_recent_sql_handle) as b
inner join sys.dm_tran_locks as c on a.session_id=c.request_session_id and request_status='wait'
inner join sys.dm_tran_locks as d on c.resource_database_id=d.resource_database_id
and c.resource_associated_entity_id=d.resource_associated_entity_id and d.request_status='GRANT'
and c.resource_description=d.resource_description
end
else
begin
select @error_message=error_message()
select @error_message
end
end catch
go
--建立演示环境
--演示准备:
create table xuwangjin(id int)
go
insert into xuwangjin select 1
go
--session1
begin tran
insert into xuwangjin select 1
--session2
begin
select id from xuwangjin
--session3
exec deadlock_error_info @sqltext='select * from xuwangjin'
--2000 因特殊性无法捕捉error_inofr 故没写
捕捉在 read committed 情况下的死锁 防止牺牲死锁或无限等待
最新推荐文章于 2024-08-16 21:30:01 发布