SqlServe存储过程中运用事务

「爱情、让人受尽委屈。」 2023-10-15 21:12 45阅读 0赞

## 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

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

SET XACT_ABORT ON
    
    BEGIN
    
        --要执行的语句
    
    END
    
    GO

如果没加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

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

### 二 ###

`这里可以实现更加细粒度化的事务；如：可以根据@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

### 三 ###

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

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

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