7.1 数据库事务的处理

---

---

1.概述

事务处理可以用来维护数据库的完整性，它保证成批的MySql操作要么完全执行，要么完全不执行，维护数据数据的完全。

首先我们要明白，不是所有的搜索引擎都支持事务，我们前边介绍过，MyISam和InnoDB是俩种最常用的引擎，前者不支持事务的处理，但其支持全文本搜索，后者支持事务处理，但不支持全文本搜索。

关系型数据库将数据存储到多张表中，当我们进行某些数据库操作时，可能会同时涉及到多种表，并且可能会同时有多人操作同一张表，此时怎么保证数据库的稳定及数据安全，就变得极为重要了。

假设，我们需要向系统添加新的订单信息，其添加的过程如下：

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），可以对它发布回退（与回退整个事务处理不同）。

2.事务的4个特性ACID

一般来说，事务是必须满足4个条件（ACID）：原子性（Atomicity，或称不可分割性）、一致性（Consistency）、隔离性（Isolation，又称独立性）、持久性（Durability）。

• 原子性：一个事务（transaction）中的所有操作，要么全部完成，要么全部不完成，不会结束在中间某个环节。事务在执行过程中如果发生错误，会被回滚（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。
• 一致性：在事务开始之前和事务结束以后，数据库的完整性没有被破坏。这表示写入的资料必须完全符合所有的预设规则，这包含资料的精确度、串联性以及后续数据库可以自发性地完成预定的工作。
• 隔离性：数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。
• 持久性：事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失。

事务的4个特性保证了数据库的完整性及其存储数据的安全性，对于一个成熟的应用程序来说，保证其事务的正确，是十分重要的。

3.事务的隔离级别

3.1 隔离级别分类

事务隔离分为不同级别，包括

• 读未提交（read uncommitted） 安全性最差,可能发生并发数据问题,性能最好
• 读已提交（read committed） Oracle默认的隔离级别
• 可重复读（repeatable read）MySQL默认的隔离级别,安全性较好,性能一般
• 串行化（Serializable） 表级锁，读写都加锁，效率低下,安全性高,不能并发

可重复读是MySql默认的隔离级别，它确保同一事务的多个实例在并发读取数据时，会看到同样的数据行，其即能保证数据的安全，性能也比较高效。在程序开发中，不可能存在即安全又高效的方法，所以我们在实际的使用，要尽可能兼顾性能与安全性。

3.2 隔离级别的相关操作

3.2.1 数据库默认提交方式查看修改

我们可以使用下列的SQL语句来查询数据库当前的提交方式：

输入：

SHOW VARIABLES LIKE 'autocommit';

输出：

我们可以看到其值为 ‘ON’，这表明当前数据库的自动提交是打开的状态。我们可以通过下列的命令，更改其VALUE值为OFF来关闭数据库自动提交：

输入：

SET autocommit = off;

3.2.2 数据库隔离级别查看修改

我们可以使用下列的SQL语句来查询数据库当前的隔离级别：

输入：

SHOW VARIABLES LIKE 'transaction_isolation';

输出：

我们可以看到，当前的隔离级别为repeatable read，通过下列的命令，我们可以修改其隔离级别：

输入：

SET SESSION ISOLATION LEVEL READ UNCOMMITTED;

我们这里将其设置为了读未提交。

3.2.3 不同隔离级别解决的问题

	脏写	脏读	不可重复读	幻读
读未提交	√	×	×	×
读已提交	√	√	×	×
可重复读	√	√	√	×
可串行化	√	√	√	√

• 脏写（事务1修改的数据被事务2给回滚了）

	事务A	事务B
T1	begin
T2		begin
T3		update xxx set password = 1000 where id =1
T4	update xxx set password = 1000 where id =1
T5	commit
T6		rollback

这里可以看到事务A在修改完id为1的password并进行事务的提交之后，事务2在T6时刻进行了事务的回滚，这就导致了事务A对数据的修改丢失了，这是一个非常严重的错误，即自己修改的数据被被人给回滚了，在任何隔离级别的情况下其都可以解决的是脏写问题。

