Mysql InnoDB隔离级别

Mysql InnoDB隔离级别

隔离级别是针对事务的。也就是事务之间能够相互影响的程度。主要是为了预防在并发的时候发现的问题。

问题：

• 更新丢失问题

• 一致性读问题

  • 脏读 可能读取到其他会话中未提交事务修改的数据

  • 不可重复读 能够读取到别的事务更新后的数据

  • 幻读 第一个事务对一个表中的数据进行了修改，这种修改涉及到表中的全部数据行。同时，第二个事务也修改这个表中的数据，这种修改是向表中插入一行新数据。那么，以后就会发生操作第一个事务的用户发现表中还有没有修改的数据行，就好象发生了幻觉一样。

为了解决以上3种一致性读的问题，InnoDB提出了4种隔离级别。为了实现更高的隔离界别，所加的锁也就更多，并发性就会越差。

隔离级别	脏读（Dirty Read）	不可重复读（NonRepeatable Read）	幻读（Phantom Read）
未提交读（Read uncommitted）	可能	可能	可能
已提交读（Read committed）	不可能	可能	可能
可重复读（Repeatable read）InnoDB解决了幻读	不可能	不可能	可能
可串行化（Serializable ）	不可能	不可能	不可能

不同级别达到的效果：

• 未提交读(Read Uncommitted)：允许脏读，也就是可能读取到其他会话中未提交事务修改的数据

• 提交读(Read Committed)：只能读取到已经提交的数据。Oracle等多数数据库默认都是该级别 (不重复读)

• 可重复读(Repeated Read)：可重复读。在同一个事务内的查询都是事务开始时刻一致的，InnoDB默认级别。在SQL标准中，该隔离级别消除了不可重复读，但是还存在幻象读

• 串行读(Serializable)：完全串行化的读，每次读都需要获得表级共享锁，读写相互都会阻塞

未提交读 Read uncommitted

隔离级别最弱的级别，别的事务没有提交就能够看到修改。不加锁。

已提交读 Read committed

升级版，当别的事务未提交时看不到修改，当别的事务提交后再去查看则能够看到修改。

数据的读不加锁，数据的 写入，修改，删除加锁

可以先修改隔离级别：

SET session transaction isolation level read committed;
SET SESSION binlog_format = 'ROW';#（或者是MIXED）

事务A	事务B
begin;	begin;
update class_teacher set class_name='初三二班' where teacher_id=1;	update class_teacher set class_name='初三三班' where teacher_id=1;
	ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
commit;

• 有索引的情形：

  第一个事务会对 teacher_id 这一行数据进行加锁，当第二个事务去修改的时候因为获取不到锁就会超时导致错误。

• 没有索引：

  1. 加表锁

  2. 过滤不需要加锁的行调用unlock_row方法。因为解锁的过程慢。就会出现虽然没有修改某些行的数据，但是它们还是被锁住了的现象。

可重复读 RC

可重复读，已提交读的升级版，意思是：别的事务提交了，当前的事务读取到的还是

事务A	事务B	事务C
begin;	begin;	begin;
select id,class_name,teacher_id from class_teacher where teacher_id=1;idclass_nameteacher_id1初三二班12初三一班1
	update class_teacher set class_name='初三三班' where id=1;commit;
		insert into class_teacher values (null,'初三三班',1);commit;
select id,class_name,teacher_id from class_teacher where teacher_id=1;idclass_nameteacher_id1初三二班12初三一班1没有读到事务B修改的数据，和第一次sql读取的一样，是可重复读的。没有读到事务C新添加的数据。
commit;

不可重复读和幻读的区别

不可重复读重点在于update和delete，而幻读的重点在于insert。

如果使用了锁机制实现隔离级别。在第一次读之后，将数据加锁，其他事务则无法修改这些数据，就可以实现可重复读，但是如果事务B是插入Insert操作。B提交之后，A再去读取，就会出现多的数据。->幻读。

这时候需要Serializable隔离级别 ，读用读锁，写用写锁，读锁和写锁互斥，这么做可以有效的避免幻读、不可重复读、脏读等问题，但会极大的降低数据库的并发能力。

所以说不可重复读和幻读最大的区别，就在于如何通过锁机制来解决他们产生的问题。

使用锁实现Serializable 是悲观锁方式，但是MySQL、ORACLE、PostgreSQL等成熟的数据库，出于性能考虑，都是使用了以乐观锁为理论基础的MVCC（多版本并发控制）来避免这两种问题。

乐观锁

相对悲观锁而言，乐观锁机制采取了更加宽松的加锁机制。悲观锁大多数情况下依靠数据库的锁机制实现，以保证操作最大程度的独占性。但随之而来的就是数据库性能的大量开销，特别是对长事务而言，这样的开销往往无法承受。

