前言

Github：https://github.com/HealerJean

博客：http://blog.healerjean.com

1、事务并发的问题

1.1、脏读：

脏读： 正在执行的事务 读取到其他事物未提交的数据

脏读的后果：如果后一个事务回滚，那么它所做的修改，统统都会被撤销。前一个事务读到的数据，就是垃圾数据

解决方法：把事务隔离级别调整到READ COMMITTED/REPEATABLE READ 、 读取时加共享锁，修改时加排他锁

1.2、不可重复读：

不可重复读：正在执行的事务 读取到了其他事务的修改

事务A，可以看到其他事务所做的修改。事务 A 多次读取同一数据，事务 B 在事务A多次读取的过程中，对数据作了更新并提交，导致事务A多次读取同一数据时，结果 不一致。

解决方法：把事务隔离级别调整到REPEATABLE READ、读取时加共享锁 写数据时加排他锁

1.3、幻读：

幻读：正在执行的事务 读取倒了其他事务已提交的插入

事务1读取指定的where子句所返回的一些行。然后，事务2插入一个新行，这个新行也满足事务1使用的查询where子句。然后事务1再次使用相同的查询读取行，但是现在它看到了事务2刚插入的行。这个行被称为幻象，因为对事务1来说，这一行的出现是不可思议的。

解決方法：当前读的间隙锁/MVCC快照读，这样别人就不能插入了

1.3.1、快照读和当前读

在可重复读隔离级别下，普通查询是快照读，是不会看到别的事务插入的数据的，幻读只在当前读下才会出现。 如果事务中都使用快照读或者都使用当前读，那么就不会产生幻读现象，但是快照读和当前读混用就会产生幻读

快照读：不会加锁， 快照读就是读取数据的时候会根据一定规则读取事务可见版本的数据（MMVC），也就是说多次查询，查询到的一样的

select * from .... where ... 

当前读 ：会在搜索的时候加锁，以下都是当前读

select * from .... where ... for update 
select * from .... where ... lock in share mode
update .... set .. where ... 
delete from. . where ..

1.3.2、间隙锁解决幻读

幻读会在 RU / RC / RR 级别下出现，SERIALIZABLE 则杜绝了幻读，但 RU存在脏读 ，RC 不可重复读，故我们就以 RR 级别来研究幻读，排除其他干扰。

RR 级别下存在幻读的可能，SERIALIZABLE 正是对所有事务都加 X锁 才杜绝了幻读，但很多场景下我们的业务sql并不会存在幻读的风险。SERIALIZABLE 的一刀切虽然事务绝对安全，但性能会有很多不必要的损失。

但也是可以使用对记录手动加 排它锁（X锁） 的方法消除幻读。故可以在 RR 下根据业务需求决定是否加锁，存在幻读风险我们加锁，不存在就不加锁，事务安全与性能兼备，这也是 RR 作为mysql默认隔是个事务离级别的原因，所以需要正确的理解幻读。

下面举一个形象的例子：

step1 T1: SELECT * FROM `users` WHERE `id` = 1;
step2 T2: INSERT INTO `users` VALUES (1, 'big cat');
step3 T1: INSERT INTO `users` VALUES (1, 'big cat');
step4 T1: SELECT * FROM `users` WHERE `id` = 1;

T1 ：主事务，检测表中是否有 id 为 1 的记录，没有则插入，这是我们期望的正常业务逻辑。   

T2 ：干扰事务，目的在于扰乱 T1 的正常的事务执行。    

在 RR 隔离级别下，step1、step2 是会正常执行的，step3 则会报错主键冲突，对于 T1 的业务来说是执行失败的，这里 T1 就是发生了幻读，

因为 T1 在 step1 中读取的数据状态并不能支撑后续的业务操作，T1：“见鬼了，我刚才读到的结果应该可以支持我这样操作才对啊，为什么现在不可以”。T1 不敢相信的又执行了 step4，发现和 setp1 读取的结果是一样的（RR下的 MMVC机制）。此时，幻读无疑已经发生，T1 无论读取多少次（此时是快照读），都查不到 id = 1 的记录，但它的确无法插入这条他通过读取来认定不存在的记录（此数据已被T2插入），对于 T1 来说，它幻读了。

