7. 数据库事务详解

数据库事务

什么是事务？

• 在实际的开发过程中，一个业务操作如：转账，往往是要多次访问数据库才能完成的。转账是一个用户扣钱，另一个用户加钱。如果其中有一条 SQL 语句出现异常，这条 SQL 就可能执行失败。

• 事务执行是一个整体，所有的 SQL 语句都必须执行成功。如果其中有 1 条 SQL 语句出现异常，则所有的 SQL 语句都要回滚，整个业务执行失败。

• 例如转账的操作

• 如果你不是很明白可以通过下面这个例子来结合着理解！

1. 通过案例理解事务

-- 数据准备
create table account (

id int primary key auto_increment,

name varchar(10),

money double

);

--	添加数据
insert into account (name, money) values ('dd', 1000), ('mm', 1000);

模拟dd给mm转 500 元钱，一个转账的业务操作最少要执行下面的 2 条语句：

• dd money-500

• mm money+500

--	dd   money-500
update account set balance = money - 500 where name='dd';

--	mm   money+500
update account set balance = money + 500 where name='mm';

假设当dd账号上-500 元,服务器崩溃了。mm的账号并没有+500 元，数据就出现问题了。我们需要保证其中一条 SQL 语句出现问题，整个转账就算失败。只有两条 SQL 都成功了转账才算成功。这个时候就需要用到事务。

2. 提交事务的两种方式

2.1 手动提交事务

总结:

• 如果事务中 SQL 语句没有问题，commit 提交事务，会对数据库数据的数据进行改变。 如果事务中 SQL 语句有问题，rollback 回滚事务，会回退到开启事务时的状态。

2.2 自动提交事务

默认我们执行的一次SQL语句都是一次事务

-- 查看 MySQL 是否开启自动提交事务
select @@autocommit;
-- @@表示全局变量，1 表示开启，0 表示关闭

-- 取消自动提交事务
set @@autocommit = 0;

3. 事务原理

事务开启之后, 所有的操作都会临时保存到事务日志中, 事务日志只有在得到 commit 命令才会同步到数据表中，其他任何情况都会清空事务日志(rollback，断开连接)

3.1 事务执行步骤

1. 客户端连接数据库服务器，创建连接时创建此用户临时日志文件

2. 开启事务以后，所有的操作都会先写入到临时日志文件中

3. 所有的查询操作从表中查询，但会经过日志文件加工后才返回

4. 如果事务提交则将日志文件中的数据写到表中，否则清空日志文件。

4. 回滚点

什么是回滚点？

• 在某些成功的操作完成之后，后续的操作有可能成功有可能失败，但是不管成功还是失败，前面操作都已经成功，可以在当前成功的位置设置一个回滚点。可以供后续失败操作返回到该位置，而不是返回所有操作，这个点称之为回滚点。

-- 设置回滚点： 
savepoint 名字；

-- 回到回滚点：
rollback to 名字

4.1 具体步骤

1. 将数据还原到 1000
2. 开启事务
3. 让dd账号减 3 次钱，每次 10 块
4. 设置回滚点：savepoint three_times;
5. 让dd账号减 4 次钱，每次 10 块
6. 回到回滚点：rollback to three_times;
7. 分析执行过程

设置回滚点可以让我们在失败的时候回到回滚点，而不是回到事务开启的时候。

5. 事务的隔离级别

5.1 事务的四大特性 ACID

事务特性	含义
原子性（Atomicity）	每个事务都是一个整体，不可再拆分，事务中所有的 SQL 语句要么都执行成功，要么都失败。
一致性（Consistency）	事务在执行前数据库的状态与执行后数据库的状态保持一致。如：转账前 2 个人的总金额是 2000，转账后 2 个人总金额也是 2000
隔离性（Isolation）	事务与事务之间不应该相互影响，执行时保持隔离的状态。
持久性（Durability）	一旦事务执行成功，对数据库的修改是持久的，就算关机，也是保存下来的。

5.2 并发访问的三种问题

事务在操作时的理想状态：

• 所有的事务之间保持隔离，互不影响。因为并发操作，多个用户同时访问同一个数据。可能引发并发访问的问题：

5.2.1 脏读

一个事务读取到了另一个事务中尚未提交的数据

脏读演示：

