日期:2014-05-17  浏览次数:20511 次

事务的原子性?
存储过程使用事务时,出现了下面几种情况,求大虾解释。

想问的是:事务何时会自动rollback(第2处错误),何时需要手动(显示)提交?

当然,为了避免出现错误而没有回滚,我想到的最好的方式是,每句t-sql后加一个判断,if @@error<>0 rollback tran

--创建测试表
create table t(i int identity(1,1),val char(1),dt datetime)

--创建测试视图,为了错误重现,视图是错误的
create view v_test_v
as
select * from t where i=(select 1 union select 2)
go


--测试存储过程
alter proc UP_Test_1
as
begin tran
declare @err int
set @err=0

insert into t
values( '0',getdate())
set @err=@err+@@error

---1.取消注释,此处出现视图查询错误,运行proc时出现错误,事务挂起
/* select * from v_test_v
set @err=@err+@@error  */

--2.取消注释,运行时,此行错误,"从字符串向 datetime 转换时失败。",事务自动rollback
/*insert into t
values( '0','210')
set @err=@err+@@error
*/

select * from t
set @err=@err+@@error

--3.错误提示:"将截断字符串或二进制数据。语句已终止。
--EXECUTE 后的事务计数指示缺少了 COMMIT 或 ROLLBACK TRANSACTION 语句。上一计数 = 2,当前计数 = 3。",事务不会自动rollback
insert t
select '00',getdate()
set @err=@err+@@error

/*
if @err <>0
begin
print 'rollback'
rollback tran
end
else
begin
print 'commit'
commit tran
end
*/
go

------解决方案--------------------
SQL2000的话,
  在事务开始前 set xact_abort on 即实现事务过程中出错自动回滚.

SQL2008的话,
  可以用try.. catch捕获异常,然后rollback tran即可,无需每句后都去判断@@error.
------解决方案--------------------
三种写法:
1、
SET XACT_ABORT ON
BEGIN TRANSACTION
-- 所有语句,这里出错会自动回滚,关键是前面的SET XACT_ABORT ON语句
COMMIT TRANSACTION

2、2005+

BEGIN TRANSACTION
BEGIN TRY
-- 所有语句,这里出错会自动跳转到CATCH
END TRY
BEGIN CATCH
   ...
   ROLLBACK TRANSACTION
   RETURN;
END CATCH
COMMIT TRANSACTION

3、事务中每句判断@@ERROR,<>0回滚和返回