[存储过程]中的事务(rollback)回滚

在编写SQL Server 事务相关的存储过程代码时，经常看到下面这样的写法：

 
 
begin tran   
update statement 1 ...   
update statement 2 ...   
delete statement 3 ...   
commit tran 

这样编写的SQL存在很大隐患。请看下面的例子：

 
 
create table demo(id int not null)   
go   
begin tran   
insert into demo values (null)  
insert into demo values (2)   
commit tran   
go 

执行时会出现一个违反not null 约束的错误信息，但随后又提示(1 row(s) affected)。 我们执行select * from demo 后发现insert into demo values(2) 却执行成功了。 这是什么原因呢? 原来 SQL Server在发生runtime 错误时，默认会rollback引起错误的语句，而继续执行后续语句。

如何避免这样的问题呢？有三种方法：

1. 在事务语句最前面加上set xact_abort on

 
 
set xact_abort on   
begin tran   
update statement 1 ...   
update statement 2 ...   
delete statement 3 ...   
commit tran   
go 

当xact_abort 选项为on 时，SQL Server在遇到错误时会终止执行并rollback 整个事务。  这个意思是说，如果该事务中包含子事务，子事务也会回滚。

2. 在每个单独的DML语句执行后，立即判断执行状态，并做相应处理。

 
 
begin tran   
update statement 1 ...  
if @@error <> 0   
begin rollback tran   
goto labend   
end   
delete statement 2 ...   
if @@error <> 0  
begin rollback tran   
goto labend   
end   
commit tran   
labend:   
go 

3. 在SQL Server 2005中，可利用 try...catch 异常处理机制。

 
 
begin tran   
begin try   
update statement 1 ...   
delete statement 2 ...   
endtry   
begin catch  
if @@trancount > 0   
rollback tran   
end catch  
if @@trancount > 0   
commit tran  
go 

下面是个简单的存储过程，演示事务处理过程。

 
 
create procedure dbo.pr_tran_inproc as begin set nocount on   
begin tran  
update statement 1 ...   
if @@error <> 0   
begin rollback tran   
return -1 end   
delete statement 2 ...   
if @@error <> 0   
begin rollback tran   
return -1   
end commit tran   
return 0   
end   
go  

原文出处：http://www.sqlstudy.com/sql_article.php?id=2008060701。