07.数据库事
庭院深深深几许,杨柳堆烟,帘幕无重数。
——欧阳修《蝶恋花》
事务
什么是事务?
在实际的开发过程中,一个业务操作如:转账,往往是要多次访问数据库才能完成的。转账是一个用户扣钱,另一个用户加钱。如果其中有一条 SQL 语句出现异常,这条 SQL 就可能执行失败。
事务执行是一个整体,所有的 SQL 语句都必须执行成功。如果其中有 1 条 SQL 语句出现异常,则所有的 SQL 语句都要回滚,整个业务执行失败。
例如转账的操作
如果你不是很明白可以通过下面这个例子来结合着理解!
1. 通过案例理解事务
1 | -- 数据准备 |
模拟dd给mm转 500 元钱,一个转账的业务操作最少要执行下面的 2 条语句:
dd money-500
mm money+500
1 | -- dd money-500 |
假设当张三账号上-500 元,服务器崩溃了。李四的账号并没有+500 元,数据就出现问题了。我们需要保证其中一条 SQL 语句出现问题,整个转账就算失败。只有两条 SQL 都成功了转账才算成功。这个时候就需要用到事务。
2. 提交事务的两种方式
2.1 手动提交事务
1 | graph TD; |
总结:
- 如果事务中 SQL 语句没有问题,commit 提交事务,会对数据库数据的数据进行改变。 如果事务中 SQL 语句有问题,rollback 回滚事务,会回退到开启事务时的状态。
2.2 自动提交事务
默认我们执行的一次SQL语句都是一次事务
1 | -- 查看 MySQL 是否开启自动提交事务 |
3. 事务原理
事务开启之后, 所有的操作都会临时保存到事务日志中, 事务日志只有在得到 commit 命令才会同步到数据表中,其他任何情况都会清空事务日志(rollback,断开连接)
3.1 事务执行步骤
客户端连接数据库服务器,创建连接时创建此用户临时日志文件
开启事务以后,所有的操作都会先写入到临时日志文件中
所有的查询操作从表中查询,但会经过日志文件加工后才返回
如果事务提交则将日志文件中的数据写到表中,否则清空日志文件。
4. 回滚点
什么是回滚点?
- 在某些成功的操作完成之后,后续的操作有可能成功有可能失败,但是不管成功还是失败,前面操作都已经成功,可以在当前成功的位置设置一个回滚点。可以供后续失败操作返回到该位置,而不是返回所有操作,这个点称之为回滚点。
1 | -- 设置回滚点: |
4.1 具体步骤
- 将数据还原到 1000
- 开启事务
- 让dd账号减 3 次钱,每次 10 块
- 设置回滚点:savepoint three_times;
- 让dd账号减 4 次钱,每次 10 块
- 回到回滚点:rollback to three_times;
- 分析执行过程
设置回滚点可以让我们在失败的时候回到回滚点,而不是回到事务开启的时候。
5. 事务的隔离级别
5.1 事务的四大特性 ACID
事务特性 | 含义 |
---|---|
原子性(Atomicity) | 每个事务都是一个整体,不可再拆分,事务中所有的 SQL 语句要么都执行成功,要么都失败。 |
一致性(Consistency) | 事务在执行前数据库的状态与执行后数据库的状态保持一致。如:转账前 2 个人的总金额是 2000,转账后 2 个人总金额也是 2000 |
隔离性(Isolation) | 事务与事务之间不应该相互影响,执行时保持隔离的状态。 |
持久性(Durability) | 一旦事务执行成功,对数据库的修改是持久的,就算关机,也是保存下来的。 |
5.2 并发访问的三种问题
事务在操作时的理想状态:
- 所有的事务之间保持隔离,互不影响。因为并发操作,多个用户同时访问同一个数据。可能引发并发访问的问题:
5.2.1 脏读
一个事务读取到了另一个事务中尚未提交的数据
脏读演示:
- 设置隔离级别:Read Uncommitted
- 打开A,B两个DOS窗口,分别开启两个事务
- A 窗口更新 2 个人的账户数据,不执行提交事务。
- B窗口查询账户数据,此时查询的是更新后的数据。
- A窗口回滚。
- B 窗口再次查询账户,发现又回到了A窗口执行更新数据之前的状态。
所以脏读非常危险的,比如dd向mm购买商品,dd开启事务,向mm账号转入 500 块,然后打电话给mm说钱已经转了。mm一查询钱到账了,发货给dd。dd收到货后回滚事务,mm的再查看钱没了。
Read Committed 的隔离级别可以避免脏读的发生
避免脏读演示:
- 设置隔离级别:Read Committed
- 打开A,B两个DOS窗口,分别开启两个事务
- A 窗口更新 2 个人的账户数据,不执行提交事务。
- B窗口查询账户数据。这时候读到的A窗口更新之前的数据,即避免了脏读。
- A窗口执行提交事务。
- B窗口查询账户数据,得到的是正常的数据。
5.2.2 不可重复读
一个事务中两次读取的数据内容不一致,要求的是一个事务中多次读取时数据是一致的。
不可重复读演示:
- 设置隔离级别:Read Committed
- 开启A窗口。
- 开启 B 窗口,在 B 窗口开启事务,执行第一次查询账户数据。
- 在 A 窗口开启事务,并更新数据,执行提交事务。
- B窗口执行第二次查询账户数据。
- 两次查询输出的结果不同,到底哪次是对的?不知道以哪次为准。 很多人认为这种情况就对了,无须困惑,当然是后面的为准。
比如银行程序需要将查询结果分别输出到电脑屏幕和发短信给客户,结果在一个事务中针对不同的输出目的地进行的两次查询不一致,导致文件和屏幕中的结果不一致,银行工作人员就不知道以哪个为准了。
Repeatable Read 隔离级别可以避免脏读和不可重复读的发生
解决不可重复读演示:
- 设置隔离级别:Repeatable Read
- 打开A,B两个DOS窗口,分别开启两个事务。
- B窗口执行第一次查询账户数据。
- A 窗口更新账户数据,提交事务。
- B窗口执行第二次查询账户数据,此时两次查询的账户数据是一致的。
5.2.3 幻读
一个事务中两次读取的数据的数量不一致,要求在一个事务多次读取的数据的数量是一致的,这是 insert 或 delete 时引发的问题。
- 在MySQL 中无法看到幻读的效果,但我们可以将事务隔离级别设置到最高,以挡住幻读的发生 。
- 设置隔离级别:Serializable
- 打开A,B两个DOS窗口,分别开启两个事务。
- B窗口执行第一次查询,通过 聚合函数count(*) 账户表中记录的条数,不提交事务。
- A窗口执行插入一条记录,会发现这个操作无法进行,光标一直闪。
- 在B窗口中 commit 提交事务,B 窗口中 insert 语句会在 A 窗口事务提交后立马运行。
- 在 B窗口中接着查询,发现数据不变(是因为A窗口的事务还没有提交)。
- A 窗口中 commit 提交当前事务。
- B窗口就能看到最新的数据。
使用 Serializable 隔离级别,一个事务没有执行完,其他事务的 SQL 执行不了,可以挡住幻读。
5.3 数据库四种隔离级别
隔离级别越高,性能越差,安全性越高。
级别 | 名字 | 隔离级别 | 脏读 | 不可重复读 | 幻读 | 数据库默认隔离级别 |
---|---|---|---|---|---|---|
1 | 读未提交 | read uncommitted | 是 | 是 | 是 | |
2 | 读已提交 | read committed | 否 | 是 | 是 | Oracle 和 SQL Server |
3 | 可重复读 | repeatable read | 否 | 否 | 是 | MySQL select @@tx_isolation |
4 | 串行化 | serializable | 否 | 否 | 否 |
查询全局事务隔离级别
1 | select @@tx_isolation; |
设置事务隔离级别,需要退出 MySQL 再重新登录才能看到隔离级别的变化
1 | set global transaction isolation level 级别字符串; |