【SQL 必知必会】- 第二十课 管理事务处理

目录

写在前面

20.1 事务处理

        可以回退哪些语句？

20.2 控制事务处理

        20.2.1 使用ROLLBACK

        20.2.2 使用COMMIT

        20.2.3 使用保留点

        保留点越多越好

---

        这一课介绍什么是事务处理，如何利用 COMMIT 和 ROLLBACK 语句管理事务处理。

写在前面

        事务是非常重要的一个概念，在工作中，几乎处处都能见到它的身影，对于保持数据的一致性起到了至关重要的作用，一定要对事务有非常深刻的了解，并学会应用，这会使你受益颇深。否则因为不是一个事务而导致出现的各种问题，会让你体会到什么叫做真正的痛苦，对于稍微大点的公司而言，几百上千张表是非常寻常的事情。

---

20.1 事务处理

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

        正如第12 课所述，关系数据库把数据存储在多个表中，使数据更容易操纵、维护和重用。不用深究如何以及为什么进行关系数据库设计，在某种程度上说，设计良好的数据库模式都是关联的。

        现在需要对很多个表先后进行插入 / 删除 / 更新 操作，现在假设由于某种数据库故障（如超出磁盘空间、安全限制、表锁等），这个过程无法完成。数据库中的数据会出现什么情况？

        会导致其中的某些语句执行成功，但是后面的语句因为中间一句的错误，没有执行成功，那么数据应该怎么操作就是个大问题，甚至都不知道是哪里出现了问题。

        如何解决这种问题？这就需要使用事务处理了。事务处理是一种机制，用来管理必须成批执行的SQL 操作，保证数据库不包含不完整的操作结果。利用事务处理，可以保证一组操作不会中途停止，它们要么完全执行，要么完全不执行（除非明确指示）。如果没有错误发生，整组语句提交给（写到）数据库表；如果发生错误，则进行回退（撤销），将数据库恢复到某个已知且安全的状态。

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

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

---

        可以回退哪些语句？

        事务处理用来管理 INSERT、UPDATE 和 DELETE 语句。不能回退 SELECT语句（回退SELECT 语句也没有必要），也不能回退 CREATE 或 DROP 操作。事务处理中可以使用这些语句，但进行回退时，这些操作也不撤销。

---

20.2 控制事务处理

        管理事务的关键在于将 SQL 语句组分解为逻辑块，并明确规定数据何时应该回退，何时不应该回退。

        Oracle 使用的语法：

SET TRANSACTION
...

        oracle 还可以使用这种操作实现事务：

begin
    语句一;
    语句二;
    ......
    语句三;
end;

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

---

        20.2.1 使用ROLLBACK

        ROLLBACK 命令用来回退（撤销）SQL 语句。

DELETE FROM Orders;
ROLLBACK;

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

---

        20.2.2 使用COMMIT

        一般的SQL 语句都是针对数据库表直接执行和编写的。这就是所谓的隐式提交（implicit commit），即提交（写或保存）操作是自动进行的。

        在事务处理块中，提交不会隐式进行。不过，不同DBMS 的做法有所不同。有的DBMS 按隐式提交处理事务端，有的则不这样。

        进行明确的提交，使用COMMIT 语句。

SET TRANSACTION
DELETE OrderItems WHERE order_num = 12345;
DELETE Orders WHERE order_num = 12345;
COMMIT;

---

        20.2.3 使用保留点

        使用简单的 ROLLBACK 和 COMMIT 语句，就可以写入或撤销整个事务。但是，只对简单的事务才能这样做，复杂的事务可能需要部分提交或回退。

        要支持回退部分事务，必须在事务处理块中的合适位置放置占位符。这样，如果需要回退，可以回退到某个占位符。

        在 SQL 中，这些占位符称为保留点。在 MariaDB、MySQL 和Oracle 中创建占位符，可使用 SAVEPOINT 语句。

SAVEPOINT delete1;

        工作中其实并没有经常用到，可能是工作性质导致的。

---

        保留点越多越好

        可以在 SQL 代码中设置任意多的保留点，越多越好。为什么呢？因为保留点越多，你就越能灵活地进行回退。

        使用保留点的好处：

        在大批量的 SQL 执行的过程中，如果后续出现错误，导致 SQL执行失败，若没有保留点，则需要从头执行，比较耗时。而有保留点，则可以使得执行的 SQL语句个数减少，从而减少时间。