Osheep

时光不回头,当下最重要。

正确编写MSSQL数据库事务

定义
数据库事务(简称:事务)是数据库管理系统执行过程中的一个逻辑单位,由一个有限的数据库操作序列构成。事务是单个工作单元。 如果某一事务成功,则在该事务中进行的所有数据修改均会提交,成为数据库中的永久组成部分。 如果事务遇到错误且必须取消或回滚,则所有数据修改均被清除。

概要
一个数据库事务通常包含了一个序列的对数据库的读/写操作。它的存在包含有以下两个目的:

  • 为数据库操作序列提供了一个从失败中恢复到正常状态的方法,同时提供了数据库即使在异常状态下仍能保持一致性的方法。
  • 当多个应用程序在并发访问数据库时,可以在这些应用程序之间提供一个隔离方法,以防止彼此的操作互相干扰。

ACID性质
数据库事务拥有以下四个特性,习惯上被称之为ACID特性。

  • 原子性(Atomicity):事务作为一个整体被执行,包含在其中的对数据库的操作要么全部被执行,要么都不执行。
  • 一致性(Consistency):事务应确保数据库的状态从一个一致状态转变为另一个一致状态。一致状态的含义是数据库中的数据应满足完整性约束。
  • 隔离性(Isolation):多个事务并发执行时,一个事务的执行不应影响其他事务的执行。
  • 持久性(Durability):已被提交的事务对数据库的修改应该永久保存在数据库中。

使用示例

  • BEGIN TRANSACTION:事务的起始点
  • COMMIT TRANSACTION :提交事务
  • ROLLBACK TRANSACTION :滚到事务的起始点或事务内的某个保存点

在编写事务代码时,我们经常看到以下的写法:

BEGIN TRAN TestTran;

INSERT INTO Table_A (Field_1, Field2, ... ) VALUES (Value_1, Value_2, ...);
INSERT INTO Table_B (Field_1, Field2, ... ) VALUES (Value_1, Value_2, ...);

COMMIT TRAN TestTran;

这种写法存在很大隐患,并不能保证数据的一致性,比如:

CREATE TABLE TranTest (Id INT NOT NULL);

BEGIN TRAN TestTran;

INSERT INTO TranTest VALUES (NULL);
INSERT INTO TranTest VALUES (2);

COMMIT TRAN TestTran;

执行结果如图:

《正确编写MSSQL数据库事务》

不好的写法

可以发现,执行第一条插入语句时出现错误,但第二条语句却提示成功。这是因为MSSQL在执行时发生错误,默认会 ROLLBACK 引起错误的语句,而继续执行后续语句。

一种改进方法是,在每条SQL语句执行后,立即判断执行状态,并做相应处理:

BEGIN TRAN TestTran;

INSERT INTO TranTest VALUES (NULL);

IF @@ERROR <> 0
BEGIN
    ROLLBACK TRAN TestTran;
    GOTO STEPOUT;
END

INSERT INTO TranTest VALUES (2);

IF @@ERROR <> 0
BEGIN
    ROLLBACK TRAN TestTran;
    GOTO STEPOUT;
END

COMMIT TRAN TestTran;

STEPOUT:   
GO

另外一种常用方法是使用 TRY…CATCH 异常处理机制:

--@@TRANCOUNT 函数记录当前事务的嵌套级。
--每一次Begin Transaction都会引起@@TranCount加1。
--而每一次Commit Transaction都会使@@TranCount减1。
--而RollBack Transaction会回滚所有的嵌套事务包括已经提交的事务和未提交的事务,而使@@TranCount置0。

BEGIN TRAN TestTran;

BEGIN TRY
INSERT INTO TranTest VALUES (1);
INSERT INTO TranTest VALUES (NULL);
INSERT INTO TranTest VALUES (2);
END TRY BEGIN CATCH
IF @@TRANCOUNT > 0 ROLLBACK TRAN TestTran;
END CATCH

IF @@TRANCOUNT > 0   
COMMIT TRAN TestTran;
GO

最后还有一种更简洁的方法是设置 XACT_ABORT 选项:

--当 XACT_ABORT 选项为 ON 时,SQL SERVER在遇到错误时会终止执行并 ROLLBACK 整个事务

SET XACT_ABORT ON;
GO

BEGIN TRAN TestTran;
INSERT INTO TranTest VALUES (1);
INSERT INTO TranTest VALUES (NULL);
INSERT INTO TranTest VALUES (2);

COMMIT TRAN TestTran;
GO

参考文献
[1] 数据库事务. https://zh.wikipedia.org/wiki/%E6%95%B0%E6%8D%AE%E5%BA%93%E4%BA%8B%E5%8A%A1
[2] 事务. https://msdn.microsoft.com/zh-sg/library/ms174377.aspx

点赞