• 脏读（事务1读到其他事务修改但是没有提交的信息、临时信息）

	事务A	事务B
T1	begin
T2		begin
T3		update xxx set password = 1000 where id =1
T4	update xxx set password = 1000 where id =1
T5	commit
T6		rollback

这里事务A在T4读取到了事务B针对id=1的用户的password修改，但是实际上事务B在最后进行了事务的回滚，这就导致了实际上事务A读到的是一个不存在的数据，也就是事务A读到了其他事务修改但是没有提提交的记录信息。在读已提交的隔离级别下就解决了脏读问题。

• 不可重复读（在同一事务进行多次的查询操作的时候，查询的结果是不一致的）

	事务A	事务B
T1	begin
T2		begin
T3	select password from xxx where id = 1
T4		update xxx set password = 11 where id =1
T5	select password from xxx where id = 1
T6		update xxx set password = 22 where id =1
T7	select password from xxx where id = 1
T8		update xxx set password = 22 where id =1
T9	commit
T10		commit

在T3、T5、T7时刻事务A对id=1的账号进行password字段的查询，发现该数据一致是变化的，这就是不可重复读的问题，在同一事务中每次进行查询的时候数据都是在变化的，在可重复读的隔离级别下解决了不可重复读的问题。

• 幻读（在同一事务中查询的时候发现多了很多条记录，称为幻影记录）

	事务A	事务B
T1	begin
T2		begin
T3	select password from xxx where id > 100
T4		insert into xxx(id,password) values (111,11)
T5	select password from xxx where id > 100
T6		insert into xxx(id,password) values (112,11)
T7	select password from xxx where id > 100
T8		insert into xxx(id,password) values (113,11)
T9	commit
T10		commit

在T3、T5、T7时刻事务A对id大于100的信息进行查询的时候，发现在同一事务中每次查询的时候都会有新的数据的出现，这就是幻读问题，在可串行化（序列化）的隔离级别下解决了幻读的问题。

4. 事务处理的控制

4.1 使用ROLLBACK

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

输入：

SELECT * FROM ordertotals;
START TRANSACTION;
DELETE FROM ordertotals;
SELECT * FROM ordertotals;
ROLLBACK;
SELECT * FROM ordertotals;

上面的例子在执行时，请一条一条执行，这样可以更加直观的看到其结果变化的不同。

1. 这个例子最开始查询ordertotals表中的所有数据；
2. 然后开始事务控制；
3. 接着删除ordertotals表中的所有行；
4. 删除后查询表中的所有内容，验证其已经被删除；
5. 验证后使用 rollback关键字回退 START TRANSACTION后，ROLLBACK关键字前的所有内容；
6. 再次验证该表中的所有内容，此时被删除的数据还全部存在；

显然，ROLLBACK关键字只能在一个事务处理内使用。

4.2 使用COMMIT

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

但是在事务处理块中，提交不会隐式进行。为了进行明确的提交，我们需要使用COMMIT关键字，例如：

输入：

START TRANSACTION;
DELETE FROM orderitems WHERE order_num = 20006;
DELETE FROM orders WHERE order_num = 20006;
COMMIT;

在这个例子中，我们要删除订单20006，因为要同时更新俩张表内的数据，所以使用事务控制块来保证订单不被部分删除，最后COMMIT关键字仅在不出错时写出更改。如果第一条删除语句成功，但第二条删除语句失败，那么俩条删除语句都不会执行，会被自动撤回。

4.3 使用保留点

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

例如前面描述的添加订单的过程就是一个事务。如果发生错误，只需要返回到添加 Orders 行之前即可。不需要回退到 Customers 表（如果存在的话）。

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

在 SQL 中，这些占位符称为保留点。可使用 SAVEPOINT 语句来设置保留点：

SAVEPOINT TO deletel;

每个保留点都要取能够标识它的唯一名字，以便在回退时，DBMS 知道回退到何处。要回退到本例给出的保留点，在 SQL Server 中可如下进行：

ROLLBACK TO deletel;

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

保留点在事务处理完成后自动释放，当然也可以使用 RELESAE SAVEPOINT 明确的释放保留点。