捕捉在 read committed 情况下的死锁 防止牺牲死锁或无限等待

--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 故没写