开讲之前首先我们先准备一个表 table_atm
| id | name | age |
|---|---|---|
| 1 | 迪迦奥特曼 | 18 |
| 2 | 泰罗奥特曼 | 19 |
| 3 | 赛文奥特曼 | 16 |
1 事务隔离级别
1.1 为什么要有事务隔离
1.1 什么事事务隔离
针对数据资源的并发访问，规定了各个事务之间相互影响的程度。我们称之为事务隔离
1.2 没有事务隔离的后果
如果多个事务同时访问同一数据，会发生哪些意想不到的情况？
- 脏读。事务读到了事务B还没提交的数据。举个实例
| 时间线 | 线程A | 线程B |
|---|---|---|
| 1 | begin transaction | |
| 2 | begin transaction | |
| 3 | update table_atm set age = 22 where id = 1 | |
| 4 | select age from table_atm where id = 1 | |
| 5 | commmit | |
| 6 | roolback |
步骤如上表，如果最线程B的事务回滚掉，相当于A读到了一条不存在的记录，这就是脏读
- 脏写
|时间线| 线程A | 线程B|
|-| --- | --- |
|1|
begin transaction| | |2| |begin transaction| |3| |update table_atm set age = 22 where id = 1| |4 |update table_atm set age = 25 where id = 1| | |5 |commmit| | |6 | |roolback|
如上表，A好不容易把数据改了，结果因为B回滚，导致A的更改小时不见了
- 不可重复读
|时间线| 线程A | 线程B|
|-| --- | --- |
|1|
begin transaction| | |2|select name fron table_atm where id = 1|begin transaction| |3| |update table_atm set age = 22 where id = 1| |4 |select name fron table_atm where id = 1|commmit| |5 |commmit| |
线程 A 2次读到的数据不一样
- 幻读
|时间线| 线程A | 线程B|
|-| --- | --- |
|1|
begin transaction| | |2|select name fron table_atm where id >= 1|begin transaction| |3| |insert into table_atm (name, age) VALUES('艾斯奥特曼'， 19)| |4|select name fron table_atm where id >= 1| | A 第一次读到的3条数据，第二次读到的竟然有4条
1.3 四大隔离级别
- 读未提交
READ UNCOMMITTED - 读已提交
READ COMMITTED - 可重复读
REPEATABLE READ - 串行化
SERIALIZABLE
| 隔离级别 | 脏读 | 不可重复读 | 幻读 |
|---|---|---|---|
读未提交 | Y | Y | Y |
读已提交 | N | Y | Y |
可重复读 | N | N | Y |
串行化 | N | N | N |
2 MVCC
2.1 版本链
Innodb 行记录中游2个不可或缺的字段
trx_id事务ID，任意事务对记录行进行更改后，都会把事务对应的事务ID 赋值给该字段roll_pointer回滚指针，对记录行进行改动的时候，原始版本数据都会写到undo log中，roll_pointer该字段指向undo log中的对应的原始数据 比如我们针对table_atm进行如下更改
| 时间线 | 操作 |
|---|---|
| 1 | update table_atm set age = 21 where id = 1 |
| 2 | update table_atm set age = 22 where id = 1 |
| 3 | update table_atm set age = 23 where id = 1 |
那么 id=1的版本链 如下图。其实就是将数据的各个版本通过链表的实行串联起来
2.2 视图
这个视图和我们常说的db视图是不一样，大家不要弄混 首先我们先隆重清楚视图中重要的四个字段
| 字段名 | 说明 |
|---|---|
m_ids | 生成视图时 当前活跃的所有的事务集合 |
min_trx_id | 生成视图时 当前活跃的所有的事务集合中最小的那个 trx_id |
max_trx_id | 注意哈，这个不是m_ids中最大的 事务id，而是生成视图时分配给下一个事务的 事务id |
creator_trx_id | 表示生成视图的 事务id，可能为0（只有执行INSERT、DELETE、UPDATE这些语句时 才会为事务分配事务id，否则在一个只读事务中的事务id值都默认为0） |
那我们如何根据这4个字段判断哪个版本的数据是当前视图可以访问的呢？
- 首先判断要访问记录的
trx_id（后续称之为目标trx_id） 是否与当前视图持有的creator_trx_id相同，如果相同说明在同一个事务，可见。 - 目标
trx_id是否小于min_trx_id？如果是的话，表明目标记录该版本的事务在生成视图之前提交，可见 - 目标
trx_id是否 大于等于max_trx_id？如果是的话，表明目标记录该版本的事务在生成视图之后提交，不可见 - 目标
trx_id大小 在min_trx_id和max_trx_id之间。目标trx_id在m_ids集合中，说明生成视图时 该版本的事务还是活跃的，不可见。否则说明该版本的事务已经提交，可见
如果某个版本的数据对当前视图不可见，那就要根据版本链找到下个版本的数据，然后在进行如上的判断，直到找到可见的数据或者 遍历完整个版本链也没找合适的数据
2.4 视图创建窗口（基于不同的隔离级别）
准备工作，定义一下2个事务
-
事务1 |时间线| 操作| |-| --- | |1|
BEGIN，举例分配的trx_id = 50| |2|update table_atm set age = 21 where id = 1| |3|update table_atm set age = 22 where id = 1| |4|update table_atm set age = 23 where id = 1| -
事务2 |时间线| 操作| |-| --- | |1|
begin举例分配的trx_id = 55| |2| 做一些其他操作，可以不是该数据行，主要是为了分配一个活跃的事务ID 没有提交哈|
版本链
而且此时 事务id为 50 55 的都没提交哈
READ COMMIT读取数据前都会生成视图 在上述2个事务的未提交的前提下，我们执行以下sql
| 时间线 | 操作 |
|---|---|
| 1 | BEGIN |
| 2 | select * from table_atm where id = 1 |
该select语句执行顺序如下
- 生成视图
ReadView,且此时活跃的mids为[50,55],min_trx_id= 50,max_trx_id= 56,creator_trx_id= 0 - 从版本链里找到可见的数据，如图所示
trx_id= 50 的 事务是当前视图中活跃的事务 故不可见 trx_id= 40的事务不在m_ids范围内，且比min_trx_id小，故可见
然后我们把trx_id = 50的事务提交一下
| 时间线 | 操作 |
|---|---|
| 1 | BEGIN，举例分配的 trx_id = 50 |
| 2 | update table_atm set age = 21 where id = 1 |
| 3 | update table_atm set age = 22 where id = 1 |
| 4 | update table_atm set age = 23 where id = 1 |
| 5 | commit |
| 时间线 | 操作 |
|---|---|
| 1 | BEGIN |
| 2 | select * from table_atm where id = 1 |
| 3 | select * from table_atm where id = 1 我们下面称之为 select2 语句 |
- 再执行
select2语句的时候又会生成一个新的视图，其中m_ids是[55],min_trx_id= 55,max_trx_id= 56,creator_trx_id= 0 - 从版本中挑选可见的数据，最新版本的
trx_id= 55， 包含在m_ids内，不可见，继续查找上一个版本链trx_id= 50,小于此时的min_trx_id。可见
总结:READ COMMIT 是在每个查询时新建视图
READ REPEATABLE第一次读取的时候生成视图 我们同样操作上述流程
| 时间线 | 操作 |
|---|---|
| 1 | BEGIN |
| 2 | select * from table_atm where id = 1 |
该select语句执行顺序如下
- 生成视图
ReadView,且此时活跃的mids为[50,55],min_trx_id= 50,max_trx_id= 56,creator_trx_id= 0 - 从版本链里找到可见的数据，如图所示
trx_id= 50 的 事务是当前视图中活跃的事务 故不可见 trx_id= 40的事务不在m_ids范围内，且比min_trx_id小，故可见
然后我们把trx_id = 50的事务提交一下
| 时间线 | 操作 |
|---|---|
| 1 | BEGIN，举例分配的 trx_id = 50 |
| 2 | update table_atm set age = 21 where id = 1 |
| 3 | update table_atm set age = 22 where id = 1 |
| 4 | update table_atm set age = 23 where id = 1 |
| 5 | commit |
| 时间线 | 操作 |
|---|---|
| 1 | BEGIN |
| 2 | select * from table_atm where id = 1 |
| 3 | select * from table_atm where id = 1 我们下面称之为 select2 语句 |
- 再执行
select2语句的时候不会生成新的视图，此时活跃的mids为[50,55],min_trx_id= 50,max_trx_id= 56,creator_trx_id= 0 - 从版本链里找到可见的数据，如图所示
trx_id= 50 的 事务是当前视图中活跃的事务 故不可见 trx_id= 40的事务不在m_ids范围内，且比min_trx_id小，故可见
总结:READ REPEATABLE 是在事务开始第一次查询时新建视图
3 锁
3.1 数据库锁的原理
mysql 中的锁其实就是内存中的一块信息。锁结构里有很多信息，比较有用的就是trx_id 和is_waiting 2个字段
trx_id这个锁是由哪个事务生成的is_waiting当前事务是否在等待。
- 一个事务对记录进行加锁的简易图
- 两个事务对记录加锁的示意图
如图所示如果
trx_id=11的事务占有锁，开始执行，trx_id=21的事务过来也想进行更改，首先先去内存判断该记录行是否有锁，如果有则进行排队，同时将is_waiting为true，表示为等待锁释放。 当trx_id=11的事务提交后，才会通知后续排队的事务进行操作
3.2 锁的分类
Mysql 中的锁有2大类
- 共享锁。也叫
S锁，事务读取一条记录的时候需要先获取到S锁 - 独占锁。也叫
X锁， 事务要改动一条记录的需要获取X锁
锁的兼容性 如下表示
| 兼容性 | S | X |
|---|---|---|
| S | Y | N |
| X | N | N |
S锁可以和S锁兼容。也只有这种兼容方式S锁与X锁不兼容X锁与S锁不兼容
3.2.1 select 语句产生 的锁
- 加S锁
SELECT ... LOCK IN SHARE MODE语句会对记录加上S锁，允许别的事务继续获取S锁，但是不能获取X锁。如果其他事务想获取X锁，需要等待S锁释放掉 - 加
X锁SELECT ... FOR UPDATE语句会对记录加X锁。这样其他事务既不能获取S锁，也不能获取X锁。其他事务想要获取锁，必须等该锁释放掉
3.2.2 update产生的锁
笼统来讲update 会产生X锁
3.2.3insert锁
insert 语句会产生隐式锁，保证插入的语句在提交事务前不会被其他事物读取到
3.2.4 next-key锁
3.2.5 gap 间隙锁
间隙锁锁的就是两个值之间的空隙
3.2.6 行锁
行锁就是针对数据表中记录行的锁，比如，事务A更新了某一行数据，同时事务B也要更新同一行，那么事务B一定要等待事务A执行完毕后才能进行操作
3.2.7 2阶段锁
沿用上面的一个例子
| 时间线 | 线程A | 线程B |
|---|---|---|
| 1 | begin transaction | |
| 2 | update table_atm set age = 25 where id = 1 | begin transaction |
| 3 | update table_atm set age = 22 where id = 1 | |
| 4 | ||
| 5 | commmit | |
| 6 | commmit |
在 InnoDB 事务中，行锁是在需要的时候才加上的，要等到事务结束时才释放。这个就是两阶段锁协议 同时我们要知道，如果在事务中需要锁住多行记录，需要把影响并发的操作放到最后，尽可能晚点对其加锁
3.2.8 死锁
当并发系统中不同的线程出现资源互相依赖时，涉及到的线程都在互相等待对方释放锁，导致几个线程进入死循环
| 时间线 | 线程A | 线程B |
|---|---|---|
| 1 | begin transaction | |
| 2 | update table_atm set age = 25 where id = 1; | begin transaction |
| 3 | update table_atm set age = 25 where id = 2 | |
| 4 | update table_atm set age = 25 where id = 2; | |
| 5 | update table_atm set age = 22 where id = 1 | |
| 6 | commmit | |
| 7 | commmit |
3.2.9 next-key lock
我们把行锁和间隙锁合成next-key lock, 它是一个前开后闭的取件
3.3 如何判断锁住哪些数据
先总结一下加锁原则
- 原则1：加锁的基本单位就是
next-key lock,next-key lock是前开后闭区间 - 原则2：查找过程中访问到的对象才加锁
- 优化1：索引上的等值查询，给唯一索引加锁(包括主键索引)的时候，
next-key lock退化为行锁 - 优化2：索引上的等值查询，向右遍历时最后一个值不满足等值条件的时候，
next-key lock退化为间隙锁 - 一个bug:唯一索引上的范围查询会访问到不满足条件的第一个值为止
下面我们就举例来说明每个原则
开讲之前首先我们先准备一个表
table_atm，同时对age加一个普通索引
| id | name | age |
|---|---|---|
| 5 | 迪迦奥特曼 | 5 |
| 10 | 泰罗奥特曼 | 10 |
| 15 | 赛文奥特曼 | 15 |
| 20 | 艾斯奥特曼 | 20 |
3.3.1 等值查询
| 线程A | 线程B | 线程C |
|---|---|---|
begin transaction | ||
update table_atm set age = 25 where id = 7 | ||
insert into table_atm values(8, '杰克奥特曼', 30) | ||
update table_atm set age = 22 where id = 13 |
- 根据原则1：线程A 要查询
id=7的数据然后进行更新，所以它会锁住(5,10] - 根据优化2：等值查询，向右遍历到最后一个不满足条件时，退化为
gap锁，所以 线程A的加锁区间是(5,10)
结论就是
- 线程A加锁区间是(5,10)
- 线程B因为A的加锁区间，导致线程B必须等待A释放锁才能进行插入
- 线程C是更新
id=10,不在被锁区间内，可以正常执行
3.3.2 非唯一索引等值锁
| 线程A | 线程B | 线程C |
|---|---|---|
begin transaction | ||
select id from table_atm where age = 10 | ||
update table_atm set name = 雷欧奥特曼 where = 10 | ||
insert into table_atm values(8, '杰克奥特曼', 30) |
如上图
- 根据原则1：线程A锁住的区间是
(5,10] age是普通索引，查找到age=10这一行后，还会继续向下查找第一条不满足条件的记录行，所以继续加锁(10,15]- 根据优化2：等查询
next-key锁 退化为gap锁，所以线程A 最终锁足的是(5,15) - 根据原则2：只有访问到对象才加锁，线程A使用普通索引即完成了查询条件，并没有用到主键索引，所以针对
id=10的主键索引上并么有被加锁， 结论
线程A锁住(5,15),并且对age=10加了读锁- 线程A 因为只查询主键ID，并没有主键索引加锁，所以线程B可以继续执行
tips
- 共享锁只会锁住普通索引， S锁会同时把 主键索引上满足条件的行记录都加锁
- 锁是加在索引上的，
3.3.3 主键索引的范围查询
| 线程A | 线程B | 线程C |
|---|---|---|
begin transaction | ||
select id from table_atm where id >= 10 and id < 11 | ||
insert into table_atm values(8, '杰克奥特曼', 30) | ||
insert into table_atm values(13, '奥特曼', 35) | ||
update table_atm set name = 雷欧奥特曼 where = 15 |
分析
- 线程A开始执行，首先
id>=10,按照原则1会锁住 区间(5,10],根据优化1，等值查询会退化成行锁，所以最终锁住的是id=10 - 线程A开始执行
id<11, 按照原则1 锁住区间(10,15]
结论
- 最终线程A 锁住的区间是
[10,15] - 线程B第一条sql可以插入成功，第二条插入需要等待锁释放
- 线程C的更新也要等待锁释放
3.3.4 非唯一索引范围查询
| 线程A | 线程B | 线程C |
|---|---|---|
begin transaction | ||
select * from table_atm where age >= 10 and age < 11 | ||
insert into table_atm values(8, '杰克奥特曼', 30) | ||
update table_atm set name = 雷欧奥特曼 where = 15 |
分析
- 查询时使用普通索引，根据原则1
age>=10会锁住(5,10],age<11会锁住(10,15)结论
-
线程A 锁住
(5,15] -
线程B和线程C都会block住，需要等待锁释放
