1、事务隔离级别
两次转账
转账前:A:11 B:2 总和:13
转账后:A:6 B:12 总和:18
违反了一致性
1.1 事务并发遇到的不一致性问题:
w=write c=commit a=abort w1=事务T1写 x,y初始值为0
-
脏写:一个事务修改了另一个未提交事务修改过的数据
w1[x=1]w2[x=2]w2[y=2]c2w1[y=1]c1 最终x=2,y=1破坏了一致性;
w1[x=2]w2[x=3]w2[y=3]c2a1 a1将会对x=2进行回滚到0,此时T2已经将x=3持久化。破坏了原子性和持久性。
-
脏读:一个事务读到了另一个未提交事务修改过的数据
-
w1[x=1]r2[x=1]r2[y=0]c2w1[y=1]c1 T2得到了一个不一致的状态
-
w1[x=1]r2[x=1]a1 T2读取了一个不存在的值x=1
-
-
不可重复读:一个事务修改了另外一个未提交事务读取的数据
r1[x=1]w2[x=2]c2r1[x=2]c1 两次读取不一致 -
幻读:一个事务按照某个搜索条件读取多次记录时,在后读取时读到了之前没有读到的记录 P:符合搜索条件P的记录,[y in P]表示写入一些符合P条件的记录——多出来的记录可以称之为幻影记录
r1[P]w2[y in P]c2r1[P]c1
解决一致性的两种方式
- MVCC
- 锁
1.2 SQL标准中的四种隔离级别
- READ UNCOMMITTED:读未提交
- READ COMMITTED:读已提交
- REPEATABLE READ:可重复读
- SERIALIZABLE:可串行化
隔离级别 | 脏读 | 不可重复读 | 幻读 |
---|---|---|---|
READ UNCOMMITTED | 可能 | 可能 | 可能 |
READ COMMITTED | 不可能 | 可能 | 可能 |
REPEATABLE READ | 不可能 | 不可能 | 可能 |
SERIALIZABLE | 不可能 | 不可能 | 不可能 |
设置隔离级别
SET [GLOBAL|SESSION] TRANSACTION ISOLATION LEVEL level
MySQL的默认隔离级别是啥?为什么?
https://dev.mysql.com/doc/refman/8.0/en/set-transaction.html
我们平时用的隔离级别是啥?
2、MVCC原理
2.1 版本链
create table hero(
number int,
name varchar(100),
country varchar(100),
PRIMARY KEY(number)
) Engine=InnoDB CHARSET=utf-8;
insert into hero values(1,'刘备');
number | name | country |
---|---|---|
1 | 刘备 | 蜀 |
InnoDB聚簇索引中两个必要的隐藏列:
- trx_id:修改聚簇索引记录的事务id
- roll_pointer: 对聚簇索引进行改动时,都会把旧的版本写入到
undo
日志中。roll_pointer
相当于一个指针,指向的就是修改前的信息
事务id分配时机
-
普通的select语句执行过程中不会分配事务id,
事务id默认是0
; -
修改语句会分配一个事务id,且事务id是自增的。
undo log 的类型:
- insert 类型: 事务提交后就没用了
- update类型: 事务提交后还需要供
MVCC
使用
2.2 ReadView(一致性视图)
READ UNCOMMITED:只需要读取最新版本记录即可;
READ COMMITED、REPEATABLE READ: 需要保证读到的已经提交的事务修改的记录
也就是版本链中的哪个版本的数据对当前事务是可见的
ReadView结构:
- m_ids:生成ReadView时,当前系统中活跃的读写事务的事务id列表
- min_trx_id:生成ReadView时,当前系统中活跃的读写事务中最小的事务id;也就是m_ids中的最小值
- max_trx_id:生成ReadView时系统应该分配给下一个事务的事务id
(不一定是m_ids中的最大值)
- creator_trx_id:生成ReadView的事务的事务id
判断规则
被访问版本的trx_id等于creator_trx_id:可以访问当前版本
被访问版本的trx_id小于min_trx_id:可以访问
被访问版本的trx_id大于max_trx_id:不可以访问
min_trx_id<被访问版本的trx_id<max_trx_id:
- 被访问版本的trx_id in (m_ids):不可访问
- 可以访问
READ COMMITED ——-每次读取数据前都生成一个ReadView
#transaction 100 #transaction 200
begin; begin;
update hero set name = '关羽' where number = 1; #更新其他一些别的表记录
update hero set name = '张飞' where number = 1;
#使用READ COMMITED隔离级别的事务
begin;
select * from hero where number = 1;#得到的值是多少?
再来一次
#transaction 100 #transaction 200
begin; begin;
update hero set name = '关羽' where number = 1; #更新其他一些别的表记录
update hero set name = '张飞' where number = 1;
------------------------------------------------------------------------------------------------------------
commit;
update hero set name = '赵云' where number = 1;
update hero set name = '诸葛亮' where number = 1;
#使用READ COMMITED隔离级别的事务
begin;
#事务100、200都未提交
select * from hero where number = 1;#得到的值是多少?
#事务100提交,200未提交
select * from hero where number = 1;#得到的值是多少?
REPEATABLE READ ——-只会在第一次读取数据时生成一个ReadView
注:MySQL中的REPEATABLE READ可以很大程度上禁止幻读
2.3 二级索引与MVCC
只有在聚簇索引中才会有trx_id和roll_pointer隐藏列。如果查询语句使用二级索引查询,如何判断可见性呢?
begin
select name from hero where name = '刘备'
- 二级索引页面的PageHeader有一个PAGE_MAX_TRX_ID属性,记录着当前页的最大事务id;当前生成ReadView中的
min_trx_id
是否大于PAGE_MAX_TRX_ID,若是则可见,否则下一步进行判断 - 通过回表,去比对版本链中的trx_id。判断可见版本中的二级索引值是否与查询时相同。
delete并不会立即删除对应的记录,而是执行一个delete mark操作,相当于打一个标记,主要就是为MVCC服务的
例如:在可重复读级别中条件搜索
MVCC小结
所谓的mvcc就是在READ COMMITED、REPEATABLE READ这两种隔离级别的事务执行普通的SELECT操作时,访问记录的版本链的过程。
可以对不同事务的读 – 写、写 – 读并发执行,从而提高系统性能。
关于Purge
把update undo日志以及仅仅标记删除的记录彻底删除,这个删除操作就叫purge。
Rollback Segment Header中的两个属性:
-
TRX_RSEG_HISTORY:History链表的基节点
-
TRX_RSEG_HISTORY_SIZE:History链表占用的页面数量
怎么判断是undo日志是否可以删除呢?
-
在事务提交时,会为这个事务生成一个事务no,表示事务提交的顺序,先提交的事务no小,提交的事务no大。
事务no将会填到undo日志的TRX_UNDO_TRX_NO属性上。因为History链表是按照事务提交的顺序来排列undo日志的。索引History链表中的undo日志也是按照事务no来排序的
-
一个ReadView除了前面的属性之外,还有一个事务no,生成ReadView时,会把比系统中最大的事务no还大1的值赋给这个属性。
InnoDB会把当前系统中所有的ReadView按照创建时间连成一个链表。
判断逻辑
将最早生成的ReadView取出来,然后从各个回滚段的History链表中取出事务no较小的各组undo日志。如果一组undo日志的事务no小于当前系统中最早生成的ReadView的事务no属性,那么就意味着undo日志没有用了。可以从链表中移除,释放空间。