如何使用 SQL COMMIT 和 ROLLBACK 语句管理事务处理

最新推荐文章于 2024-04-23 09:36:52 发布

vaivxuanzi

最新推荐文章于 2024-04-23 09:36:52 发布

阅读量4.2k

点赞数 1

分类专栏： SQL 文章标签： sql

原文链接：https://www.developerastrid.com/sql/sql-transaction/

版权

SQL 专栏收录该内容

22 篇文章 5 订阅

订阅专栏

文章目录

本文介绍什么是 SQL 事务处理，如何利用 COMMIT 和 ROLLBACK 语句对何时写数据、何时撤销进行明确的管理；还学习了如何使用保留点，更好地控制回退操作。

一、事务处理

使用事务处理（transaction processing），通过确保成批的 SQL 操作要么完全执行，要么完全不执行，来维护数据库的完整性。

正如如何使用 SQL INNER JOIN 联结两个或多个表所述，关系数据库把数据存储在多个表中，使数据更容易操纵、维护和重用。

不用深究如何以及为什么进行关系数据库设计，在某种程度上说，设计良好的数据库模式都是关联的。

前面使用的 Orders 表就是一个很好的例子。订单存储在 Orders 和 OrderItems 两个表中：Orders 存储实际的订单，OrderItems 存储订购的各项物品。

这两个表使用称为主键（参阅学习 SQL 之前需要了解的基础知识）的唯一 ID 互相关联，又与包含客户和产品信息的其他表相关联。

给系统添加订单的过程如下：

(1) 检查数据库中是否存在相应的顾客，如果不存在，添加他；

(2) 检索顾客的 ID；

(3) 在 Orders 表添加一行，它与顾客 ID 相关联；

(4) 检索 Orders 表中赋予的新订单 ID；

(5) 为订购的每个物品在 OrderItems 表中添加一行，通过检索出来的 ID 把它与 Orders 表关联（并且通过产品 ID 与 Products 表关联）。

现在假设由于某种数据库故障（如超出磁盘空间、安全限制、表锁等），这个过程无法完成。数据库中的数据会出现什么情况？

如果故障发生在添加顾客之后，添加 Orders 表之前，则不会有什么问题。某些顾客没有订单是完全合法的。

重新执行此过程时，所插入的顾客记录将被检索和使用。可以有效地从出故障的地方开始执行此过程。

但是，如果故障发生在插入 Orders 行之后，添加 OrderItems 行之前，怎么办？现在，数据库中有一个空订单。

更糟的是，如果系统在添加 OrderItems 行之时出现故障，怎么办？结果是数据库中存在不完整的订单，而你还不知道。

如何解决这种问题？这就需要使用事务处理了。

事务处理是一种机制，用来管理必须成批执行的 SQL 操作，保证数据库不包含不完整的操作结果。

利用事务处理，可以保证一组操作不会中途停止，它们要么完全执行，要么完全不执行（除非明确指示）。

如果没有错误发生，整组语句提交给（写到）数据库表；如果发生错误，则进行回退（撤销），将数据库恢复到某个已知且安全的状态。

再看这个例子，这次我们说明这一过程是如何工作的：

(1) 检查数据库中是否存在相应的顾客，如果不存在，添加他；

(2) 提交顾客信息；

(3) 检索顾客的 ID；

(4) 在 Orders 表中添加一行；

(5) 如果向 Orders 表添加行时出现故障，回退；

(6) 检索 Orders 表中赋予的新订单 ID；

(7) 对于订购的每项物品，添加新行到 OrderItems 表；

(8) 如果向 OrderItems 添加行时出现故障，回退所有添加的 OrderItems 行和 Orders 行。

在使用事务处理时，有几个反复出现的关键词。下面是关于事务处理需要知道的几个术语：

事务（transaction）指一组 SQL 语句；
回退（rollback）指撤销指定 SQL 语句的过程；
提交（commit）指将未存储的 SQL 语句结果写入数据库表；
保留点（savepoint）指事务处理中设置的临时占位符（placeholder），可以对它发布回退（与回退整个事务处理不同）。

提示：可以回退哪些语句？

事务处理用来管理 INSERT、UPDATE 和 DELETE 语句。

不能回退 SELECT 语句（回退 SELECT 语句也没有必要），也不能回退 CREATE 或 DROP 操作。

事务处理中可以使用这些语句，但进行回退时，这些操作也不撤销。

二、控制事务处理

我们已经知道了什么是事务处理，下面讨论管理事务中涉及的问题。

注意：事务处理实现的差异

不同 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 采用上述语法的变体。

你会发现，多数实现没有明确标识事务处理在何处结束。事务一直存在，直到被中断。通常，COMMIT 用于保存更改，ROLLBACK 用于撤销，详述如下。

2.1 使用 ROLLBACK

SQL 的 ROLLBACK 命令用来回退（撤销）SQL 语句，请看下面的语句：

DELETE FROM Orders;
ROLLBACK;

在此例子中，执行 DELETE 操作，然后用 ROLLBACK 语句撤销。

虽然这不是最有用的例子，但它的确能够说明，在事务处理块中，DELETE 操作（与 INSERT 和 UPDATE 操作一样）并不是最终的结果。

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;

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 代码中设置任意多的保留点，越多越好。为什么呢？因为保留点越多，你就越能灵活地进行回退。

三、小结

本文介绍了事务是必须完整执行的 SQL 语句块。

我们学习了如何使用 COMMIT 和 ROLLBACK 语句对何时写数据、何时撤销进行明确的管理；还学习了如何使用保留点，更好地控制回退操作。

事务处理是个相当重要的主题，一篇文章的内容无法全部涉及。各种 DBMS 对事务处理的实现不同，详细内容请参考具体的 DBMS 文档。

原文链接：https://www.developerastrid.com/sql/sql-transaction/

（完）

vaivxuanzi

关注

1
点赞
踩
10

收藏

觉得还不错? 一键收藏
0
评论
如何使用 SQL COMMIT 和 ROLLBACK 语句管理事务处理

文章目录一、事务处理二、控制事务处理2.1 使用 ROLLBACK2.2 使用 COMMIT2.3 使用保留点三、小结本文介绍什么是 SQL 事务处理，如何利用 COMMIT 和 ROLLBACK 语句对何时写数据、何时撤销进行明确的管理；还学习了如何使用保留点，更好地控制回退操作。一、事务处理使用事务处理（transaction processing），通过确保成批的 SQL 操作要么完全执行，要么完全不执行，来维护数据库的完整性。正如如何使用 SQL INNER JOIN 联结两个或多个表所
复制链接

扫一扫