begin tran begin try update statement 1 ... delete statement 2 ... end try begin catch if @@trancount > 0 rollback tran end catch if @@trancount > 0 commit tran go
源码如下: set nocount on; set lock_timeout 30000;declare @retry as int, @i as int, @j as int, @maxretries as int; select @retry=1, @i=0, @maxretries=3; while @retry=1 and @i<=@maxretries begin set @retry=0; begin try begin tran set @j=(select sum(col1) from dbo.T01); waitfor delay '00:00:05'; update dbo.T01 set col1=col1+1; waitfor delay '00:00:05'; set @j=(select sum(col1) from dbo.T02); commit tran print 'Transaction completed successfully.'; end try begin catch if error_number()=1222 begin print 'Lock timeout detected.'; if xact_state() <> 0 rollback; end else if error_number() in (1205, 3960) begin print case error_number() when 1205 then 'Deadlock' when 3960 then 'Update conflict' end + ' detected.'; if xact_state() <> 0 rollback; select @retry = 1, @i=@i+1; if @i <= @maxretries begin print 'Retry #'+cast(@i as varchar(10))+'.' waitfor delay '00:00:05'; end end else begin print 'Unhandled error: '+cast(error_number() as varchar(10))+', '+error_message(); if xact_state() <> 0 rollback; end end catch end
while @retry=1 and @i<=@maxretries begin set @retry=0; begin try ... end try begin catch if error_number()=1222 begin -- @retry 为 0,继续循环 end else if error_number() in (1205, 3960) begin ... select @retry = 1, @i=@i+1; ... end else begin -- @retry 为 0,继续循环 end end catch end
5 楼搞反了 while @retry=1 and @i<=@maxretries begin set @retry=0; begin try ... end try begin catch if error_number()=1222 begin ... end else if error_number() in (1205, 3960) begin ... select @retry = 1, @i=@i+1; -- 只要 @i<=@maxretries,继续循环 end else begin ... end end catch end
Deadlock detected. --这里是catch块代码
Retry #1.
Transaction completed successfully. --这里是try块代码说明程序走进了catch块,之后又回到了try块,这是怎么回事?请教!
set nocount on;
set lock_timeout 30000;declare @retry as int, @i as int, @j as int, @maxretries as int;
select @retry=1, @i=0, @maxretries=3;
while @retry=1 and @i<=@maxretries
begin
set @retry=0;
begin try
begin tran
set @j=(select sum(col1) from dbo.T01);
waitfor delay '00:00:05';
update dbo.T01 set col1=col1+1;
waitfor delay '00:00:05';
set @j=(select sum(col1) from dbo.T02);
commit tran
print 'Transaction completed successfully.';
end try
begin catch
if error_number()=1222
begin
print 'Lock timeout detected.';
if xact_state() <> 0 rollback;
end
else if error_number() in (1205, 3960)
begin
print case error_number()
when 1205 then 'Deadlock'
when 3960 then 'Update conflict'
end + ' detected.';
if xact_state() <> 0 rollback;
select @retry = 1, @i=@i+1;
if @i <= @maxretries
begin
print 'Retry #'+cast(@i as varchar(10))+'.'
waitfor delay '00:00:05';
end
end
else
begin
print 'Unhandled error: '+cast(error_number() as varchar(10))+', '+error_message();
if xact_state() <> 0 rollback;
end
end catch
end
begin
set @retry=0;
begin try
...
end try
begin catch
if error_number()=1222
begin
-- @retry 为 0,继续循环
end
else if error_number() in (1205, 3960)
begin
...
select @retry = 1, @i=@i+1;
...
end
else
begin
-- @retry 为 0,继续循环
end
end catch
end
while @retry=1 and @i<=@maxretries
begin
set @retry=0;
begin try
...
end try
begin catch
if error_number()=1222
begin
...
end
else if error_number() in (1205, 3960)
begin
...
select @retry = 1, @i=@i+1;
-- 只要 @i<=@maxretries,继续循环
end
else
begin
...
end
end catch
end
原因出在:你把begin try 和begin catch放在了while @retry=1 and @i<=@maxretries 这个while循环的内部,这样异常时会跳出来到begin catch,但是这里执行完之后,还是会继续去执行未完成的while循环,直至这个while循环结束,其实你看到的现象不是try catch的问题,而是while循环那里你把那个begin try 和begin catch放到while循环的外面应该就可以解决这个问题了,但是如果这样的话会带来另外一个问题:锁表的时间会变长,会降低性能和并发度,这个是需要酌情考虑的范畴。update语句可以添加with(rowlock)降低锁的粒度 。
你这个和我问的不是一个问题啊
--------------------------------------------------------------------------------------------------------
谢谢,如果说还有问题,就是你上面这段代码,rollback tran 之后 如果加条 RAISERROR,是否不是if @@trancount > 0 在进入catch之后就不会再判断了?
我是指你贴的那段代码,我试了下,RAISERROR之后还是往下执行,除非加上return,否则哪怕再加上SET XACT_ABORT ON都没用。
我觉得RAISERROR之后就应当停止运行了,这才是对的。这算不算TSQL的BUG?
我是指你贴的那段代码,我试了下,RAISERROR之后还是往下执行,除非加上return,否则哪怕再加上SET XACT_ABORT ON都没用。
我觉得RAISERROR之后就应当停止运行了,这才是对的。这算不算TSQL的BUG?raiserror只是抛出异常,异常抛出之后只是当前的语句停止进行,你的while循环还是要继续循环的。
按照程序运行的完整性来说这个不能算是bug吧,因为那段程序运行起来就是应该那样走的