第22节 事务让多步操作一起成功

假设新增图书后还要添加销售记录,第二步出错时,不希望只留下一半结果。事务把一组修改组成一个整体:全部成功才提交,失败就撤销。

1 先亲眼看见回滚

-- 临时表用于演示,不改变Book、Sale
CREATE TABLE #Account(Id int PRIMARY KEY,Amount int NOT NULL);
INSERT #Account(Id,Amount) VALUES(1,100);
 
BEGIN TRANSACTION; -- 开始事务
UPDATE #Account SET Amount=Amount-20 WHERE Id=1;
SELECT Amount AS [事务内看到80] FROM #Account;
ROLLBACK TRANSACTION; -- 撤销刚才减20的操作
 
SELECT Amount AS [回到100] FROM #Account;
DROP TABLE #Account;

COMMIT表示确认,ROLLBACK表示撤销。BEGIN/END只是代码块,BEGIN TRANSACTION才是真正开始事务。

2 一个可重复的失败路径练习

先给一个小表插入编号1,再故意插入重复编号。第二条失败时,第一条也必须撤销。

CREATE TABLE #TxnPractice(Id int PRIMARY KEY,Name nvarchar(20));
SET XACT_ABORT ON; -- 让许多运行时错误中止事务,仍需CATCH处理
 
BEGIN TRY
    BEGIN TRANSACTION;
    INSERT #TxnPractice(Id,Name) VALUES(1,N'第一次');
    INSERT #TxnPractice(Id,Name) VALUES(1,N'重复编号'); -- 故意触发主键错误
    COMMIT TRANSACTION; -- 本次不会执行到这里
END TRY
BEGIN CATCH
    -- 0无事务,1可提交,-1不可提交;非0时回滚,避免没有事务仍ROLLBACK
    IF XACT_STATE()<>0 ROLLBACK TRANSACTION;
    SELECT ERROR_MESSAGE() AS [预期的重复键错误];
END CATCH;
 
SELECT COUNT(*) AS [应为0] FROM #TxnPractice;
DROP TABLE #TxnPractice;

SELECT为0说明不仅拒绝了第二条,还撤销了第一条。仅测成功一次,并不能验证“失败会回滚”。

3 正式代码的基本形状

以下是结构模板,两条修改在真正使用时放入注释位置;空模板本身不改变数据。

SET XACT_ABORT ON;
BEGIN TRY
    BEGIN TRANSACTION;
    -- 在这里写第一条修改,例如插入新图书
    -- 在这里写第二条修改,例如插入该图书的销售
    COMMIT TRANSACTION;
END TRY
BEGIN CATCH
    IF XACT_STATE()<>0 ROLLBACK TRANSACTION;
    THROW; -- 在CATCH重新抛出原错误;前一条语句以分号结束
END CATCH;

XACT_ABORT不能代替TRY/CATCH;THROW与RAISERROR在响应XACT_ABORT等方面也有区别,不能假设完全相同。模板面向独立上机批,不是可任意嵌套的通用事务库。

4 理解ACID

特性简单含义
原子性Atomicity多步操作作为整体全部成功或全部撤销
一致性Consistency正确事务保持约束与业务规则
隔离性Isolation并发事务按隔离级别控制相互影响
持久性Durability提交成功的数据能够持久保存

事务开启后要及时提交或回滚,别一直挂着窗口不结束,可能占锁。SQL Server里TRUNCATE、DELETE以及许多DDL操作在合适的显式事务内可回滚,不要套其他产品“DDL都自动提交”的规则。

本节练习

把失败路径示例的第二个Id改为2重新运行,应得到几行?若第二步外键失败,第一步是否该保留?


上一节:21 变量判断与TSQL代码块 | 下一节:23 创建和调用存储过程 | 课程目录