事务在执行过程中报错的处理方式

最新推荐文章于 2024-05-21 11:30:26 发布

KelonsByCsdn

最新推荐文章于 2024-05-21 11:30:26 发布

阅读量2.8k

点赞数

分类专栏： SQL TRANSACTION 文章标签： SQL TRANSACTION

本文链接：https://blog.csdn.net/u013986317/article/details/80417262

版权

SQL 同时被 2 个专栏收录

27 篇文章 1 订阅

订阅专栏

TRANSACTION

4 篇文章 0 订阅

订阅专栏

1.系统在默认情况下：

a.在事务执行过程中遇到运行时的报错，不会回滚整个事务，会继续执行报错后面的语句；举例如下：

BEGIN TRAN;

INSERT  INTO dbo.Material_PO_PipeMaterialOrder
        ( ID ,
          OrderNO ,
          OrderName ,
          Status ,
          RMDSC
        )
VALUES  ( NEWID() ,
          N'测试自动回滚' ,
          N'测试自动回滚' ,
          9999999999999999999 ,--字段类型是int,但9999999999999999999超过int的最大值，会溢出报错
          N'测试自动回滚' 
        );

UPDATE  dbo.Material_PO_PipeMaterialOrder
SET     OrderNO = '测试自动回滚';
COMMIT TRAN;

执行消息:

消息 8115，级别 16，状态 2，第 3 行
将 expression 转换为数据类型 int 时出现算术溢出错误。
语句已终止。

(54 行受影响)

可以看出：Insert语句报错后，系统继续执行后面的Update语句(有行数受影响)，没有回滚事务

b.在事务执行过程中遇到编译时的报错(如语法错误等)，会回滚整个事务，报错后面的语句不会执行；举例如下：

BEGIN TRAN;

INSERT  INTO dbo.Material_PO_PipeMaterialOrder
        ( ID ,
          OrderNO ,
          OrderName ,
          Status ,
          RMDSC
        )
VALUES  ( NEWID() ,
          N'测试自动回滚' ,
          N'测试自动回滚' ,
           ,--此处有语法错误
          N'测试自动回滚' 
        );

UPDATE  dbo.Material_PO_PipeMaterialOrder
SET     OrderNO = '测试自动回滚1';
COMMIT TRAN;

执行消息:

消息 102，级别 15，状态 1，第 13 行
“,”附近有语法错误。

可以看出：Insert语句报错后，并没有继续执行Update语句，事务直接回滚。

2.SET XACT_ABORT { ON | OFF } ：

指定当 SQL Server 语句出现运行时错误时， Transact-SQL 是否自动回滚当前事务，参照https://docs.microsoft.com/zh-cn/sql/t-sql/statements/set-xact-abort-transact-sql?view=sql-server-2017

(1)为ON时，会自动回滚当前事务，举例如下：

SET XACT_ABORT ON
BEGIN TRAN;

INSERT  INTO dbo.Material_PO_PipeMaterialOrder
        ( ID ,
          OrderNO ,
          OrderName ,
          Status ,
          RMDSC
        )
VALUES  ( NEWID() ,
          N'测试自动回滚' ,
          N'测试自动回滚' ,
          9999999999999999999 ,--字段类型是int,但9999999999999999999超过int的最大值，会溢出报错
          N'测试自动回滚' 
        );

UPDATE  dbo.Material_PO_PipeMaterialOrder
SET     OrderNO = '测试自动回滚';
COMMIT TRAN;

执行消息:

消息 8115，级别 16，状态 2，第 4 行

将 expression 转换为数据类型 int 时出现算术溢出错误。

可以看出：Insert语句报错后，并没有继续执行Update语句，事务直接回滚

(2)为OFF时，不会自动回滚当前事务，会继续执行报错后面的语句，举例如下：

SET XACT_ABORT OFF
BEGIN TRAN;

INSERT  INTO dbo.Material_PO_PipeMaterialOrder
        ( ID ,
          OrderNO ,
          OrderName ,
          Status ,
          RMDSC
        )
