SQL-事务管理

来自必知必会的实践

事务处理 transaction processing

是一种机制，用来管理必须成批执行的SQL操作，保证一组操作不会中途停止。这组语句要么完全执行，要么完全不执行。如果没有错误发生，整组语句的结果提交（写入）数据库表，否则，回滚（rollback），将数据库恢复到某个已知且安全的状态。
比如转账，自己银行卡减少的同时，另一个银行卡必须增加，不能中途错误导致一个减少另一个却没有增加，这组操作必须成批执行。

关键词：
事务transaction：必须完整执行的语句块
– 对何时写数据，何时撤销进行明确的管理：
回滚rollback：撤销指定SQL语句的过程
提交commit：将未存储的SQL语句结果写入数据库表。
一般的SQL语句直接操作了表，这是隐式提交，即执行完语句结果就自动提交到表了。事务处理块中不是隐式提交，而进行明确的提交，使用COMMIT语句。
保留点savepoint：事务处理中设置的临时占位符，可以回滚到保留点。只可以回滚对数据的增删改操作，不能回滚表的操作。
– 关键：将SQL语句分解为逻辑块，并明确规定何时应该回滚，回滚到哪里

– SQLserver中以BEGIN TRANSACTION… COMMIT TRANSACTION标识事务处理块的开始和结束，语句之间的SQL必须完全执行或完全不执行

-- 删除订单12345，涉及订单表和订单细节表，要同时删除，
-- 因此使用事务处理块保证订单不被部分删除
BEGIN TRANSACTION
DELETE OrderItems WHERE order_num=12345;
DELETE Orders WHERE order_num=12345;
COMMIT TRANSACTION     -- 仅在上面都不出错时提交更改，若第一条成功，第二条失败，则两条都不会提交

– 使用保留点，更好的控制回退。因为复杂事务需要部分提交或回滚，发生错误时只需要返回到出错前一步，不需要回退到原点。
– 在事务处理块的合适位置放占位符，作为回滚的地标，称为保留点。
– 设置保留点：SAVE TRANSACTION savePoint_name;
– 回滚到保留点：ROLLBACK TRANSACTION savePoint_name:

BEGIN TRANSACTION -- 开始事务处理
INSERT INTO Customers(cust_id,cust_name) VALUES('1000000010','Toys Emporium'); --增加一个顾客
SAVE TRANSACTION StartOrder;  -- 设置保留点为开始订单前的一步
 -- 增加该顾客的订单
INSERT INTO Orders(order_num,order_date,cust_id) VALUES(20100,'2001/12/1','1000000010'); 
 -- 如果插入订单表出错（@@ERROR 错误数不为0），回滚到保留点，没有插入订单表那一步
IF @@ERROR!=0 ROLLBACK TRANSACTION StartOrder;     
-- 如果插入订单表未出错，则将第一个物品插入订单细节表
INSERT INTO OrderItems(order_num,order_item,prod_id,quantity,item_price) VALUES(20100,1,'BR01',100,5.49) 
-- 如果插入订单细节表出错（@@ERROR 错误数不为0），回滚到保留点，没有插入订单表那一步
IF @@ERROR!=0 ROLLBACK TRANSACTION StartOrder;    
-- 如果插入订单细节表未出错，则将第二个物品插入订单细节表  
INSERT INTO OrderItems(order_num,order_item,prod_id,quantity,item_price) VALUES(20100,2,'BR03',100,10.99) 
 -- 如果插入订单细节表出错（@@ERROR 错误数不为0），回滚到保留点，没有插入订单表那一步
IF @@ERROR!=0 ROLLBACK TRANSACTION StartOrder;     
COMMIT TRANSACTION            -- 如果全未出错，提交更改结果

– 该例中不能在插入订单细节表之前设第二个保留点，因为不允许有订单却没有订单物品，增加订单和增加订单物品是一个原子项，所以只设一个订单表之前的保留点就行
– SQLserver中通过检查名为@@ERROR的变量看是否出错，若为0则成功
– 保留点越多越好，越灵活进行回退