解决：其实 RR 也是可以避免幻读的，通过对 select 操作手动加 行X锁（SELECT … FOR UPDATE 这也正是 SERIALIZABLE 隔离级别下会隐式为你做的事情），同时还需要知道，即便当前记录不存在，比如 id = 1 是不存在的，当前事务也会获得一把锁（因为InnoDB的行锁锁定的是索引，故记录实体存在与否没关系，存在就加 行X锁，不存在就加 next-key lock间隙X锁），其他事务则无法插入此索引的记录，故杜绝了幻读。

2、事物的隔离级别

2.1、读未提交

解释： 正在执行的事务 读取到其他事物未提交的数据

并发问题：

1、脏读：正在执行的事务 读取到其他事物未提交的数据

2、不可重读：读取到其他事务已提交的修改

3、幻读：读取到其他事务的插入

2.2、读已提交

解释：正在执行的事务 读取到其他事务已提交的修改 、同时也能读取到其他事务已提交的插入 、

并发问题：

1、不可重读， 读取到其他事务已提交的修改

2、幻读：读取到其他事务的插入

2.3、可重复读

解释： 正在执行的事务 读取不到其他事务已提交或未提交的修改，同时也能读取到其他事务已提交的插入

并发问题：

1、幻读：读取到其他事务的插入

解决并发问题：

2.4、串行化

解释：最高的默认级别，强制事务串行执行（即一个事务一个事务执行）

无并发问题：

3、锁的分类

3.1、功能上分类

3.1..1、共享锁和排它锁

共享锁和排它锁 都可以直接通过select …from…查询数据，因为普通查询没有任何锁机制

排它锁与共享锁相对应，就是指对于多个不同的事务，对同一个资源只能有一把锁。与共享锁类似，在需要执行的语句后面加上for update就可以了

共享锁：

共享锁又称为读锁，简称S锁，顾名思义，共享锁就是多个事务对于同一数据可以共享一把锁，都能访问到数据，但是只能读不能修改，包括自己。

SELECT * from city where id = "1"  lock in share mode;
然后在另一个查询窗口中，对id为1的数据进行更新


update  city set name="666" where id ="1";
此时，操作界面进入了卡顿状态，过几秒后，也提示错误信息
[SQL]update  city set name="666" where id ="1";
[Err] 1205 - Lock wait timeout exceeded; try restarting transaction
那么证明，对于id=1的记录加锁成功了，在上一条记录还没有commit之前，这条id=1的记录被锁住了，只有在上一个事务释放掉锁后才能进行操作，或用共享锁才能对此数据进行操作。


本窗口中

update city set name="666" where id ="1" lock in share mode;
[Err] 1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'lock in share mode' at line 1
加上共享锁后，也提示错误信息了，通过查询资料才知道，对于update,insert,delete语句会自动加排它锁的原因
于是，我又试了试SELECT * from city where id = "1" lock in share mode;这下成功了。

排它锁：

排他锁又称为写锁，简称X锁，顾名思义，排他锁就是不能与其他所并存，如一个事务获取了一个数据行的排他锁，其他事务就不能再获取该行的其他锁，包括共享锁和排他锁，但是获取排他锁的事务是可以对数据就行读取和修改。

对于**update

delete

insert**语句，innodb会自动的给涉及到的数据集加排他锁(从功能上讲是排他锁，从范围上，可能是记录锁、间隙锁、临建锁、插入意向锁的任何一种) （这三个锁住的是整个表，看情况查询的是不是主键，如果是主键和索引，则是行锁，否则是整张表）

// session1
mysql> begin;
mysql> select * from lock_test where id = 4 for update;
+----+--------+------+--------+
| id | name   | age  | number |
+----+--------+------+--------+
|  4 | 琴女   |   40 |    400 |
+----+--------+------+--------+
1 row in set (0.00 sec)

// session2测试
mysql> begin;
mysql> select * from lock_test where id = 4 for update;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

3.1.2、乐观锁和悲观锁

3.1.2.1、悲观锁

悲观锁（Pessimistic Lock），顾名思义，就是很悲观，假定会发生并发冲突，屏蔽一切可能违反数据完整性的操作。每次去拿数据的时候都认为别人会修改，所以每次在拿数据的时候都会上锁，这样别人想拿这个数据就会block直到它拿到锁。

