事务隔离级别

幻读：事务在插入已经检查过不存在的记录时，惊奇的发现这些数据已经存在了，之前检测获取到的数据如同鬼影一般。

不可重复读：同样的条件，你读取过的数据，再次读取出来发现值不一样了。

mysql 在RR级别下，通过MVCC解决了部分幻读问题。

准备

drop table if exists city;
create table city(
    id int(11) primary key,
    name varchar(200)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

一些命令

-- 获取当前的隔离级别
select @@tx_isolation;


SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SET AUTOCOMMIT=0;
-- 设置锁超时时间
set innodb_lock_wait_timeout=100;
set global innodb_lock_wait_timeout=100;

READ-UNCOMMITTED级别下

从上面执行过程看以看到，T1事务，在执行2操作之后，还没提交，T2去读的时候，就读到了数据，当T1 回滚之后，T2再次读取，这条数据就不见了。

这就是在RU级别下，产生的读取到别人未提交的数据从而产生的脏读。

READ-COMMITTED级别下

幻读

T1和T2同时开启事务，且这个时候city表为空。

在第2步，T1插入一条数据，未提交。T2这个时候读取数据是读取不到的（RU级别的区别）。
但是在T1 COMMIT之后，T2这个时候查询的时候就跟之前不一样，这就是读到了别人已经提交过的数据。

不可重复读

在第7行，T2读取到了T1未提交的数据，5,7两次读取到的结果不一致，产生了不可重复读。

REPEATABLE READ 级别下

解决不可重复读

无论T1是否提交，T2读到的数据始终是开启事务时查询到的数据

解决一般性幻读

可以看到，无论T1是否提交数据，在T2事务中总是看不到最新的数据，这样就解决了幻读。
MYSQL通过MVCC解决了一般性幻读和可重复读；

InnoDB存储引擎MVCC的实现策略

在每一行数据中额外保存两个隐藏的列：当前行创建时的版本号和删除时的版本号（可能为空，其实还有一列称为回滚指针，用于事务回滚）。这里的版本号并不是实际的时间值，而是系统版本号。每开始新的事务，系统版本号都会自动递增。事务开始时刻的系统版本号作为事务的版本号，用来和查询每行记录的版本号进行比较。

MVCC下InnoDB的增删改查是如何工作的

• 插入数据（insert）：记录的版本号即当前事务的版本号。
  执行一条插入的SQL语句,insert into test values(1,"test");
  假设事务ID为1，那么插入后的数据行如下：

• 在更新操作的时候，采用的是先标记旧的那行记录为已删除，并且删除版本号是事务版本号，然后插入一行新的记录的方式。

比如执行更新语句：update test set name="new_test" where id=1;
事务ID=2

• 删除操作的时候，就把事务版本号作为删除版本号。
  比如：delete from test where id=1;
  事务ID=3

• 查询操作：

从上面的描述可看到，在查询时要符合以下两个条件的记录才能被事务查询出来：

1. 删除版本号未指定或者大于当前事务版本号，即查询事务开启后确保读取的行未被删除。（即上述事务ID=2的事务查询时，依然能读取到事务ID=3所删除的数据行）

2. 创建版本号小于或者等于当前事务版本号，也就是说记录创建是在当前事务中（等于的情况）或者在当前事务启动之前的其他事务进行的insert

仍然存在幻读

T2明明没有查询到数据，但是插入的时候，却提示已经存在了数据，就像见鬼了一样。

是什么造成了这样一个问题呢？

在MVCC中，读操作分为两种：一种是快照读，另外一种是当前读。

• 快照读即按照版本号读取
• 当前读则是读取最新的数据，操作：insert/update/delete都属于当前读，需要加锁。
  • select * from table where ? lock in share mode;
  • select * from table where ? for update;
  • insert into table values(...);
  • update table set ? where ?;
  • delete from table where ?;

delete ,update ,insert ,select .. for update 都是加的X锁即排它锁。
select .. lock in share mode 是共享锁

insert into city values(11,"test");

通过next key lock 解决幻读

Next Key Lock

InnoDB有三种行锁算法：

• Record Lock:单条记录上的锁
• Gap Lock:间隙锁，锁定一个范围，但不包括记录本身。
• Next Key Lock:Record Lock + Gap Lock；锁定一个范围，并且锁定记录本身。

GAP锁的目的是防止出现两次当前读不一致的情况

drop table if exists person;
CREATE TABLE `person` (
  `id` int(11) NOT NULL,
  `age` int(11) DEFAULT NULL,
  `name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `age` (`age`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

insert into person values(1,10,"t1");
insert into person values(2,20,"t2");
insert into person values(3,30,"t3");

可以发现在age索引范围内[10,30)

使用普通索引增加X锁

 drop table if exists book;
 create table book(
    id int ,
    key id_x(id)
) engine=innodb;

insert into book values(1),(3),(5),(8),(11);

如果查询条件中没有使用索引且加了X锁，那么将会锁住整张表。

create table book(
    id int ,
    price int ,
    primary key(price)
) engine=innodb;

 insert into book values (1,9),(3,5),(5,8),(8,6),(11,2);

GAP的范围

drop table if exists book;
create table book(
  id int,
  key id_x(id)
) engine=innodb;
insert into book values(8),(3);

从3,4,5,6操作中可以看到，能够插入的数据只有 id<3的情况
why ??
我们先看下 2操作之后产生间隙
(3,8] (8 ~ ∞]
所以，id>=8都无法插入；

但是为什么3也插入不了呢？

接下来看下面的操作：

create table book (id int ,seq int primary key ,key id_x(id)) engine=innodb;

insert into book values(8,10),(3,5);

第一个表是没有主键的，所以mysql会有一个rowid作为主键，是递增的。而下面的操作是有主键的。
对比上下两个操作，我们可以得出一个结论：
假设间隙是： (3,8]₍₈∞)
那么 可插入的值为： id<=3 and seq <5

接着继续插入一条数据：

insert into book values(11,2);

3操作之后，加锁的间隙为：
(3,8],(8,11]
那么可插入的值为：
(id <=3 and seq<5 ) or (id >=11 and seq>2)

间隙是根据什么来的呢？？
索引的结构

在B+Tree索引的叶子节点是一个顺序的列表，那么上述的结构的链表如下所示：

(3,5) -> (8,10) -> (11,2)
从这个结构来看，如果现在有一条数据(11,0)插入，结果产生的链表应该为：
(3,5) -> (8,10) -> (11,0) -> (11,2)

所以，间隙就是根据链表结构来的：
以上面链表来看，如果 执行的是select * from book where id=8 for update ;

那么插入的范围应该是：
(id <=3 and seq<4) or (id>=11 and seq>0)

当查询的索引含有唯一属性的时候，Next-Key Lock会进行优化，将其降级为Record Lock，即仅锁住索引本身，而不是范围。

drop table if exists book;
create table book(
     id int primary key,
     seq int,
    unique key seq_x(seq)
)engine=innodb;
insert into book values(3,3),(5,5),(8,8),(11,11),(1,1);

降级为record锁

从操作3可以看到，2操作并没有锁定间隙。

注意： 通过主键或者唯一索引来锁定不存在的值，也会产生GAP锁定。

从操作3可以看到，操作2锁定了一条不存在的数据，仍然产生了间隙锁。

幻读的理解

首先，mysql 幻读并非是“一个事务内进行两次相同操作居然得到了不一样的结果”，因为它根本不可能发生在使用了read view/MVCC的RR隔离级别下，这种幻读的定义更适合给Oracle，Oracle的事务隔离级别只有两级，RC和Serializable。

这里给出mysql幻读的比较形象的场景：
users:id 主键

1.T1:select * from users where id = 1;
2.T2:insert into `users`(`id`,`name`) values(1,'big cat');
3.T1:insert into `users`(`id`,`name`) values(1,'big cat');

T1:主事务，检测表中是否有id为1的记录，没有则插入，这是我们期望的正常业务逻辑。
T2:干扰事务，目的在于扰乱T1的正常的事务执行。
在RR隔离级别下，1,2是会正常执行的，3则会报错主键冲突，对于T1的业务来说是执行失败的，这里T1就是发生了幻读，因为T1读取的数据状态并不能支持他的下一步的业务，见鬼了一样。
在Serializable隔离级别下，1执行时是会隐式的添加gap共享锁的，从而2会被阻塞，3会正常执行，对于T1来说业务是正确的，成功的扼杀了扰乱业务的T2，对于T1来说他读取的状态是可以拿来支持业务的。
所以mysql的幻读并非什么读取两次返回结果集不同，而是事务在插入事先检测不存在的记录时，惊奇的发现这些数据已经存在了，之前的检测读获取到的数据如同鬼影一般。
这里要灵活的理解读取的意思，第一次select是读取，第二次的insert其实也属于隐式的读取，只不过是在mysql的机制中读取的，插入数据也是要先读取一下有没有主键冲突才能决定是否执行插入。
不可重复读侧重表达读-读，幻读则是说读-写，用写来证实读的是鬼影。

死锁

死锁检测回滚策略：保留回滚代价最小的事务。