mysql innodb隔离级别_MySQL InnoDB的隔离级别

1.概念

隔离性主要是指数据库系统通过一定的隔离机制，保证事务在不受外部并发操作影响的"独立"环境执行，意思是多个事务并发执行时，一个事务的执行不影响其他事务的执行。

2.隔离界别

SQL标准中定义了4中隔离级别。

1>Read uncommitted 未提交读，事务中的修改，及时没有提交，对其他事务也是可见的，存在脏读。

2>Read commited 提交读，大多数数据库系统的默认隔离级别，但是MySQL不是，一个事务从开始到提交之前，所做的修改对其他事务不可见，解决脏读，但存在幻读和不可重读。

3>reoeatable read 可重复读，该级别保证在同一个事务中多次读取同样记录的结果是一致的，解决脏读和不可重复读，理论上存在幻读，但是在InnoDB引擎解决了幻读。

4>Serializable 可串行化，强制事务串行执行。

这四种隔离级别是QL标准定义的，但是在不同的存储引擎中，实现的隔离级别不尽相同。

在InnoDB存储引擎中，Reapeatable Read是默认的隔离级别，同时该引擎的实现基于多版本的并发控制协议-MVCC(Multi-Version Concurrency Controler)，解决了幻读问题，当然脏读和不可重复读也是不存在的，MVCC最大的好处在于读不加锁，读写不冲突，这样加大的增加了系统的并发性能。

Read uncommitted

未提交读，这种情况下，一个事务A可以看到另一个事务B未提交的数据，如果此时事务B发生回滚，那么事务AA拿到的数据就是脏数据，这也是脏读的含义。次隔离级别在MySQL InnoDB一般是不会使用的。

Read Committed

提交读，一个事务从开始直到提交事务，所做的任何修改对其他事务都是不可见的。解决了脏读问题，但是存在幻读问题。

注:幻读 -指的是在同一个事务下，连续执行两次同样的SQL语句可能导致不同的结果，第二次的SQL语句

Repeatable Read

可重复读，该级别保证在通过一个事务中多次读取同样记录的结果是一致的，在InnoDB存储中同时解决了幻读和不可重复读的问题。

Serializable(可串行化)

Serializable是最高的隔离级别，他通过强制事务串行执行，避免了幻读的问题，但是Serilizable会在读取的每一行数据上加锁，所以可能导致大量的超时和锁竞争的问题因此并发读集聚下降。

Read Committed 隔离级别下的加锁分析

隔离级别的实现与锁机制密不可分，所以引入锁的概念，我们先看InnoDB存储引擎提供的两种标准的行级锁。

1>共享锁 又可称为读锁，可以允许多个事务并发的读取同一资源，互不干扰，即如果一个事务T对数据A加上共享锁后，其他事务只能付A再加共享锁，不能再加排它锁，只能读数据，不能修改数据。

2>排它锁 又称写锁，如果事务T对数据A加上排它锁后，其他事务不能再对A加上任何类型的锁，获取排它锁的事务技能读数据，也能修改数据。

注：共享锁和排它锁是不能相容的。

MySQL InnoDB存储引擎是使用多版本并发控制的，读不加锁，读写补冲突，除非特定场景下的显示加读锁。这里分析Read Committed隔离级别下的加锁情况，在MVCC的作用下，一般也就是写操作加X锁。

加锁操作是和索引紧密相关的，对一个SQL语句进行加锁分析时，也要仔细考究其属性列上的索引类型，假设有数据表t1,有两列，name列和id列，插入了几条数据，没明确索引情况：

insert into t1(name,id) values("a",10);

insert into t1(name,id) values("b",11);

insert into t1(name,id) values("c",13);

insert into t1(name,id) values("d",20);

下面执行delete from t1 where id =10这条SQL语句，这里的隔离级别设置为Read Commited，从这条SQL语句不能得知id列索引的情况，所以需要分情况讨论：id列是主键

id列是二级唯一索引

id列是二级非唯一索引

id列上没有索引

下面是对以上几种情况的加锁情况进行归纳总结。

第一种 id列是主键

id是主键时，上述SQL只需要在id=10这条记录上加X锁即可。

