事务错误 和进程阻塞

--阻塞和错误 
--version 2005
declare @tcount int,@rcount int
select @tcount=@@trancount
if @tcount=0 ---外部没有事务打开
begin tran
else 
save tran s1 --外部事务已经打开
begin try
set lock_timeout 5000 --5秒超时
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 @count= @@rowcount
insert into ..... --语句
if @tcount=0 and @count=0
begin
 commit tran
end
end try
begin catch
 if @tcount=0 and @count>0  --外部事物没打开并且阻塞
 begin
 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 error_message(),error_severity(),error_line()
 rollback tran
 end
 if @tcount>0 and @count=0 --外部事物打开并且没阻塞
 begin 
  if xact_state()=1 --事务错误 
  begin 
  select error_message(),error_severity(),error_line()
  rollback tran s1 
  end
  if xact_state()=-1 --外部事务错误 
  begin 
  print '外部事务错误 自己判断 committed 或 rollback '
  end
end
if @tcount>0 and @count>0 
begin 
 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 --查看阻塞
 if xact_state()=1
 begin 
  select error_message(),error_severity(),error_line()
  rollback tran s1 
 end
 if xact_state()=-1 --外部事务错误 
 begin 
  print '外部事务错误 自己判断 committed 或 rollback '
 end
end
end catch