Java synchronized 就属于悲观锁的一种实现，每次线程要修改数据时都先获得锁，保证同一时刻只有一个线程能操作数据，其他线程则会被block。

缺点：因为悲观锁大多数情况下依靠数据库的锁机制实现，以保证操作最大程度的独占性。如果加锁的时间过长，其他用户长时间无法访问，影响了程序的并发访问性，同时这样对数据库性能开销影响也很大，特别是对长事务而言，这样的开销往往无法承受。所以与悲观锁相对的，我们有了乐观锁。

3.1.2.2、乐观锁

乐观锁（Optimistic Lock），顾名思义，就是很乐观，每次去拿数据的时候都认为别人不会修改，所以不会上锁，但是在提交更新的时候会判断一下在此期间别人有没有去更新这个数据。

Java JUC中的atomic包就是乐观锁的一种实现，AtomicInteger 通过CAS（Compare And Set）操作实现线程安全的自增。

1、使用数据版本（Version）记录机制实现

这是乐观锁最常用的一种实现方式。何谓数据版本？即为数据增加一个版本标识，一般是通过为数据库表增加一个数字类型的 “version” 字段来实现。当读取数据时，将version字段的值一同读出，数据每更新一次，对此version值加一。当我们提交更新的时候，判断数据库表对应记录的当前版本信息与第一次取出来的version值进行比对，如果数据库表当前版本号与第一次取出来的version值相等，则予以更新，否则认为是过期数据。

2. 使用时间戳（timestamp）。

乐观锁定的第二种实现方式和第一种差不多，同样是在需要乐观锁控制的table中增加一个字段，名称无所谓，字段类型使用时间戳（timestamp）, 和上面的version类似，也是在更新提交的时候检查当前数据库中数据的时间戳和自己更新前取到的时间戳进行对比，如果一致则OK，否则就是版本冲突。

4、行级锁

InnoDB的行锁，就是通过锁住索引来实现的，主键也是索引哦。

记录锁(Record Locks)：主键等值更新或删除的时候，锁定一行记录

间隙锁(Gap Locks)：索引范围更新的时候，锁定一个开区间的范围，前提是数据库是RR（可重复读）级别，如果是RC（读已提交）级别，就没有间隙锁了

临键锁(Next-Key Locks)：记录锁 + 间隙锁，一个左开右闭或者左闭右开的区间

插入意向锁(Insert Intention Locks)：是一种特殊的间隙锁，也是锁定一个区间

4.1、锁的相关问题

问题1：表里面数据的时候，下面的sql语句也能导致锁表，或者说，如果锁住的是索引，一张表没有索引怎么办？所以，一张表有没有可能没有索引？

事务一：
SELECT * FROM t1 where id=1 FOR UPDATE;	


事务二：
SELECT * FROM t3 where name=‘4’ FOR UPDATE; // BLOCKED
SELECT * FROM t3 where id=4 FOR UPDATE; // BLOCKED

答：

无论表中有没有数据，索引是肯定创建的

1、如果我们定义了主键(PRIMARYKEY)，那么 InnoDB 会选择主键作为聚集索引   

2、如果没有显式定义主键，则 InnoDB 会选择第一个不包含有 NULL 值的唯一索引作为主键索    

3、如果也没有这样的唯一索引，则 InnoDB 会选择内置 6 字节长的 ROWID 作为隐藏的聚集索引，它会随着行记录的写入而主键递增   

所以，因为查询没有使用索引，会进行全表扫描，然后把每一个隐藏的聚集索引都锁住了，这里是用到了间隙锁。

问题2：这里唯一索引比较好理解，但是不唯一索引确定了数据集之后，为什么还要额外的给主键索引加记录锁呢？

答：其实不光不唯一索引，非主键的唯一索引确定了数据集，也要给命中记录的主键索引加记录锁的，在普通索引和唯一索引里面， 索引存储的是索引和主键的值。 比如name=4，存储的是name的索引和主键id的值4。而主键索引里面除了索引之外，还存储了完整的数据。所以我们通过辅助索引锁定一行数据的时候，它跟我们检索数据的步骤是一样的，会通过主键值找到主键索引，然后也锁定。

问题3：为什么要”多此一举，给主键锁定呢”,

答：因为只有给主键索引加了记录锁，才能彻底锁住这条记录，可以理解为我们对一条数据做更改操作的时候，最后都是要通过主键索引去判断。

