事务失败会全部回滚吗？

最新推荐文章于 2024-04-20 14:50:59 发布

OwenZeng_DBA

最新推荐文章于 2024-04-20 14:50:59 发布

阅读量5.9k

点赞数 5

分类专栏： SQL SERVER 文章标签：事务回滚异常捕捉

本文链接：https://blog.csdn.net/z10843087/article/details/79206973

版权

SQL SERVER 专栏收录该内容

49 篇文章 4 订阅

订阅专栏

前言

这个问题看起来是一个简单明了，显而易见的问题。事务应该是原子的，要么整个事务完成，要么没有完成。

举个栗子

CREATE TABLE TestingTransactionRollbacks (
 ID INT NOT NULL PRIMARY KEY ,
 SomeDate DATETIME DEFAULT GETDATE()
 ) ;
GO
BEGIN TRANSACTION
-- succeeds
INSERT INTO TestingTransactionRollbacks (ID)
 VALUES (1)
-- Fails. Cannot insert null into a non-null column
INSERT INTO TestingTransactionRollbacks (ID)
 VALUES (NULL)
-- succeeds
INSERT INTO TestingTransactionRollbacks (ID)
 VALUES (2)
-- fails. Duplicate key
INSERT INTO TestingTransactionRollbacks (ID)
 VALUES (2)
-- succeeds
INSERT INTO TestingTransactionRollbacks (ID)
 VALUES (3)
COMMIT TRANSACTION
GO
SELECT ID, SomeDate FROM TestingTransactionRollbacks
GO
DROP TABLE TestingTransactionRollbacks

按照通常对事务的理解，如果事务在第一次失败时，应该回滚，最后的查询将不返回任何行。但结果却不是，它返回3了行。

语句的的失败被忽略了，==事务完成并提交了==。

如果这是一个重要的业务流程，而不是一个虚构的例子，那么可能会对数据的事务一致性产生一些不良后果。
这里究竟发生了什么？交易是不是原子？如果SQL没有成功完成，SQL是不是应该把它们回滚呢？
微软官网说明如下：

A transaction is a single unit of work. If a transaction is successful, all of the data modifications made during the transaction are committed and become a permanent part of the database. If a transaction encounters errors and must be canceled or rolled back, then all of the data modifications are erased.

大意如下：
事务是一个单一的工作单位。如果事务成功，则在事务中进行的所有数据修改都将被提交并成为数据库的永久部分。如果事务遇到错误，必须取消或回退，则所有数据修改都将被清除这表明事实上交易应该自动回滚

不过官网又说了：

If the client’s network connection to an instance of the Database Engine is broken, any outstanding transactions for the connection are rolled back when the network notifies the instance of the break.

If a run-time statement error (such as a constraint violation) occurs in a batch, the default behavior in the Database Engine is to roll back only the statement that generated the error.

如果客户端到数据库引擎的实例的网络连接中断，则在网络通知中断实例时，所有未完成的连接事务将被回滚。

如果批处理中发生==运行时语句错误==（例如违反约束），那么数据库引擎中的默认行为是仅回滚生成错误的语句。默认行为是仅回滚生成错误的语句。不是整个交易。

如果连接关闭（网络错误，客户端断开，严重性错误）并且未提交，则事务将回滚。如果SQL Server终止（关闭，电源故障，意外终止）并且未达到提交，事务将被回滚。在默认设置下，事务中的语句抛出的非致命错误不会自动导致回滚。（致命=严重程度为19以上）

关于严重程度可以参考官网：
数据库引擎错误严重性

怎么办

那么，如果我们希望事务在执行过程中遇到任何错误时完全回滚，我们该怎么办？

有两个选项。

1）使用Xact_Abort设置

2）捕获并处理错误，并在错误处理中指定回滚

Xact_Abort

当SET XACT_ABORT为ON时，如果Transact-SQL语句引发运行时错误，则整个事务将终止并回滚。

当SET XACT_ABORT为OFF时，在某些情况下，只有导致错误的Transact-SQL语句被回滚，事务继续处理。根据错误的严重程度，即使SET XACT_ABORT为OFF，整个事务也可能会回滚。默认设置是OFF。