乐观锁原理：

乐观锁，大多是基于数据版本（ Version ）记录机制实现。何谓数据版本？即为数据增加一个版本标识，在基于数据库表的版本解决方案中，一般是通过为数据库表增加一个 “version” 字段来实现。读取出数据时，将此版本号一同读出，之后更新时，对此版本号加一。此时，将提交数据的版本数据与数据库表对应记录的当前版本信息进行比对，如果提交的数据版本号大于数据库表当前版本号，则予以更新，否则认为是过期数据。

MVCC

在InnoDB中，会在每行数据后添加两个额外的隐藏的值来实现MVCC.这两个值一个记录这行数据何时被创建，另外一个记录这行数据何时过期（或者被删除）。

隐藏的行: [存储的是事务的版本号]

• 记录这行数据何时被创建

• 记录这行数据何时过期 被删除

在可重复读的情形下，这么去更新和读取：

• SELECT时，读取创建版本号<=当前事务版本号，删除版本号为空或>当前事务版本号。

• INSERT时，保存当前事务版本号为行的创建版本号

• DELETE时，保存当前事务版本号为行的删除版本号

• UPDATE时，插入一条新纪录，保存当前事务版本号为行创建版本号，同时保存当前事务版本号到原来删除的行

读的分类

事务的隔离级别其实都是对于读数据的定义。MySQL中的读，和事务隔离级别中的读，是不一样的。

MVCC机制让数据b变得可重复读，但是数据k可能是历史数据。又称之为快照读。

• 快照读：就是select

  • select * from table ....;

• 当前读：特殊的读操作，插入/更新/删除操作，属于当前读，处理的都是当前的数据，需要加锁。以下几种方式都是当前读：

  • select * from table where ? lock in share mode;

  • select * from table where ? for update;

  • insert;

  • update ;

  • delete;

写

事务的隔离级别中虽然只定义了读数据的要求，实际上这也可以说是写数据的要求。上文的“读”，实际是讲的快照读；而这里说的“写”就是当前读了。为了解决当前读中的幻读问题，MySQL事务使用了Next-Key锁。

Next-Key锁

是行锁和GAP（间隙锁）的合并，行锁上文已经介绍了，接下来说下GAP间隙锁。

行锁避免了不同版本事务修改，提交(update)。但如何避免别的事务插入(Insert)数据就成了问题。

RC级别：

事务A	事务B
begin;	begin;
select id,class_name,teacher_id from class_teacher where teacher_id=30; #first
update class_teacher set class_name='初三四班' where teacher_id=30;
	insert into class_teacher values (null,'初三二班',30);commit;
select id,class_name,teacher_id from class_teacher where teacher_id=30;#second

First:

id	class_name	teacher_id
2	初三二班	30

Second:

id	class_name	teacher_id
2	初三四班	30
10	初三二班	30

RR级别：

事务A	事务B
begin;	begin;
select id,class_name,teacher_id from class_teacher where teacher_id=30;#first
update class_teacher set class_name='初三四班' where teacher_id=30;
	insert into class_teacher values (null,'初三二班',30);waiting....
select id,class_name,teacher_id from class_teacher where teacher_id=30;#second
commit;	事务Acommit后，事务B的insert执行。

First:

id	class_name	teacher_id
2	初三二班	30

Second:

id	class_name	teacher_id
2	初三四班	30

RR级别中，事务A在update后加锁，事务B无法插入新数据，这样事务A在update前后读的数据保持一致，避免了幻读。这个锁，就是Gap锁。

Gap锁实现

在class_teacher这张表中，teacher_id是个索引，那么它就会维护一套B+树的数据关系，为了简化，我们用链表结构来表达（实际上是个树形结构，但原理相同）

如图所示，InnoDB使用的是聚集索引，teacher_id身为二级索引，就要维护一个索引字段和主键id的树状结构（这里用链表形式表现），并保持顺序排列。

其中 5和30是数据行。Innodb将这段数据分成几个个区间

• (negative infinity, 5],

• (5,30],

• (30,positive infinity)；

update class_teacher set class_name='初三四班' where teacher_id=30;

不仅用行锁，锁住了相应的数据行；同时也在两边的区间，（5,30]和（30，positive infinity），都加入了gap锁。这样事务B就无法在这个两个区间insert进新数据。

没有索引的Gap锁

update class_teacher set teacher_id=7 where class_name='初三八班';

因为没有索引，所以没有办法分区间(有索引可以用B+tree 找到O(1)上一个值和下一个值)。这时候将锁全表..

Serializable

这个级别很简单，读加共享锁，写加排他锁，读写互斥。使用的悲观锁的理论，实现简单，数据更加安全，但是并发能力非常差。

参考：https://tech.meituan.com/innodb-lock.html