4.1、分类

4.1.1、Record Lock记录锁

对应死锁：lock_mode X locks rec but not gap

锁定一条记录，范围就是一条记录，不允许其他事务对这个记录进行加锁操作，记录锁就是锁update或者delete操作的（我下面的举例其实很多就是临减锁，只是说为了方便理解，下面写成了记录锁+间隙锁）

记录锁针对update或者delete

4.1.2、Gap Lock 间隙锁

对应死锁：lock_mode X locks gap before rec

前提是数据库是RR（可重复读）级别，如果是RC（读已提交）级别，就没有间隙锁了

锁定一个范围，范围是一个开区间，不包括首尾两个节点，不允许在这个间隙进行插入操作，因为间隙没有记录存在，所以也就不存在对记录进行更新或者操作的锁了，当然使用update或者delete肯定也不行，这个玩意也是排它锁，因为只能上一把锁。 简单理解就是间隙锁就是防止insert操作的，间隙锁防止其他事物做插入操作

间隙锁针对insert，且左右区间必须是存在的节点

4.1.3、Next-Key Lock 临键锁

对应死锁： lock_mode X

临键锁即 next-key lock，是行锁和它之前的间隙共同构成的锁，即一个前开后闭的加锁区间。从原理上来说，它就是一个行锁叠加了一个间隙锁，它是RR模式下基本的加锁粒度。

⬤ 原则1：RR 模式加锁的基本单位是next-key lock，即前开后闭的区间

⬤ 原则2：查找过程中访问到的对象才会加锁

⬤ 优化1：唯一索引上的等值查询，next-key lock 会退化为行锁

⬤ 优化2：索引上的等值查询，向右遍历的时候，最后一个值不满足等值条件的时候会退化为间隙锁。

⬤ 一个bug:唯一索引上的范围查询，会访问到不满足条件的第一个值为止。

4.1.3.1、等值查询—唯一索引

步骤	事务A	事务B	事务B
1	begin; update u set d= d+ 1 where id = 7;	-	-
2	-	begin; insert into u (8,8,8);	-
3	-	-	update set d = d+ 1 where id = 10

1、加锁的范围是（5,10]的范围锁

2、由于数据是等值查询，并且表中最后数据id = 10 不满足id= 7的查询要求，故id=10 的行级锁退化为间隙锁，（5,10）

3、所以事务B中id=8会被锁住，而id=10的时候不会被锁住

4.1.3.2、等值查询—普通索引

步骤	事务A	事务B	事务B
1	begin; select id form t where c = 5 lock in share mode;	-	-
2	-	update t set d = d + 1 where id = 5	-
3	-	-	insert into values (7,7,7)

1、加锁的范围是（0,5]，（5,10]的范围锁

2、由于c是普通索引，根据原则4，搜索到5后继续向后遍历直到搜索到10才放弃，故加锁范围为（5,10]

3、由于查询是等值查询，并且最后一个值不满足查询要求，故间隙锁退化为（5,10）

1. 因为加锁是对普通索引c加锁，而且因为索引覆盖，没有对主键进行加锁，所以事务B执行正常
2. 因为加锁范围（5,10）故事务C执行阻塞
3. 需要注意的是，lock in share mode 因为覆盖索引故没有锁主键索引，如果使用for update 程序会觉得之后会执行更新操作故会将主键索引一同锁住

4.1.3.3、范围查询—唯一索引

步骤	事务A	事务B	事务B
1	begin; select * form t where id >= 10 and id <11 for update;	-	-
2	-	insert into values(8,8,8)	-
3	-	-	update t set d = d+ 1 where id = 15

1、next-key lock 增加范围锁（5,10]

2、根据原则5，唯一索引的范围查询会到第一个不符合的值位置，故增加（10，15]

3、因为等值查询有id =10 根据原则3间隙锁升级为行锁，故剩余锁[10,15]

4、因为查询并不是等值查询，故[10,15]不会退化成[10,15)

5、故事务B（13,13,13）阻塞，事务C阻塞

4.1.3.4、范围查询—普通索引

步骤	事务A	事务B	事务B
1	begin; select * form t where c >= 10 and c <11 for update;	-	-
2	-	insert into values(8,8,8)	-
3	-	-	update t set d = d+ 1 where c = 15