VALUES  ( NEWID() ,
          N'测试自动回滚' ,
          N'测试自动回滚' ,
          9999999999999999999 ,--字段类型是int,但9999999999999999999超过int的最大值，会溢出报错
          N'测试自动回滚' 
        );

UPDATE  dbo.Material_PO_PipeMaterialOrder
SET     OrderNO = '测试自动回滚';
COMMIT TRAN;

执行消息:

消息 8115，级别 16，状态 2，第 4 行

将 expression 转换为数据类型 int 时出现算术溢出错误。

语句已终止。

(54 行受影响)

可以看出：Insert语句报错后，系统继续执行后面的Update语句(有行数受影响)，没有回滚事务

(3)注意点：

a.如果错误很严重，那么即使 SET XACT_ABORT 为 OFF，也可能回滚整个事务;

b.编译错误（如语法错误）都会回滚事务，不受 SET XACT_ABORT 的影响，举例如下：

SET XACT_ABORT OFF;
BEGIN TRAN;

INSERT  INTO dbo.Material_PO_PipeMaterialOrder
        ( ID ,
          OrderNO ,
          OrderName ,
          Status ,
          RMDSC
        )
VALUES  ( NEWID() ,
          N'测试自动回滚' ,
          N'测试自动回滚' ,
           ,--此处有语法错误
          N'测试自动回滚' 
        );

UPDATE  dbo.Material_PO_PipeMaterialOrder
SET     OrderNO = '测试自动回滚1';
COMMIT TRAN;

执行消息:

消息 102，级别 15，状态 1，第 14 行

“,”附近有语法错误。

可以看出：设置自动回滚为OFF后，Insert语句有语法错误，并没有继续执行Update语句，事务仍然自动回滚；

c. 在不手动设置 SET XACT_ABORT { ON | OFF }的情况下，系统会在执行事务之前，默认设置为OFF。

3.捕捉事务执行过程中的错误：

(1)使用try-catch捕获异常：

BEGIN TRAN;
BEGIN TRY
    INSERT  INTO dbo.Material_PO_PipeMaterialOrder
            ( ID ,
              OrderNO ,
              OrderName ,
              Status ,
              RMDSC
            )
    VALUES  ( NEWID() ,
              N'测试自动回滚' ,
              N'测试自动回滚' ,
              9999999999999999999 ,--此处有语法错误
              N'测试自动回滚' 
            );
END TRY
BEGIN CATCH
    ROLLBACK TRAN;--如果检查到try语句块中有异常,则回滚事务
    RETURN;--退出执行,return后面的语句不执行
END CATCH;
UPDATE  dbo.Material_PO_PipeMaterialOrder
SET     OrderNO = '测试自动回滚';
COMMIT TRAN;

执行消息:

(0 行受影响)

可以看出：事务只执行了Insert语句，而Insert失败，有0行受影响；因为使用了try-catch,所以消息中没有错误消息；(try-catch用法参见https://docs.microsoft.com/zh-cn/previous-versions/sql/sql-server-2008-r2/ms175976(v=sql.105))

注意：ROLLBACK TRAN 后要加return退出执行，否则后面的Update会另起事务继续执行；

(2).使用@@ERROR来检测错误：

BEGIN TRAN;

INSERT  INTO dbo.Material_PO_PipeMaterialOrder
        ( ID ,
          OrderNO ,
          OrderName ,
          Status ,
          RMDSC
        )
VALUES  ( NEWID() ,
          N'测试自动回滚' ,
          N'测试自动回滚' ,
          9999999999999999999 ,--此处有语法错误
          N'测试自动回滚' 
        );

IF ( @@ERROR <> 0 )--如果检查到错误
    BEGIN
        ROLLBACK TRAN;--回滚事务
        RETURN;--退出执行,return后面的语句不执行
    END;
UPDATE  dbo.Material_PO_PipeMaterialOrder
SET     OrderNO = '测试自动回滚';
COMMIT TRAN;

执行消息：

消息 8115，级别 16，状态 2，第 3 行
将 expression 转换为数据类型 int 时出现算术溢出错误。

语句已终止。

可以看出：检查到错误后，事务回滚，且显示错误消息