正确编写MSSQL数据库事务

2017-06-12 本文已影响0人简书冷雨

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

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

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

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在执行时发生错误，默认会 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

正确编写MSSQL数据库事务

猜你喜欢

热点阅读