T-SQL——关于事务回滚的方式

目录

• 0. 关于事务的基础说明
• 1. 准备测试表
• 2. 使用@@ERROR
• 3. 使用XACT_ABORT
• 4. 使用TRY……CATCH（推荐）

0. 关于事务的基础说明

0. 事务的特点ACID

• 原子性：事务作为一个独立的逻辑单元，被称为原子单元。它的执行是一次性的，要么事务中的所有事件都完成，要么不执行事务中的任何事件。

• 一致性：事务提交或回滚操作成功执行后，所有数据必须保持从一种状态到另一种状态的一致性。

• 隔离性：并发事务的修改必须与其他并发事务所做的修改隔离。一个事务执行时不可以看到另一个事务执行的中间阶段。

• 持久性：事务提交或回滚操作成功执行后，该结果对于系统的响应是永久的，无论之后发生任何事件。

1. SQL Server 中事务分为三种：务可以`分显式事务`、`隐式事务`和`自动提交事务`三种类型

• 自动提交事务。是数据库的默认事务管理模式。每一个T-SQL语句都作为一个独立的事务发生。如果语句成功执行，则提交该语句；如果执行遇到错误，则回滚该语句。只要没有声明显式事务或隐式事务，那么数据库的实际连接就此默认模式操作。

• 隐式事务。指不需要描述事务的开始，只需要指明何时提交或回滚事务即可。若想使用隐式事务，可以使用set implicit_transaction on语句开启隐式事务模式，在隐式事务使用完毕后，再用set implicit_transaction off语句关闭隐式事务模式即可。当隐式事务模式开启后，首次执行以下任何语句时，都会自动启动一个事务：create、alter、drop、select、insert、update、delete、truncate、open、fetch、grant、revoke。在发生COMMIT或ROLLBACK语句之前，该事务将一直保持有效。在第一个事务被提交或回滚之后，下次当连接执行以上任何语句时，数据库引擎实例都将自动启动一个新事务。该模式将不断的生成隐性事务链，直到隐式事务模式关闭为止。

• 显式事务。指表明了事物的开始和结束的事务。事务以beigin transaction语句开始，以commit transaction或rollback transaction语句结束。

  • 隐式事务不用过多关注，重点在于显示事务

2. 显式事务的主要操作流程：

• 开始事务：begin transaction [事务名称]

  使用该语句标识一个事务的开始，全局变量@@transaction的值递增1，可以用来判断事务执行的结果状态。允许开始事务是不指定事务名称。transaction可简写为tran。

• 提交事务：commit transaction [事务名称]

  如果事务中的语句执行时没有遇到错误，可以使用该语句成功提交事务。事务中对数据库的操作有效执行，事务占有的资源被释放。可以不指定事务名称，以提交与之配对的begin transaction开始的事务。transaction可简写为tran。

• 回滚事务：rollback transaction [事务名称]

  如果事务中的语句执行时遇到错误，可以使用该语句清除遇到的事务，所有数据返回到事务开始时的状态。事务占用的资源被释放。可以不指定事务名称，以回滚与之配对的begin transaction开始的事务。transaction可简写为tran。

1. 准备测试表

