Mysql事务隔离级别与MVCC(多版本并发控制)

事务隔离级别

1、读未提交(Read Uncommitted) 能读到其他事务未提交的数据
2、读已提交(Read Committed) 能读到其他数据已提交的数据
3、可重复读(Repeatable Read) 不管其他事务修改完提交的数据，只要是在本事务内，多次读取，都是一样的结果，但若是本事务自己修改了，那是会根据数据库最新的数据去修改的，再查询时，也是最新的修改结果
4、串行化(Serializable) 每个事务按顺序执行

4种隔离级别存在的问题：
脏读：事务a读取事务b未提交的数据，事务b回滚了之后，事务a不知道。不符合一致性。
不可重复读：事务a内部多次读取时，事务b修改完提交了，事务a中途读取到了事务b提交的数据，前后不一致。不符合隔离性。
幻读：事务a按相同的条件去查询之前查过的数据，却发现事务b插入的新数据。不符合隔离性。

事务执行用例

可以通过以下sql 设置某次会话的事务隔离级别

读未提交
set session TRANSACTION ISOLATION LEVEL  READ UNCOMMITTED;
读已提交
set session TRANSACTION ISOLATION LEVEL  READ COMMITTED;
可重复读（数据库默认级别）
set session TRANSACTION ISOLATION LEVEL REPEATABLE READ;
1
2
3
4
5
6

这里主要研究的是可重复读(Repeatable Read)

新建一个account表数据

行锁、表锁、间隙锁、死锁的验证

模拟两个会话
（1）验证行锁： 一个session开启事务更新不提交，另一个session更新同一条记录会阻塞，更新不同记录不会阻塞

这里where 的条件是 id,是主键索引，默认加行锁

（2）验证无索引行锁升级为表锁：

锁一般是加在索引字段上的，不是加在一整条数据上的，
这里where 条件是name，它是没有建立name的索引，本来我们以为是加行锁，但是它会升级为表锁，事务a执行下面sql
begin;
update account set balance =  balance-10 where name='hzh-2' ;
那么会锁住一整个表，事务b执行任何一条写事务都会被阻塞，除非给name加上索引。
1
2
3
4
5

（3）验证间隙锁：

事务a执行
begin;
select * from account;
update account set balance =  balance-10 where id>=1 and id<=10;
底层会给该id范围加锁，在本事务commit前，当其他事务修改，删除，插入id为1-10时，阻塞
1
2
3
4
5

事务b在执行修改，删除，插入时，id=1到10 会被阻塞
begin;
insert into account values(5,'hzh-5',5000);
1
2
3

注意：若表中数据是id=1,2,3,4,10,20这6条数据; 条件是where id>=7 and id<=17;
因为7在4-10之间，所以涉及到的4-10的整个区间都是会被锁到，包括4，同理10-20也会被锁。并不是只有7-17会被锁，而是4-20。

（4）验证死锁：

1、事务a
begin;
update account set balance =  balance-10 where id=1 ;
2、事务b
begin;
update account set balance =  balance-10 where id=2 ;
3、事务a
update account set balance =  balance-10 where id=2 ;
4、事务b
update account set balance =  balance-10 where id=1 ;

死锁报错
> 1213 - Deadlock found when trying to get lock; try restarting transaction
> 自动回滚其中一个事务，另一个事务正常继续执行。
1
2
3
4
5
6
7
8
9
10
11
12
13
14

可重复读例1：

a 事务先查询和修改，b事务再查询，a事务再commit, b事务再查询。
b事务查询的数据全程不受a事务影响

步骤1、a 事务

begin;
select * from account;
update account set balance =  balance-10 where id=1;
select * from account;
此时还没有commit
1
2
3
4
5

a事务查询到自己修改的 第一行数据的 balance变成990

步骤2、b事务

begin;
select * from account;
此时还没有commit
1
2
3

b事务查询到的第一行数据的 balance还是1000
因为是可重复读级别，a事务没有提交是查不到了，就算a事务提交也查不到。
因为这个隔离级别要保证b事务多次读取，结果都一样。

步骤3、现在我们去事务a commit; 再 select * from account;
发现数据确实是改好了。

步骤4、再去事务b执行看 select * from account; 还是1000；这个隔离级别保证了b事务多次读取，结果都一样。commit之后结束事务了再查询，就能查到了。

可重复读例2：

**a事务先查询后，b事务修改后提交，a事务再修改并查询再提交 **

步骤1、事务a先查询

begin;
select * from account;
1
2

步骤2、事务b 执行更新后提交，此时数据库真实数据已经是990

begin;
select * from account;
update account set balance =  balance-10 where id=1;
commit;
1
2
3
4

步骤3、事务a 再查询 select * from account; 依然是1000，符合可重复读 多次读取结果都一样。
但是若此时执行
update account set balance = balance-10 where id=1;
select * from account;
会怎么样呢？
它会去根据数据库的真实数据 990-10=980 来作为真实结果。

只有增删改时会根据数据库的真实数据去操作，查询是不会的。

这样的效果是大部分开发中所需要的，读取数据时不受其他事务影响，增删改时需要根据最新数据修改，mysql的底层是怎么做到这样的呢？ 就是 MVCC(多版本并发控制)做到的

MVCC(Multi-Version Concurrency Control 多版本并发控制)

数据库表隐藏的2个相关字段 DB_TRX_ID（创建事务id）, DB_ROLL_PTR（回滚指针）,执行begin时不会申请事务id，begin之后的第一条增删改查sql才会触发去申请事务id,事务id根据申请的前后顺序严格递增。

我们以 account表的第一条数据为例来分析，新建4个事务，假设按照图中步骤执行各sql

undo log和版本链的动态变化

1、插入，以下情况是事务10插入前3条数据，事务11插入id=4的数据，两个事务都commit结束之后，数据库account表实际的存储样子

id  name   balance  创建事务id     删除事务id
1   hzh-1   1000       10            null
2   hzh-2   2000       10            null
3   hzh-3   3000       10            null
4   hzh-4   4000       11            null
1
2
3
4
5

2、修改，若新事务12 执行 update account set name=hzh-666 where id=1,第5行新增的数据在 undo log 位置，这里拿过来放表的方便对比

id  name     balance  创建事务id     删除事务id
1   hzh-1     1000       10            12(原数据标记被事务12删除)
2   hzh-2     2000       10            null
3   hzh-3     3000       10            null
4   hzh-4     4000       11            null
1   hzh-666   1000       12            null(事务12新增1条数据)
1
2
3
4
5
6

3、删除，若新事务13 执行 delete from account where id=2,account表实际的存储样子

id  name     balance  创建事务id     删除事务id
1   hzh-1     1000       10            12
2   hzh-2     2000       10            13
3   hzh-3     3000       10            null
4   hzh-4     4000       11            null
1   hzh-666   1000       12            null
1
2
3
4
5
6

4、查询，若事务12 继续 执行 select * from account ，其实底层会给我们拼接上过滤条件字段 ：
select * from account where DB_TRX_ID<=12 and DB_ROLL_PTR>12
DB_TRX_ID<=12（创建事务id<=12）：在事务12之前提交的事务都是可靠的数据，需要查出来，即事务12以后创建的数据对事务12来说是不可靠的；
DB_ROLL_PTR>12（删除事务id>12）：在事务12之后删除的数据，我事务12都不认帐，认为还没被删掉，也需要查到。

查到的数据应如下：

id  name     balance  创建事务id     删除事务id
2   hzh-2     2000       10            13
3   hzh-3     3000       10            null
4   hzh-4     4000       11            null
1   hzh-666   1000       12            null
1
2
3
4
5