MySQL高阶之事务原理篇
insert语句流程图(InnoDB引擎)
InnoDB引擎:开启事务(增删改操作都会默认开启事务和提交事务)->加插入意向锁->Undo_log(回滚日志)的的redo_log(重写日志)的buffer->记录redo_log的buffer(缓冲区)->记录变更的redo_log->更新数据页->事务提交 redo_log落磁盘->释放锁->结束
MySQL四大特性ACID
- 原子性(atomicity) :事务最小工作单元,要么全成功,要么全失败 。
- 一致性(consistency): 事务开始和结束后,数据库的完整性不会被破坏 。
- 隔离性(isolation) :不同事务之间互不影响,四种隔离级别为RU(读未提交)、RC(读已提交)、
RR(可重复读)、SERIALIZABLE (串行化) - 持久性(durability) :事务提交后,对数据的修改是永久性的,即使系统故障也不会丢失 。
隔离级别详解
读未提交-脏读(一个事务读到另一个事务没有提交的数据)
造成危险后果:读的一个值 别人回滚了不打算提交 此时就是个脏数据 十分不安全 不建议使用
读已提交-不可重复读(两次读到的结果可能是不同的)-可用的隔离级别
可重复读-经常用到的隔离级别(Mysql默认隔离级别)
在事务还没提交之前,相同的sql语句查询到的结果是相同的,不会受其他已经提交的事务的影响
串行化
一个事务提交完另外一个事务才能开始执行,性能很差,不推荐使用
事务带来的几个问题
问题1 丢失更新问题
问题2 丢失更新问题
问题3 数据读取异常
解决方案 针对问题3
解决方案1:针对第三个问题数据读取异常可以采用 LBCC (Lock Based Concurrency Control)基于锁的并发控制简单粗暴但是性能低下,连读一条数据都需要上锁,不允许其他事务参与。不建议使用
解决方案2:MVCC(Multi Version Concurrency Control)机制 基于版本的并发控制 快照版本,读写不冲突。
InnoDB的MVCC实现
undo_log和read view
undo_log(数据回滚 逆转)
存储了行的主键RowID(唯一)、修改这行数据的事务id、回滚指针(回滚到之前的版本)、版本、行数据
insert undo log:
因为 insert 操作的记录只对事务本身可见,对于其它事务此记录是不可见的,所以 insert undo
log 可以在事务提交后直接删除而不需要进行 purge 操作。
update undo log:
是 update 或 delete 操作中产生的 undo log。
因为会对已经存在的记录产生影响,为了提供 MVCC机制,因此 update undo log 不能在事务提交
时就进行删除,而是将事务提交时放到入 history list 上,等待 purge 线程进行最后的删除操作。
刘备是原始值、张飞是update之前做的一条undo_log
read view(可读视图版本链)
只针对读已提交和可重复读两种隔离级别生效。用来判断undo_log中的哪些版本是可见的
变量:
m_ids:记录了当前数据库中活跃的事务id(意味着是一个还未提交的事务) 是一个数组 降序排序
m_up_limit_id:事务id下限 m_ids中最小的事务id
m_low_limit_id:mysql系统即将生成的下一个事务id 也就是即将是最大的事务id
假设即将生成的事务id=160 那么此时readview的区间是 110<=readview<160
通过Readview的可见性判断:
- 1.读取id为1的记录,判断版本链中哪个版本是可见的,需要通过Readview来判断
- 2.如果版本链中的版本小于readView的最小值那么认为是可见的(小于110)
- 3.如果版本链中的版本大于等于readview的的最大值那么是不可见的
- 4.如果版本的事务id大于等于readview的最小值,小于readview的最大值,判断事务id是否在m_ids数组中存在,那么不可见,如果不存在,可见。
注意 只针对读已提交和可重复读两种隔离级别生效。用来判断undo_log中的哪些版本是可见的
READ COMMITTED隔离级别案例
原始值
id=1 c="刘备"
1.Transaction 100:注意 未提交事务
BEGIN;
UPDATE t SET c = '关羽' WHERE id = 1;
UPDATE t SET c = '张飞' WHERE id = 1;
2.Transaction 200
# Transaction 200
BEGIN;
# 更新了一些别的表的记录
3.此时有两个id分别为100、200的事务在执行
此时t表中id=1的记录得到的版本链如下
4.现在有一个READ COMMITTED隔离级别的事务A开始执行
# 使用READ COMMITTED隔离级别的事务A
BEGIN;
# SELECT1:Transaction 100、200未提交
SELECT * FROM t WHERE id = 1; # 得到的列c的值为'刘备'
解析:此时select1的执行过程如下:
- 此时执行SELECT * FROM t WHERE id = 1会先生成一个ReadView,ReadView中的m_ids的值为[100,200] (注意此时事务100和事务200都未提交 属于活跃事务)
- 此时从版本链中挑选可见的记录。id=1的这条记录最新的版本内容c字段是“张飞”,但是该事务id的值是100,是存在m_ids中的,此时不符合可见性版本,根据roll_pointer回滚指针指向下一个版本。
- 下一个版本的内容是关羽,同理事务id也是100,属于不可见版本,继续 跳下一个版本
- 下一个版本c字段的内容是刘备,此时事务id是80,小于m_ids中的下限值100,因此该版本是可见的,所以此时将刘备这个版本记录返回给客户端。查询到的c就是’刘备’
- 请注意:此时事务A并没有提交
5.此时将事务100提交
#事务100
BEGIN;
UPDATE t SET c = '关羽' WHERE id = 1;
UPDATE t SET c = '张飞' WHERE id = 1;
COMMIT;
6.此时事务200更新以下id=1的记录 注意此时没有提交事务
# Transaction 200
BEGIN;
UPDATE t SET c = '赵云' WHERE id = 1;
UPDATE t SET c = '诸葛亮' WHERE id = 1;
7.此时id=1的版本链
8.此时使用刚才未提交的事务A来查询id=1的记录
# 使用READ COMMITTED隔离级别的事务A
BEGIN;
# SELECT1:条件:Transaction 100、200未提交
SELECT * FROM t WHERE id = 1; # 得到的列c的值为'刘备'
# SELECT2:条件:Transaction 100提交,Transaction 200未提交
SELECT * FROM t WHERE id = 1; # 得到的列c的值为'张飞'
select1获取到的c值是"刘备":
- Transaction 100、200未提交 所以是是刘备
select2得到的列c的值为’张飞’:
- 此时执行select语句就会生成一个ReadView,m_ids的列表[200] (请注意此时事务100已经提交,不再是活跃的事务)
- 此时判断id=1最新的记录是’诸葛亮’,该事务id存在于m_ids,因此不可见。
- 下一个版本同理 是’赵云’,也不可见
- 下一个版本是’张飞’,此时判断他的事务id是否小于m_ids的下限200,是符合的,因此将这条记录’张飞’返回刻划断,因此select2查询到的结果值是’张飞’
一句话总结:RC隔离级别在开启事务后进行的每一次select语句都会新生成一个独立ReadView
REPEATABLE READ隔离级别案例
1.事务100
# Transaction 100
BEGIN;
UPDATE t SET c = '关羽' WHERE id = 1;
UPDATE t SET c = '张飞' WHERE id = 1;
2.事务200
# Transaction 200
BEGIN;
# 更新了一些别的表的记录
3.此刻,表t中id为1的记录得到的版本链
4.事务A使用REPEATABLE READ隔离级别的事务开始执行:
# 使用REPEATABLE READ隔离级别的事务
BEGIN;
# SELECT1:Transaction 100、200未提交
SELECT * FROM t WHERE id = 1; # 得到的列c的值为'刘备'
select1:
- readView值[100, 200] 只有事务80的条件符合。因此返回刘备
5.此时事务100提交
# Transaction 100
BEGIN;
UPDATE t SET c = '关羽' WHERE id = 1;
UPDATE t SET c = '张飞' WHERE id = 1;
COMMIT;
6.事务200更新记录
# Transaction 200
BEGIN;
# 更新了一些别的表的记录
UPDATE t SET c = '赵云' WHERE id = 1;
UPDATE t SET c = '诸葛亮' WHERE id = 1;
7.此时版本链
8.此时使用未提交的事务A查询
# 使用REPEATABLE READ隔离级别的事务
BEGIN;
# SELECT1:Transaction 100、200均未提交
SELECT * FROM t WHERE id = 1; # 得到的列c的值为'刘备'
# SELECT2:Transaction 100提交,Transaction 200未提交
SELECT * FROM t WHERE id = 1; # 得到的列c的值仍为'刘备
select2解析:
- 由于select1执行就已经生成ReadView了,此时执行select2不会再次生成一个ReadView,而是直接复用,那么同理查到的值是’刘备’。这就是可重复读的含义。此时就算事务200的修改也提交了,事务A读取到的值仍然是’刘备’
一句话总结:RR隔离级别在开启事务后进行第一次select语句就会生成一个独立ReadView,后续在这个事务中将复用这个ReadView
MVCC下的读操作:
当前读:需要加锁 保证其他事务不会并发修改这条记录
快照读:读取的记录是可见版本,不需要加锁。不一定是最新的值。提高并发能力
事务回滚和数据恢复
- 事务的回滚主要依赖undo log的回滚指针
- 数据恢复依赖redo log以及checkoutpoint机制