1、next-key lock 增加范围锁（5,10]，（10，15]

2、因为c是非唯一索引，故（5,10]不会退化为10

3、因为查询并不是等值查询，故[10,15]不会退化成[10,15)

4、所以事务B和事务C全部堵塞

4.1.4、自增锁 （表级锁）

自增锁是一个特殊的表级别的锁。当执行插入时，主键是AUTO_INCREMENT的列的时候，会触发自增锁。

它的原理是，它是表级别的锁，意味着，当一个事务执行插入的时候，其它的事务可能需要等待，以便获取唯一主键值，虽然等待的时间是极短的。

自增锁，为了考虑效率和一致性，有3种模式，包括：传统（默认）、连续、插入。对比如下：

4.1.4、Insert Intention Locks插入意向锁

对应死锁：lock_mode X locks gap before rec insert intention

特殊的间隙锁，锁定一个范围，范围是一个开区间，允许在这个区间内进行不重复(主键、唯一索引不重复)的插入操作，

即插入意向锁是有可以共存的，只要唯一索引不重复即可。但是不与其他锁（一般也就是间隙锁）共存，好好理解这句话，别到时候再看又忘记了。并发插入就是使用意向锁

在MySQL，InnoDB，RR下：

t(id unique PK, name);

数据表中有数据：

10, shenjian

20, zhangsan

30, lisi

事务A先执行，在10与20两条记录中插入了一行，还未提交：

insert into t values(11, xxx);

事务B后执行，也在10与20两条记录中插入了一行：

insert into t values(12, ooo);

(1)会使用什么锁？(2)事务B会不会被阻塞呢？

回答：虽然事务隔离级别是RR，虽然是同一个索引，虽然是同一个区间，但插入的记录并不冲突，故这里：使用的是插入意向锁，并不会阻塞事务B

4.1.4、意向锁 （表锁）

又分为意向共享锁 IS 和:意向排它锁 IX， 表明一个事务想对数据库的某些行加共享/排他锁；它的原理是是记录在表级别，记录这个锁，而不是在索引上。

比如, SELECT ... LOCK IN SHARE MODE 会加一个IS锁, SELECT ... FOR UPDATE会加一个 IX 锁.

即IS 和 IX 锁，是当一个事务想在表中的某些行加 S锁和X锁的时候，它会在表上登记一个IS 和 IX 锁。那这个意向锁到底有什么作用呢？

问题1：为什么意向锁被设计出来

答案：即当一个事务想申请表级别的共享或者排他锁时，会检查这个表上已经有的意向锁，来快速知道能否加锁成功。意向锁和表级锁的兼容关系如下（可以看出意向锁之间是彼此兼容的，它只和表级锁之间有兼容关系。所以，意向锁引入的主要目的也是为了提高获取表锁时的效率。）：

4.2、行锁加锁方式

对于**update

delete

insert**语句，innodb会自动的给涉及到的数据集加排他锁(从功能上讲是排他锁，从范围上，可能是记录锁、间隙锁、临建锁、插入意向锁的任何一种)

1、只命中了记录，一定有记录锁，可能会有间隙锁 只要命中了记录一定会给主键索引上记录锁，为了定位这条数据(因为数据是在主键索引上的)

2、只命中了间隙(也就是没有命中任何记录)一定有间隙锁，没有记录锁

3、都命中的话(说明是范围查询)，那么就是记录锁+间隙锁

对于select语句，不会加任何锁。 但是我们可以手动给select语句加锁(这种悲观锁等效于把select语句变成一个update语句，但不等效insert)

// 加共享锁
select * from lock_test where... lock in share mode;
// 加排他锁
select * from lock_test where... for update;

mysql> select * from lock_test;
+----+--------------+------+--------+
| id | name         | age  | number |
+----+--------------+------+--------+
|  1 | 托儿索       |    6 |    100 |
|  2 | 瞎子         |   10 |    200 |
|  3 | 大头         |  100 |    300 |
|  4 | 琴女         |   40 |    400 |
|  7 | 皇子         |   30 |    700 |
|  9 | 机器人       |   40 |    900 |
| 11 | 伊泽瑞尔     |   20 |   1100 |
+----+--------------+------+--------+

a.只给id=4的索引项加了记录锁，此时已经可以准确定位到琴女这一行记录了