1. 设置隔离级别：Read Uncommitted
2. 打开A,B两个DOS窗口，分别开启两个事务
3. A 窗口更新 2 个人的账户数据，不执行提交事务。
4. B窗口查询账户数据，此时查询的是更新后的数据。
5. A窗口回滚。
6. B 窗口再次查询账户，发现又回到了A窗口执行更新数据之前的状态。

​ 所以脏读非常危险的，比如dd向mm购买商品，dd开启事务，向mm账号转入 500 块，然后打电话给mm说钱已经转了。mm一查询钱到账了，发货给dd。dd收到货后回滚事务，mm的再查看钱没了。

Read Committed 的隔离级别可以避免脏读的发生

避免脏读演示:

1. 设置隔离级别：Read Committed
2. 打开A,B两个DOS窗口，分别开启两个事务
3. A 窗口更新 2 个人的账户数据，不执行提交事务。
4. B窗口查询账户数据。这时候读到的A窗口更新之前的数据，即避免了脏读。
5. A窗口执行提交事务。
6. B窗口查询账户数据，得到的是正常的数据。

5.2.2 不可重复读

一个事务中两次读取的数据内容不一致，要求的是一个事务中多次读取时数据是一致的。

不可重复读演示：

1. 设置隔离级别：Read Committed
2. 开启A窗口。
3. 开启 B 窗口，在 B 窗口开启事务，执行第一次查询账户数据。
4. 在 A 窗口开启事务，并更新数据，执行提交事务。
5. B窗口执行第二次查询账户数据。
6. 两次查询输出的结果不同，到底哪次是对的？不知道以哪次为准。 很多人认为这种情况就对了，无须困惑，当然是后面的为准。

​ 比如银行程序需要将查询结果分别输出到电脑屏幕和发短信给客户，结果在一个事务中针对不同的输出目的地进行的两次查询不一致，导致文件和屏幕中的结果不一致，银行工作人员就不知道以哪个为准了。

Repeatable Read 隔离级别可以避免脏读和不可重复读的发生

解决不可重复读演示：

1. 设置隔离级别：Repeatable Read
2. 打开A,B两个DOS窗口，分别开启两个事务。
3. B窗口执行第一次查询账户数据。
4. A 窗口更新账户数据，提交事务。
5. B窗口执行第二次查询账户数据，此时两次查询的账户数据是一致的。

5.2.3 幻读

一个事务中两次读取的数据的数量不一致，要求在一个事务多次读取的数据的数量是一致的，这是 insert 或 delete 时引发的问题。

1. 在MySQL 中无法看到幻读的效果，但我们可以将事务隔离级别设置到最高，以挡住幻读的发生 。
2. 设置隔离级别：Serializable
3. 打开A,B两个DOS窗口，分别开启两个事务。
4. B窗口执行第一次查询，通过 聚合函数count(*) 账户表中记录的条数，不提交事务。
5. A窗口执行插入一条记录，会发现这个操作无法进行，光标一直闪。
6. 在B窗口中 commit 提交事务，B 窗口中 insert 语句会在 A 窗口事务提交后立马运行。
7. 在 B窗口中接着查询，发现数据不变(是因为A窗口的事务还没有提交)。
8. A 窗口中 commit 提交当前事务。
9. B窗口就能看到最新的数据。

使用 Serializable 隔离级别，一个事务没有执行完，其他事务的 SQL 执行不了，可以挡住幻读。

5.3 数据库四种隔离级别

1. 一个事务与其他事务隔离的程度称为隔离级别
2. 隔离级别越高，性能越差，安全性越高。

级别	名字	隔离级别	脏读	不可重复读	幻读	数据库默认隔离级别
1	读未提交	read uncommitted	是	是	是
2	读已提交	read committed	否	是	是	Oracle 和 SQL Server
3	可重复读	repeatable read	否	否	是	MySQL select @@tx_isolation
4	串行化	serializable	否	否	否

3. 各种数据库产品对事务隔离级别的支持程度

	Oracle	MySQL
读未提交	×	√
读已提交	√	√
可重复读	×	√(默认)
串行化	√	√

查询全局事务隔离级别

select @@tx_isolation;

​

设置事务隔离级别，需要退出 MySQL 再重新登录才能看到隔离级别的变化

set global transaction isolation level 级别字符串;

☆

​