第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重新运行,应得到几行?若第二步外键失败,第一步是否该保留?
对照答案
两个编号不冲突时COMMIT执行,最后得到2行。第二步外键失败时应整体回滚,不保留第一步。
上一节:21 变量判断与TSQL代码块 | 下一节:23 创建和调用存储过程 | 课程目录