事物处理
使用事务处理(transaction processing),通过确保成批的 SQL 操作要么完全执行,要么完全不执行,来维护数据库的完整性
关系数据库把数据存储在多个表中,使数据更容易操作、维护和重用
不用深究如何以及为什么进行关系数据库设计,在某种程度上说,设计良好的数据库模式都是关联的
以 Orders 表为例,订单存储在 Orders 和 OrderItems 两个表中:Orders 存储实际的订单,OrderItems 存储订购的各项物品
这两个表使用称为主键的唯一 ID 互相关联,又与包含客户和产品信息的其他表相关联
给系统添加订单的过程如下:
- 检查数据库中是否存在相应的顾客,如果不存在,就添加他
- 检索顾客的 ID
- 在 Orders 表中添加一行,它与顾客 ID 相关联
- 检索 Orders 表中赋予的新订单 ID
- 为订购的每个物品在 OrderItems 表中添加一行,通过检索出来的 ID 把它与 Orders 表关联(并且通过产品 ID 与 Products 表相关联)
假设由于某种数据库故障(如超出磁盘空间、安全限制、表锁等),这个过程无法完成,数据库中的数据会出现什么情况呢?
如果故障发生在添加顾客之后,添加 Orders 表之前,则不会出现什么问题;某些顾客没有订单是完全合法的;重新执行此过程时,所插入的顾客记录将被检索和利用,可以有效地从出现故障的地方开始执行此过程
如果故障发生在插入 Orders 行之后,添加 OrderItems 行前,则数据库中将出现一个空订单
更糟糕的是,如果系统在添加 OrderItems 行之时出现故障,结果是数据库中存在不完整的订单,并且不知道
为了结果这些问题,就需要使用事务处理
事务处理是一种机制,用来管理必须成批执行的 SQL 操作,保证数据库不包含不完整的操作结果
利用事务处理,可以保证一组操作不会中途停止,它们要么完全执行,要么完全不执行(除非有明确指示)
如果没有错误发生,整组语句提交给(写到)数据表;如果发生错误,则进行回退(撤销),将数据库恢复到某个已知且安全的状态
事务处理的相关术语:
事务(transaction): 指一组 SQL 语句
回退(rollback): 指撤销指定 SQL 语句的过程
提交(commit): 指将未存储的 SQL 语句写入数据库表
保留点(savepoint): 指事务处理中设置的临时占位符(placeholder),可以对它发布回退(与回退整个事务处理不同)
可以回退的语句:
事务处理用来管理 INSERT、UPDATE 和 DELETE 语句
不能回退 SELECT 语句(实际上,回退也没有必要);也不能回退 CREATE 或 DROP 操作
事物处理中可以使用这些语句,但是进行回退时,这些操作不可撤销
还是上述例子,其过程为:
- 检查数据库中是否存在相应的顾客,如果不存在,添加他
- 提交顾客信息
- 检索顾客的 ID
- 在 Orders 表中添加一行
- 如果向 Orders 表添加行时出现故障,回退
- 检索 Orders 表中赋予的新订单 ID
- 对于订购的每项物品,添加新行到 OrderItems 表
- 如果向 OrderItems 添加行时出现故障,回退所有添加的 OrderItems 行和 Orders 行
控制事务处理
管理事务的关键在于将 SQL 语句组分解为逻辑块,并明确规定数据何时应该回退,何时不应该回退
有的 DBMS 要求明确标识事务处理块的开始和结束,不同的 DBMS 中相关语句不相同,在 MySQL 中的语句为:
START TRANSACTION
...
从上述语句中,可以发现实现并没有明确标识事务处理在何处结束
事务一直存在,直到被中断;通常,COMMIT 用于保存更改,ROLLBACK 用于撤销,具体如下
使用 ROLLBACK
SQL 的ROLLBACK 命令用来回退(撤销)SQL 语句
DELETE FROM Orders;
ROLLBACK;
在上述代码中,执行 DELETE 操作,然后用 ROLLBACK 语句撤销;这说明,在事务处理中,DELETE (INSERT、UPDATE一样)操作并不是最终的结果
使用 COMMIT
一般的 SQL 语句都是针对数据库表直接执行和编写的,也就是所谓的隐式提交(implicit commit),即提交(写或保存)操作是自动进行的
在事务处理块中,提交不会隐式进行
进行明确的提交,使用 COMMIT 语句
使用保留点
使用简单的 ROLLBACK 和 COMMIT 语句,就可以写入或撤销整个事务
但是,只是对于简单的事务才能这样做,复杂的事务可能需要部分提交或回退
例如,之前描述的添加订单的过程就是一个事务;如果发生错误,只需要返回到添加 Orders 行之前即可;不需要回退到 Customers 表(如果存在的话)
要支持回退部分事务,必须在事务处理块中的合适位置放置占位符;当需要回退时,就可以回退到某个占位符
在 SQL 中,这些占位符称为保留点,在 MySQL 中可以使用 SAVEPOINT 语句创建占位符
SAVEPOINT delete1;
每个保留点都要取能够标识它的唯一名字,以便在回退时,DBMS 知道回退到何处,回退到上述代码中保留点的语句为:
ROLLBACK TO delete1;
注意:
可以在 SQL 代码中设置任意多的保留点,越多越好,越能灵活地进行回退