SqlServe存储过程中运用事务

SqlServe存储过程中运用事务三种写法

通过 BEGIN TRANSACTION XXX（事务名字）语句定义了一个 事务，通过 COMMIT TRANSACTION XXX（事务名字）或 COMMIT Transaction XXX（事务名字）语句指定事务结束；

SET XACT_ABORT ON

SET XACT_ABORT ON
指当Transact-SQL语句产生运行错误时，SQL Server是否自动回滚当前事务。
语法
SET XACT_ABORT{ON|OFF}
当SET XACT_ABORT为ON时，如果Transact-SQL语句产生运行时错误，整个事务将终止并回滚。
为OFF时，只回滚产生错误的Transact-SQL语句，而事务将继续进行处理。
编译错误（如语法错误）不受SET XACT_ABORT的影响。
事务嵌套使用时，子事务成功，外面的事务失败，回滚时会一起回滚子事务的数据。

SET XACT_ABORT ON也可分为两种：

1、总体作为一个事务，整体提交或整体回滚，格式为：

SET XACT_ABORT ON

BEGIN TRAN

    --要执行的语句

COMMIT TRAN

GO
1
2
3
4
5
6
7
8
9

2、每个语句作为一个事务，事务在错误行终止，错误行回滚，错误行之前的不回滚，格式为：

SET XACT_ABORT ON

BEGIN

    --要执行的语句

END

GO
1
2
3
4
5
6
7
8
9

如果没加SET XACT_ABORT ON
可能会出现如：

begin tran --开始执行事务 
    --语句一

    update  set acount = acount-100 where person = 1

   --语句二

    update  set acount = acount+100 where person = 2

   --语句三

     insert into T_IMP_LOG values (1,2,100)


if @@error<>0 --判断如果语句有任何一条出现错误
    begin rollback tran --开始执行事务的回滚 
        return 0
    end
else   --如何都执行成功
    begin 
        commit tran --执行这个事务的操作
        return 1
    end
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

在上面代码中，我遇到的问题是：语句二出错，语句一仍然执行，语句三不执行，本以为添加了事物控制就能全部回滚，后来查了资料才知道需要加上SET XACT_ABORT ON。

一

没有第二种那么细粒度化

   SET XACT_ABORT ON
   Begin Transaction UPDATE_T
       Update XXXXX ...
       Update XXXXX ...
   -- @@Error是SQLSERVE自带的判断存储过程是否执行有问题的标识，@@Error等于0表示事务正常执行完成可以做提交操作
   IF @@Error <> 0
   Begin
       Print '111'
       RollBack Transaction UPDATE_T
   End
   Else
   Begin
       print '222'
       COMMIT Transaction UPDATE_T
   End
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

二

这里可以实现更加细粒度化的事务；如：可以根据@error等于几去编写具体的错误信息

--事务中操作的错误记录
declare @error int =0 
  SET XACT_ABORT ON
  --开启事务
  begin transaction INSERT_T
    --INSERT语句
    insert into XXXXX ...
    --记录有可能产生的错误号 
    set @error+=@@ERROR 

    if exists (select * from XXXXX ... ) 
      begin
        --UPDATE语句
        update XXXXX ...
        set @error+=@@ERROR --记录有可能产生的错误号     
    end
    else 
      begin
        --INSERT语句
        insert into  XXXXX ...
        --记录有可能产生的错误号     
        set @error+=@@ERROR 
      end
--判断事务的提交或者回滚
if(@error<>0)
  begin
    rollback transaction INSERT_T
    return -1 --设置操作结果错误标识
  end
else
  begin
    commit transaction INSERT_T
    return 1 --操作成功的标识
  end
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34

三

带任务点的形式
执行一部分事务，而另一部分不执行的情况（一般用不到）

Begin
　　　　Set NOCOUNT ON; --不返回影响行数
　　　　Set XACT_ABORT ON; --使用存储过程执行事务需要开启XACT_ABORT参数(默认为OFF)
　　　　　　delete from table1 where name='' --删除数据sql1
　　　　begin tran tran1 --开始一个事务tran1
　　　　　　delete from table1 where name='' --删除数据sql2
　　　　save tran tran2 --保存一个事务点tran2
　　　　　　update table2 set name='' where id='' --修改数据sql3
　　　　　　if @@error<>0 --判断修改数据有没有错误(@@error表示返回与@@ERROR 最近的语句(即sql3)的非零的错误码,没有错误则返回0)
　　　　　　　　begin
　　　　　　　　　　rollback tran tran2 --回滚事务到tran2的还原点
　　　　　　　　　　commit tran tran1 --提交事务tran1
　　　　　　　　end
　　　　　　else --没有出错则提交事务tran1
　　　　　　　　commit tran tran1 --提交事务tran1
　　End
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

如果sql3执行失败，则会回滚到事务tran2的创建处(相当于只有sql1和sql2执行外，其他都未执行)。
1