// session1
mysql> begin;
mysql> select * from lock_test where id = 4 for update;
+----+--------+------+--------+
| id | name   | age  | number |
+----+--------+------+--------+
|  4 | 琴女   |   40 |    400 |
+----+--------+------+--------+
1 row in set (0.00 sec)

// session2测试
mysql> begin;
mysql> select * from lock_test where id = 4 for update;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

b.未命中记录行，向上向下分别寻找节点，得到id(4,7)的锁定范围，4-7不能插

// session1
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from lock_test where id = 5 for update;
Empty set (0.00 sec)

// session2测试
mysql> begin;
Query OK, 0 rows affected (0.01 sec)

mysql> select * from lock_test where id =4 for update;
+----+--------+------+--------+
| id | name   | age  | number |
+----+--------+------+--------+
|  4 | 琴女   |   40 |    400 |
+----+--------+------+--------+
1 row in set (0.00 sec)

mysql> select * from lock_test where id =7 for update;
+----+--------+------+--------+
| id | name   | age  | number |
+----+--------+------+--------+
|  7 | 皇子   |   30 |    700 |
+----+--------+------+--------+
1 row in set (0.01 sec)

mysql> select * from lock_test where id =6 for update;
Empty set (0.00 sec)

mysql> insert into lock_test values (6,'凯隐',31,'600');
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

c.命中记录行主键&唯一索引->记录锁+where条件间隙锁,得到id(4,7],[7,9],[9,11],[11,+oo),大于6的条件命中了7，9，11，4是向左边找到的一个开区间节点。

// session1
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from lock_test where id > 6 for update;
+----+--------------+------+--------+
| id | name         | age  | number |
+----+--------------+------+--------+
|  7 | 皇子         |   30 |    700 |
|  9 | 机器人       |   40 |    900 |
| 11 | 伊泽瑞尔     |   20 |   1100 |
+----+--------------+------+--------+
3 rows in set (0.00 sec)

// session2测试
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from lock_test where id = 7 for update; --记录锁的原因不能修改
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> insert into lock_test values (12,'凯隐',31,'600'); --间隙锁不能插入
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> insert into lock_test values (5,'凯隐',31,'600');--间隙锁不能插入
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

d.id大于11未命中任何记录，所以有了一个(11,+oo)的间隙锁。如果这里是id大于12，间隙锁范围也是(11,+oo)

// session1
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from lock_test where id > 11 for update;
Empty set (0.00 sec)

// session2
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into lock_test values (12,'凯隐',31,'600');
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> select * from lock_test where id = 11 for update;
+----+--------------+------+--------+
| id | name         | age  | number |
+----+--------------+------+--------+
| 11 | 伊泽瑞尔     |   20 |   1100 |
+----+--------------+------+--------+
1 row in set (0.00 sec)

如果是不唯一索引同理，只测试一下范围命中的情况，其他的感兴趣可以自己测试 。

age是一个normal索引，这里大于35,加锁情况分析如下

id索引项:3,4,9 记录锁

age索引项:40,100记录锁， (30,+oo)间隙锁

// session1
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from lock_test where age > 35 for update;
+----+-----------+------+--------+
| id | name      | age  | number |
+----+-----------+------+--------+
|  3 | 大头      |  100 |    300 |
|  4 | 琴女      |   40 |    400 |
|  9 | 机器人    |   40 |    900 |
+----+-----------+------+--------+
3 rows in set (6.56 sec)

// session2
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into lock_test values (13,'凯隐',29,201); --没有记录锁可以修改
Query OK, 1 row affected (0.00 sec)

mysql> insert into lock_test values (14,'凯隐',30,203); --间隙锁不能超如
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

4.3、聚集索引和非聚集索引

InnoDB聚集索引的叶子节点存储行记录， InnoDB必须有，且只有一个聚集索引：

1、如果表定义了PK，则PK就是聚集索引；

2、如果表没有定义PK，则第一个not NULL unique列是聚集索引；

3、否则，InnoDB会创建一个隐藏的row-id作为聚集索引；

InnoDB非聚集索引的叶子节点仍然是索引节点，只是有一个指针是指向主键值。分成普通索引，唯一索引。如果非要把非聚集索引类比成现实生活中的东西，那么非聚集索引就像新华字典的偏旁字典，他结构顺序与实际存放顺序不一定一致。

