本专栏目录
第1课 SQL入门之了解SQL
第2课 SQL入门之检索数据
第3课 SQL入门之排序检索数据
第4课 SQL入门之过滤数据
第5课 SQL入门之高级数据过滤
第6课 SQL入门之用通配符进行过滤
第7课 SQL入门之创建计算字段
第8课 SQL入门之使用数据处理函数
第9课 SQL入门之汇总数据
第10课 SQL入门之分组数据
第11课 SQL入门之使用子查询
第12课 SQL入门之联结表
第13课 SQL入门之创建高级联结
第14课 SQL入门之组合查询
第15课 SQL入门之插入数据
第16课 SQL入门之更新和删除数据
第17课 SQL入门之创建和操纵表
第18课 SQL入门之使用视图
第19课 SQL入门之使用存储过程
第20课 SQL入门之管理事务处理
第21课 SQL入门之使用游标
第22课 SQL入门之高级SQL特性
附录A SQL入门之SQL样例表脚本
附录B SQL入门之SQL流行的应用程序
附录C SQL入门之SQL语句的语法
附录D SQL入门之SQL数据类型
附录E SQL入门之SQL保留字
这一课介绍什么是事务处理，如何利用COMMIT和ROLLBACK语句管理事务处理。
20.1 事务处理
使用事务处理（transaction processing），通过确保成批的SQL操作要么完全执行，要么完全不执行，来维护数据库的完整性。
正如第12课所述，关系数据库把数据存储在多个表中，使数据更容易操纵、维护和重用。不用深究如何以及为什么进行关系数据库设计，在某种程度上说，设计良好的数据库模式都是关联的。
前面使用的Orders表就是一个很好的例子。订单存储在Orders和OrderItems两个表中：Orders存储实际的订单，OrderItems存储订购的各项物品。这两个表使用称为主键（参阅第1课）的唯一ID互相关联，又与包含客户和产品信息的其他表相关联。
给系统添加订单的过程如下：
- 检查数据库中是否存在相应的顾客，如果不存在，添加他；
- 检索顾客的ID；
- 在Orders表添加一行，它与顾客ID相关联；
- 检索Orders表中赋予的新订单ID；
- 为订购的每个物品在OrderItems表中添加一行，通过检索出来的ID把它与Orders表关联（并且通过产品ID与Products表关联）。
现在假设由于某种数据库故障（如超出磁盘空间、安全限制、表锁等），这个过程无法完成。数据库中的数据会出现什么情况？
如果故障发生在添加顾客之后，添加Orders表之前，则不会有什么问题。某些顾客没有订单是完全合法的。重新执行此过程时，所插入的顾客记录将被检索和使用。可以有效地从出故障的地方开始执行此过程。
但是，如果故障发生在插入Orders行之后，添加OrderItems行之前，怎么办？现在，数据库中有一个空订单。
更糟的是，如果系统在添加OrderItems行之时出现故障，怎么办？结果是数据库中存在不完整的订单，而你还不知道。
如何解决这种问题？这就需要使用事务处理了。事务处理是一种机制，用来管理必须成批执行的SQL操作，保证数据库不包含不完整的操作结果。利用事务处理，可以保证一组操作不会中途停止，它们要么完全执行，要么完全不执行（除非明确指示）。如果没有错误发生，整组语句提交给（写到）数据库表；如果发生错误，则进行回退（撤销），将数据库恢复到某个已知且安全的状态。
再看这个例子，这次我们说明这一过程是如何工作的： - 检查数据库中是否存在相应的顾客，如果不存在，添加他；
- 提交顾客信息；
- 检索顾客的ID；
- 在Orders表中添加一行；
- 如果向Orders表添加行时出现故障，回退；
- 检索Orders表中赋予的新订单ID；
- 对于订购的每项物品，添加新行到OrderItems表；
- 如果向OrderItems添加行时出现故障，回退所有添加的OrderItems行和Orders行。
在使用事务处理时，有几个反复出现的关键词。下面是关于事务处理需要知道的几个术语：
- 事务（transaction）指一组SQL语句；
- 回退（rollback）指撤销指定SQL语句的过程；
- 提交（commit）指将未存储的SQL语句结果写入数据库表；
- 保留点（savepoint）指事务处理中设置的临时占位符（placeholder），可以对它发布回退（与回退整个事务处理不同）。
提示：可以回退哪些语句？
事务处理用来管理INSERT、UPDATE和DELETE语句。不能回退SELECT语句（回退SELECT语句也没有必要），也不能回退CREATE或DROP操作。事务处理中可以使用这些语句，但进行回退时，这些操作也不撤销。
20.2 控制事务处理
我们已经知道了什么是事务处理，下面讨论管理事务中涉及的问题。
警告：事务处理实现的差异
不同DBMS用来实现事务处理的语法有所不同。在使用事务处理时请参阅相应的DBMS文档。
管理事务的关键在于将SQL语句组分解为逻辑块，并明确规定数据何时应该回退，何时不应该回退。
有的DBMS要求明确标识事务处理块的开始和结束。如在SQL Server中，标识如下：
输入▼
BEGIN TRANSACTION
...
COMMIT TRANSACTION
分析▼
在这个例子中，BEGIN TRANSACTION和COMMIT TRANSACTION语句之间的SQL必须完全执行或者完全不执行。
MariaDB和MySQL中等同的代码为：
输入▼
START TRANSACTION
...
Oracle使用的语法：
输入▼
SET TRANSACTION
...
PostgreSQL使用ANSI SQL语法：
输入▼
BEGIN
...
其他DBMS采用上述语法的变体。你会发现，多数实现没有明确标识事务处理在何处结束。事务一直存在，直到被中断。通常，COMMITT用于保存更改，ROLLBACK用于撤销，详述如下。
20.2.1 使用ROLLBACK
SQL的ROLLBACK命令用来回退（撤销）SQL语句，请看下面的语句：
输入▼
DELETE FROM Orders;
ROLLBACK;
分析▼
在此例子中，执行DELETE操作，然后用ROLLBACK语句撤销。虽然这不是最有用的例子，但它的确能够说明，在事务处理块中，DELETE操作（与INSERT和UPDATE操作一样）并不是最终的结果。
20.2.2 使用COMMIT
一般的SQL语句都是针对数据库表直接执行和编写的。这就是所谓的隐式提交（implicit commit），即提交（写或保存）操作是自动进行的。
在事务处理块中，提交不会隐式进行。不过，不同DBMS的做法有所不同。有的DBMS按隐式提交处理事务端，有的则不这样。
进行明确的提交，使用COMMIT语句。下面是一个SQL Server的例子：
输入▼
BEGIN TRANSACTION
DELETE OrderItems
WHERE order_num = 12345
DELETE Orders
WHERE order_num = 12345
COMMIT TRANSACTION
分析▼
在这个SQL Server例子中，从系统中完全删除订单12345。因为涉及更新两个数据库表Orders和OrderItems，所以使用事务处理块来保证订单不被部分删除。最后的COMMIT语句仅在不出错时写出更改。如果第一条DELETE起作用，但第二条失败，则DELETE不会提交。
为在Oracle中完成相同的工作，可如下进行：
输入▼
SET TRANSACTION
DELETE OrderItems
WHERE order_num = 12345;
DELETE Orders
WHERE order_num = 12345;
COMMIT;
20.2.3 使用保留点
使用简单的ROLLBACK和COMMIT语句，就可以写入或撤销整个事务。但是，只对简单的事务才能这样做，复杂的事务可能需要部分提交或回退。
例如前面描述的添加订单的过程就是一个事务。如果发生错误，只需要返回到添加Orders行之前即可。不需要回退到Customers表（如果存在的话）。
要支持回退部分事务，必须在事务处理块中的合适位置放置占位符。这样，如果需要回退，可以回退到某个占位符。
在SQL中，这些占位符称为保留点。在MariaDB、MySQL和Oracle中创建占位符，可使用SAVEPOINT语句：
输入▼
SAVEPOINT delete1;
在SQL Server中，如下进行：
输入▼
SAVE TRANSACTION delete1;
每个保留点都要取能够标识它的唯一名字，以便在回退时，DBMS知道回退到何处。要回退到本例给出的保留点，在SQL Server中可如下进行：
输入▼
ROLLBACK TRANSACTION delete1;
在MariaDB、MySQL和Oracle中，如下进行：
输入▼
ROLLBACK TO delete1;
下面是一个完整的SQL Server例子：
输入▼
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');
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);
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);
IF @@ERROR <> 0
ROLLBACK TRANSACTION StartOrder;
COMMIT TRANSACTION
分析▼
这里的事务处理块中包含了4条INSERT语句。在第一条INSERT语句之后定义了一个保留点，因此，如果后面的任何一个INSERT操作失败，事务处理最近回退到这里。在SQL Server中，可检查一个名为@@ERROR的变量，看操作是否成功。（其他DBMS使用不同的函数或变量返回此信息。）如果@@ERROR返回一个非0的值，表示有错误发生，事务处理回退到保留点。如果整个事务处理成功，发布COMMIT以保留数据。
提示：保留点越多越好
可以在SQL代码中设置任意多的保留点，越多越好。为什么呢？因为保留点越多，你就越能灵活地进行回退。
扫一扫
978
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