CREATE TABLE [dbo].[AMyTest](
	[Id] [BIGINT] IDENTITY(1,1) NOT NULL,
	[Name] [NVARCHAR](50) ,----------Name不允许为Null
	[Age] [INT] NULL,
	[Gender] [INT] NULL,
	[CreateTime] [DATETIME] NULL,
 CONSTRAINT [PK_AMyTest] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[AMyTest] ADD  CONSTRAINT [DF_AMyTest_CreateTime]  DEFAULT (GETDATE()) FOR [CreateTime]
GO

2. 使用@@ERROR

@@ERROR是全局变量，用来统计最后一次T-SQL语句操作是否有误，如有返回错误代码，如无返回0。

注意1：@@ERROR仅仅是统计其上一句的SQL的执行是否有误

注意2：@@ERROR在每一条语句执行后会被立刻重置，因此应该将它的值保存到局部变量中以备将来使用。

BEGIN TRANSACTION;

DECLARE @ErrorNum INT=0;--声明该变量用于统计错误信息

INSERT INTO dbo.AMyTest(Name, Age, Gender)VALUES('张三', 30, 1);
SET @ErrorNum += @@ERROR;

INSERT INTO dbo.AMyTest(Name, Age, Gender)VALUES('李四', 30, 1);
SET @ErrorNum += @@ERROR;

UPDATE dbo.AMyTest SET Age=100 WHERE Name='张三';
--UPDATE dbo.AMyTest SET Age='a' WHERE Name='张三';--这里会报错，可以用于验证事务的回滚（这里可以发现，回滚后，前面两条的插入的记录被删除，其的自增Id，下次插入会跳过两个自增Id）
SET @ErrorNum += @@ERROR;

IF(@ErrorNum<>0) --判断是否报错如果报错就回滚信息否则提交事务
      ROLLBACK TRANSACTION;
ELSE COMMIT TRANSACTION;

3. 使用XACT_ABORT

SET XACT_ABORT ON ：开启，事务一旦出问题，全部回滚

SET XACT_ABORT OFF：默认值，关闭，不检查事务是否发生错误。

SET XACT_ABORT ON;

BEGIN TRANSACTION;

INSERT INTO dbo.AMyTest(Name, Age, Gender)VALUES('张三', 30, 1);
INSERT INTO dbo.AMyTest(Age, Gender)VALUES(30, 1);
INSERT INTO dbo.AMyTest(Name, Age, Gender)VALUES('李四', 30, 1);

COMMIT TRANSACTION;

4. 使用TRY……CATCH（推荐）

• 使用Try……Catch可以获取异常信息，可以将异常信息保存或返回
• 关于@@TRANCOUNT

在SqlServer里，嵌套事务的层次是由@@TranCount全局变量反映出来的。
每一次Begin Transaction都会引起@@TranCount加1。
每一次Commit Transaction都会使@@TranCount减1，
RollBack Transaction会回滚所有的嵌套事务包括已经提交的事务和未提交的事务，
而使@@TranCount置0

如果@@TRANCOUNT>0的话 那就是有begin tran有提交。
如果有异常,就进到catch里,然后@@TRANCOUNT又是大于0的,就是会全部回滚
没有catch中,那 就是全部提交

1. 事务包裹Try……Catch

BEGIN TRANSACTION;

BEGIN TRY
      INSERT INTO dbo.AMyTest(Name, Age, Gender)VALUES('张三', 30, 1);
      INSERT INTO dbo.AMyTest(Age, Gender)VALUES(30, 1);
      INSERT INTO dbo.AMyTest(Name, Age, Gender)VALUES('李四', 30, 1);
END TRY
BEGIN CATCH
      IF @@TRANCOUNT>0 ROLLBACK TRANSACTION;
	  --显示异常信息
	  SELECT ERROR_NUMBER() AS ErrorNumber, ERROR_MESSAGE() AS ErrorMessage, ERROR_LINE() AS LINE;
END CATCH;

IF @@TRANCOUNT>0 COMMIT TRANSACTION;

2. 使用Try……Catch 包裹 事务本事（推荐这样写）

BEGIN TRY
      BEGIN TRANSACTION;

      INSERT INTO dbo.AMyTest(Name, Age, Gender)VALUES('张三', 30, 1);
      INSERT INTO dbo.AMyTest(Age, Gender)VALUES(30, 1);
      INSERT INTO dbo.AMyTest(Name, Age, Gender)VALUES('李四', 30, 1);

      COMMIT TRANSACTION;
END TRY
BEGIN CATCH
      IF @@TRANCOUNT>0 ROLLBACK TRANSACTION;
	   --显示异常信息
      SELECT ERROR_NUMBER() AS ErrorNumber, ERROR_MESSAGE() AS ErrorMessage, ERROR_LINE() AS LINENum;
END CATCH;