区别在于， 通过聚集索引可以查到需要查找的数据， 而通过非聚集索引可以查到记录对应的主键值 ， 再使用主键的值通过聚集索引查找到需要的数据。

4.3.1、回表

非聚集索引的二次查询问题： 非聚集索引叶节点仍然是索引节点，只是有一个指针指向对应的数据块

如果使用非聚集索引查询，而查询列中包含了其他该索引没有覆盖的列，那么他还要进行第二次的查询，查询节点上对应的数据行的数据。

其中有 聚集索引clustered index(id), 非聚集索引index(username)。

使用以下语句进行查询，不需要进行二次查询，直接就可以从非聚集索引的节点里面就可以获取到查询列的数据。

select id, username from t1 where username = '小明'
select username from t1 where username = '小明'

但是使用以下语句进行查询，就需要二次的查询去获取原数据行的score，其实也就是回表查询。

select username, score from t1 where username = '小明'

using index > using where > using index condition ，如果不需要回表查询数据，效率上应该比较快的

5、MVCC

就是多版本并发控制。MVCC 是一种并发控制的方法，一般在数据库管理系统中，实现对数据库的并发访问。

MVCC就是为了实现读-写冲突不加锁（读操作不阻塞写操作，写操作不阻塞读操作），而这个读指的就是快照读, 而非当前读，当前读实际上是一种加锁的操作，是悲观锁的实现，

MVCC只工作在REPEATABLE READ和READ COMMITED隔离级别下,

READ UNCOMMITED不是MVCC兼容的，因为查询不能找到适合他们事务版本的行版本；它们每次都只能读到最新的版本。

SERIABLABLE也不与MVCC兼容，因为读操作会锁定他们返回的每一行数据。

5.1、隐式字段

每行记录除了我们自定义的字段外，还有数据库隐式定义的DB_TRX_ID,DB_ROLL_PTR,DB_ROW_ID等字段

DB_ROW_ID：6byte，主键，如果数据表没有主键，InnoDB会自动以DB_ROW_ID产生一个聚簇索引

DB_TRX_ID：6byte，最近修改(修改/插入)事务ID：记录创建这条记录/最后一次修改该记录的事务ID

DB_ROLL_PTR：7byte，回滚指针，指向这条记录的上一个版本（存储于rollback segment里）

实际还有一个删除flag隐藏字段, 既记录被更新或删除并不代表真的删除，而是删除flag变了

5.2、undo日志(版本链)

insert undo log：代表事务在insert新记录时产生的undo log, 只在事务回滚时需要，并且在事务提交后可以被立即丢弃

update undo log：事务在进行update或delete时产生的undo log; 不仅在事务回滚时需要，在快照读时也需要；所以不能随便删除，只有在快速读或事务回滚不涉及该日志时，对应的日志才会被purge线程统一清除

对MVCC有帮助的实质是update undo log ，undo log实际上就是存在rollback segment中旧记录链，它的执行流程如下：

1、比如一个有个事务插入persion表插入了一条新记录，记录如下，name为Jerry, age为24岁，隐式主键是1，事务ID和回滚指针，我们假设为NULL

2、 现在来了一个事务1对该记录的name做出了修改，改为Tom

3、又来了个事务2修改person表的同一个记录，将age修改为30岁

从上面，我们就可以看出，不同事务或者相同事务的对同一记录的修改，会导致该记录的undo log成为一条记录版本线性表，既链表，undo log的链首就是最新的旧记录，链尾就是最早的旧记录（当然就像之前说的该undo log的节点可能是会purge线程清除掉，向图中的第一条insert undo log，其实在事务提交之后可能就被删除丢失了，不过这里为了演示，所以还放在这里）

5.3、ReadView（读视图)

什么是Read View，ReadView中主要就是有个列表来存储我们系统中当前活跃着的读写事务，也就是begin了还未提交的事务。通过这个列表来判断记录的某个版本是否对当前事务可见