下面做个试验：

CREATE TABLE TestingTransactionRollbacks (
 ID INT NOT NULL PRIMARY KEY ,
 SomeDate DATETIME DEFAULT GETDATE()
 ) ;
GO
SET XACT_ABORT ON
GO

BEGIN TRANSACTION
-- succeeds
INSERT INTO TestingTransactionRollbacks (ID)
 VALUES (1)
-- Fails. Cannot insert null into a non-null column
INSERT INTO TestingTransactionRollbacks (ID)
 VALUES (NULL)
-- succeeds
INSERT INTO TestingTransactionRollbacks (ID)
 VALUES (2)
-- fails. Duplicate key
INSERT INTO TestingTransactionRollbacks (ID)
 VALUES (2)
-- succeeds
INSERT INTO TestingTransactionRollbacks (ID)
 VALUES (3)
COMMIT TRANSACTION
GO
SELECT ID, SomeDate FROM TestingTransactionRollbacks
GO
DROP TABLE TestingTransactionRollbacks

从结果来，第一个运行时错误，导致整个事务回滚。返回的结果是0行
# ![这里写图片描述](https://img-blog.csdn.net/20180130133952599?watermark/2/text/aHR0cDovL2Jsb2cuY3Nkbi5uZXQvejEwODQzMDg3/font/5a6L5L2T/fontsize/400/fill/I0JBQkFCMA==/dissolve/70/gravity/SouthEast)

如果你想要的是一旦发生错误，事务回滚，但是对任何额外的错误处理或日志记录不感兴趣，开启这个选项就足够了。

捕捉错误并处理

CREATE TABLE TestingTransactionRollbacks (
 ID INT NOT NULL
 PRIMARY KEY ,
 SomeDate DATETIME DEFAULT GETDATE()
 ) ;
GO

BEGIN TRANSACTION
BEGIN TRY
 -- succeeds
 INSERT INTO TestingTransactionRollbacks (ID)
 VALUES (1)
 -- Fails. Cannot insert null into a non-null column
 INSERT INTO TestingTransactionRollbacks (ID)
 VALUES (NULL)
 -- succeeds
 INSERT INTO TestingTransactionRollbacks (ID)
 VALUES (2)
 -- fails. Duplicate key
 INSERT INTO TestingTransactionRollbacks (ID)
 VALUES (2)
 -- succeeds
 INSERT INTO TestingTransactionRollbacks (ID)
 VALUES (3)
 COMMIT TRANSACTION
END TRY
BEGIN CATCH
 ROLLBACK TRANSACTION
END CATCH
GO
SELECT ID, SomeDate FROM TestingTransactionRollbacks
GO
DROP TABLE TestingTransactionRollbacks

第一个异常将执行转移到Catch块中，然后将事务回滚，当select运行时，表中有0行。

记录错误日志

这看起来像XactAbort一样，但是处理错误有好处的，而不是让SQL自动将事务处理回滚，自动回滚了可能你就不知道什么时候发生了什么错误。 catch块并不局限于只回滚事务，它可以记录到错误日志（在回滚之后，这样日志不会回滚），它可以采取补偿措施，甚至不需要将事务回滚（在多数情况下）。

使用catch块的原因之一是有许多错误相关的函数只在catch块内调用时才返回数据。这些函数可以创建一个友好的错误提示（使用raiserror）。客户端应用程序得到就不是默认的SQL错误消息。而是检查出现的错误是什么，并针对不同的错误采取不同的行为。

比如像下面的例子：

CREATE TABLE TestingTransactionRollbacks (
 ID INT NOT NULL
 PRIMARY KEY ,
 SomeDate DATETIME DEFAULT GETDATE()
 ) ;
GO