第二种情况 id列是二级唯一索引

若id列是唯一索引，而主键是name列，那么SQL需要加上两个X锁，一个是对应id索引上的id=10的记录，另一把锁对应于主键索引上的[name='a',id=10]的记录

第三种情况 id列是二级非唯一索引

若id列上有非唯一索引，那么对应的所有满足SQL查询条件的记录，都会被加锁，同时，这些记录在主键索引上的记录也会被加锁。

第四种情况 id列上没有索引

若id列上没有索引，SQL会走聚簇索引的全扫描进行过滤，由于过滤是由MySQL Sever层面进行的，因此每条记录，无论是否满足条件，都会被加上X锁。

Repeatable Read隔离级别下的 加锁分析

在Repeatable Read隔离级别下，InnoDB存储引擎解决了幻读和不可 重复读问题。

InnoDB中行级锁的三种算法：Record Lock 单个索引记录上的锁，即加X锁。

Gap Lock 间隙锁，锁定一个范围，但不包含记录自身。

Next-Key Lock: Gap Lock + Record Lock，锁定一个范围，并且锁定本身.

Record Lock 总是会去锁住索引记录，如果InnoDB存储引擎在建表的时候没有设置任何一个索引，那么这是InnoDB会使用隐式的主键来进行锁定，(表没有定义主键的情况下，InnoDB会默认添加一隐式的主键索引)

Next-Key Lock是结合了Gap Lock和Record Lock的一种锁定算法，比如一个索引列有10,11,13和20这4个值，那么该索引可能被Next-Key Locking的区间为($-\infty$,10)

(10,11]

(11,13]

(13,20]

(20,$+\infty$)

需要注意一点的是，当查询的索引含有唯一属性时，即是主键索引或者唯一索引时，InnoDB存储引擎会对Next-Key Lock进行优化，将其降级为Record Lock,即仅锁住索引本身，一般加上X锁。

Next-Key Lock机制设计的目的就是为了解决幻读问题，主要针对查询列索引为非唯一索引的时候。以下面这个例子进行说明：

1.首先创建测试表t1，name是主键索引，id为非唯一索引，即辅助索引

CREATE TABLE `t1` (

`id` int(11) NOT NULL,

`name` varchar(200) DEFAULT NULL,

PRIMARY KEY (`name`),

KEY `id_indx` (`id`)

) ENGINE=InnoDB

insert into t1(name,id) values("a",10);

insert into t1(name,id) values("b",11);

insert into t1(name,id) values("c",13);

insert into t1(name,id) values("d",20);

2.执行 delete from t1 where id = 11,其加锁情况如下图所示

这条SQL通过索引列id进行删除操作，该索引为非唯一索引，所以其使用传统的Next-Key Locking 技术加锁，并且由于有主键索引和辅助索引两个，需要分别进行锁定。对于主键索引(即聚集索引)，其仅对列name = "b"的索引加上 Record Lock，实际上就是X锁。

而对于非唯一索引，其加上的时Next-Key Lock，锁定范围是(10,11)，对其加上Gap Lock(间隙锁)，GAP锁实际上就是加在两条边界记录之间的位置。还需要注意的是，InnoDB还会对辅助索引下一个键值加上gap lock，即看到在(11,13)之间加了一个GAP锁。对于11值本身加上Record Lock，即X锁

若此时开启另外一个事务执行下面的语句，就会阻塞：

1. select * from t1 where name = "b";

2. insert into t1(name,id) values("c",12);

比如第一条语句不能执行，因为在开始的事务中已经对聚集索引中的列name="b"的值加上了X锁。因此执行会被阻塞。而第二个SQL，同样不能执行，插入的值12在锁定范围(11,13)中，需要阻塞等待。

所以，从上例就可以看出，GAP Lock的作用就是为了阻止多个事务将记录插入到同一范围内，这样就有效的解决了幻读问题。

隔离级别总结

参考资料https://book.douban.com/subject/24708143/​book.douban.comhttps://link.juejin.im/?target=http%3A%2F%2Fhedengcheng.com%2F%3Fp%3D771​link.juejin.im浅析MySQL InnoDB的隔离级别​juejin.im