Read View遵循一个可见性算法，主要是将要被修改的数据的最新记录中的DB_TRX_ID（即当前事务ID）取出来，与系统当前其他活跃事务的ID去对比（由Read View维护），如果DB_TRX_ID跟Read View的属性做了某些比较，不符合可见性，那就通过DB_ROLL_PTR回滚指针去取出Undo Log中的DB_TRX_ID再比较，即遍历链表的DB_TRX_ID（从链首到链尾，即从最近的一次修改查起），直到找到满足特定条件的DB_TRX_ID, 那么这个DB_TRX_ID所在的旧记录就是当前事务能看见的最新老版本

已提交读和可重复读的区别就在于它们生成ReadView的策略不同。

具体的一个可见性算法：

trx_list（名字我随便取的 ReadView列表） 一个数值列表，用来维护Read View生成时刻系统正活跃的事务ID

up_limit_id：记录trx_list列表中事务ID最小的ID

low_limit_id：ReadView生成时刻系统尚未分配的下一个事务ID，也就是目前已出现过的事务ID的最大值+1



1、首先比较DB_TRX_ID < up_limit_id, 如果小于，则当前事务能看到DB_TRX_ID 所在的记录
2、接下来判断 DB_TRX_ID 大于等于 low_limit_id , 如果大于等于则代表DB_TRX_ID 所在的记录在Read View生成后才出现的，那对当前事务肯定不可见，如果小于则进入下一个判断
3、判断DB_TRX_ID 是否在活跃事务之中，trx_list.contains(DB_TRX_ID)，如果在，则代表我Read View生成时刻，你这个事务还在活跃，还没有Commit，你修改的数据，我当前事务也是看不见的；如果不在，则说明，你这个事务在Read View生成之前就已经Commit了，你修改的结果，我当前事务是能看见的

假设当前列表里的事务id为[80,100]。

1、如果你要访问的记录版本的事务id为50，比当前列表最小的id80小，那说明这个事务在之前就提交了，所以对当前活动的事务来说是可访问的。

2、如果你要访问的记录版本的事务id为90,发现此事务在列表id最大值和最小值之间，那就再判断一下是否在列表内，如果在那就说明此事务还未提交，所以版本不能被访问。如果不在那说明事务已经提交，所以版本可以被访问。

3、如果你要访问的记录版本的事务id为110，那比事务列表最大id100都大，那说明这个版本是在ReadView生成之后才发生的，所以不能被访问。

这些记录都是去版本链里面找的，先找最近记录，如果最近这一条记录事务id不符合条件，不可见的话，再去找上一个版本再比较当前事务的id和这个版本事务id看能不能访问，以此类推直到返回可见的版本或者结束。

读已提交和可重复的区别 （我们这里测试的是更新，同时删除和更新是一样的，这里讲的是更新）

​ 举个例子 ，在已提交读隔离级别下：

比如此时有一个事务id为100的事务，修改了name,使得的name等于小明2，但是事务还没提交。则此时的版本链是

那此时另一个事务发起了select 语句要查询id为1的记录，去版本链去找了，首先肯定找最近的一条，发现trx_id是100,也就是name为小明2的那条记录，发现在列表内（此时生成的ReadView 列表只有[100]），所以不能访问。

这时候就通过指针继续找下一条，name为小明1的记录，发现trx_id是60，小于列表中的最小id,所以可以访问，直接访问结果为小明1。

那这时候我们把事务id为100的事务提交了，并且新建了一个事务id为110也修改id为1的记录，并且不提交事务

这时候之前那个select事务又执行了一次查询,要查询id为1的记录。

如果你是已提交读隔离级别，这时候你会重新一个ReadView，那你的活动事务列表中的值就变了，变成了[110]。按照上的说法，你去版本链通过trx_id对比查找到合适的结果就是小明2（因为110在ReadView中是110，所以这个版本不可以读）。

如果你是可重复读隔离级别，这时候你的ReadView还是第一次select时候生成的ReadView,也就是列表的值还是[100]。所以select的结果是小明1。所以第二次select结果和第一次一样，所以叫可重复读！（版本链中110大于100 不可取，100在ReadView中也不可以取，所以就是60的那条记录也就是小明1）

也就是说已提交读隔离级别下的事务在每次查询的开始都会生成一个独立的ReadView,而可重复读隔离级别则在第一次读的时候生成一个ReadView，之后的读都复用之前的ReadView。

这就是Mysql的MVCC,通过版本链，实现多版本，可并发读-写，写-读。通过ReadView生成策略的不同实现不同的隔离级别。