BEGIN TRANSACTION
BEGIN TRY
 -- succeeds
 INSERT INTO TestingTransactionRollbacks (ID)
 VALUES (1)
 -- Fails. Cannot insert null into a non-null column
 INSERT INTO TestingTransactionRollbacks (ID)
 VALUES (NULL)
 -- succeeds
 INSERT INTO TestingTransactionRollbacks (ID)
 VALUES (2)
 -- fails. Duplicate key
 INSERT INTO TestingTransactionRollbacks (ID)
 VALUES (2)
 -- succeeds
 INSERT INTO TestingTransactionRollbacks (ID)
 VALUES (3)
 COMMIT TRANSACTION
END TRY
BEGIN CATCH
  ROLLBACK TRANSACTION
  SELECT  ERROR_NUMBER() AS ErrorNumber, ERROR_SEVERITY() AS Severity, ERROR_MESSAGE() AS ErrorMessage, ERROR_LINE() AS ErrorLine, ERROR_PROCEDURE() AS ErrorProcedure
END CATCH
GO
EXEC InsertWithError

GO
DROP TABLE TestingTransactionRollbacks
DROP PROCEDURE InsertWithError

使用这些函数，可以将确切的错误文本记录到表中以供进一步分析，以及错误发生的行和过程，然后将友好错误发回给用户。

doomed transaction

最后需要提到的一件事是doomed transaction的概念。这是一个事务，一旦执行转移到catch块，必须回滚。最简单的方法是将XactAbort和Try-Catch块组合起来

CREATE TABLE TestingTransactionRollbacks (
 ID INT NOT NULL PRIMARY KEY ,
 SomeDate DATETIME DEFAULT GETDATE()
 ) ;
GO

SET XACT_ABORT ON ;

BEGIN TRANSACTION
BEGIN TRY
 -- succeeds
 INSERT INTO TestingTransactionRollbacks (ID)
 VALUES (1)
 -- Fails. Cannot insert null into a non-null column
 INSERT INTO TestingTransactionRollbacks (ID)
 VALUES (NULL)
 -- succeeds
 INSERT INTO TestingTransactionRollbacks (ID)
 VALUES (2)
 -- fails. Duplicate key
 INSERT INTO TestingTransactionRollbacks (ID)
 VALUES (2)
 -- succeeds
 INSERT INTO TestingTransactionRollbacks (ID)
 VALUES (3)
 COMMIT TRANSACTION
END TRY
BEGIN CATCH
 COMMIT TRANSACTION
END CATCH
GO
SELECT ID, SomeDate FROM TestingTransactionRollbacks
GO
DROP TABLE TestingTransactionRollbacks

在这种情况下，我无视错误并提交。可能在实际生产系统中很少会出现，但仅用于演示目的。运行这个会返回另一个错误（一个在catch块中抛出）

这里写图片描述

那么你如何检查这个？内置函数XactState会告诉我们事务的状态。值为1表示可以提交事务，值为-1表示事务已注销，只能回滚。
用下面的代码替换catch块允许代码运行没有错误

BEGIN CATCH
  IF XACT_STATE() = 1
    COMMIT TRANSACTION
  IF XACT_STATE() = -1
    ROLLBACK TRANSACTION
END CATCH

总结

尽管SQL没有提供前端应用程序的丰富的异常处理，但它所提供的对于良好的错误处理来说是足够的，特别是与必须提交或作为原子单位回退的事务相结合。

所有在世界上的错误处理，如果没有被使用，将不会有所帮助，而只是希望代码每次都能正确运行，从来不是一个好的开发实践。

在全局开启XACT_ABORT

EXEC sys.sp_configure N'user options', N'16384'
GO
RECONFIGURE WITH OVERRIDE
GO

直接开启全局选项的问题，所有问题都被直接回滚了，无法看到错误的信息。这不利于对问题的分析

OwenZeng_DBA

关注

5
点赞
踩
21

收藏

觉得还不错? 一键收藏
0
评论
事务失败会全部回滚吗？

前言这个问题看起来是一个简单明了，显而易见的问题。事务应该是原子的，要么整个事务完成，要么没有完成。举个栗子CREATE TABLE TestingTransactionRollbacks ( ID INT NOT NULL PRIMARY KEY , SomeDate DATETIME DEFAULT GETDATE() ) ;GOBEGIN TRANSACTIO
复制链接

扫一扫