mysql查看隔离级别 ACID 四种隔离等级 mysql如何实现可重复读

1、查看当前会话的 隔离级别:

 select @@tx_isolation;

2、查看系统的隔离级别:

select @@global.tx_isolation;

3、设置会话的隔离级别，隔离级别由低到高设置依次为:

set session transacton isolation level read uncommitted;
set session transacton isolation level read committed;
set session transacton isolation level repeatable read;
set session transacton isolation level serializable;

4、设置当前系统的隔离级别，隔离级别由低到高设置依次为:

set global transacton isolation level read uncommitted;
set global transacton isolation level read committed;
set global transacton isolation level repeatable read;
set global transacton isolation level serializable;

注意：mysql默认的事务处理级别是'REPEATABLE-READ'，而Oracle和SQL Server是READ_COMMITED

 

ACID特性

数据库管理系统中事务(transaction)的四个特性（分析时根据首字母缩写依次解释）：原子性（Atomicity）、一致性（Consistency）、隔离性（Isolation）、持久性（Durability）

所谓事务，它是一个操作序列，这些操作要么都执行，要么都不执行，它是一个不可分割的工作单位。（执行单个逻辑功能的一组指令或操作称为事务）

详解

1. 原子性

原子性是指事务是一个不可再分割的工作单元，事务中的操作要么都发生，要么都不发生。

可采用“A向B转账”这个例子来说明解释

在DBMS中，默认情况下一条SQL就是一个单独事务，事务是自动提交的。只有显式的使用start transaction开启一个事务，才能将一个代码块放在事务中执行。

2. 一致性

一致性是指在事务开始之前和事务结束以后，数据库的完整性约束没有被破坏。这是说数据库事务不能破坏关系数据的完整性以及业务逻辑上的一致性。

如A给B转账，不论转账的事务操作是否成功，其两者的存款总额不变（这是业务逻辑的一致性，至于数据库关系约束的完整性就更好理解了）。

保障机制（也从两方面着手）：数据库层面会在一个事务执行之前和之后，数据会符合你设置的约束（唯一约束，外键约束,check约束等)和触发器设置；此外，数据库的内部数据结构（如 B 树索引或双向链表）都必须是正确的。业务的一致性一般由开发人员进行保证，亦可转移至数据库层面。

3. 隔离性

多个事务并发访问时，事务之间是隔离的，一个事务不应该影响其它事务运行效果。

在并发环境中，当不同的事务同时操纵相同的数据时，每个事务都有各自的完整数据空间。由并发事务所做的修改必须与任何其他并发事务所做的修改隔离。事务查看数据更新时，数据所处的状态要么是另一事务修改它之前的状态，要么是另一事务修改它之后的状态，事务不会查看到中间状态的数据。

事务最复杂问题都是由事务隔离性引起的。完全的隔离性是不现实的，完全的隔离性要求数据库同一时间只执行一条事务，这样会严重影响性能。

关于隔离性中的事务隔离等级（事务之间影响），参见相应博文

4. 持久性

这是最好理解的一个特性：持久性，意味着在事务完成以后，该事务所对数据库所作的更改便持久的保存在数据库之中，并不会被回滚。（完成的事务是系统永久的部分，对系统的影响是永久性的，该修改即使出现致命的系统故障也将一直保持）

 

 

 

 

MySQL的事务隔离级别

MySQL提供了4种事务隔离级别，其中最常用的InnoDB存储引擎的默认隔离级别为可重复读。

事务隔离级别	脏读	不可重复读	幻读
读未提交（read-uncommitted）	是	是	是
已提交读（read-committed）	否	是	是
可重复读（repeatable-read）	否	否	是
串行化（serializable）	否	否	否

 

 

 1、未提交读。

      最低的隔离级别（是一种危险的隔离级别，会出现脏读），其含义是允许一个事务读取另外一个事务没有提交的数据。

      

      T2时刻中事务1中扣减了库存，T3时刻中事务2也扣减了库存，但是它也读取到了T2时刻中事务1的操作，因此T3时刻扣减后库存为0，这个时候T5在提交事务后事务1进行回滚，因为第一类丢失更新已经被克服，所以库存最终会出现脏读(库存应该为1才是正确的)。

      2、读写提交。

      指一个事务只能读取到另外一个事务已经提交的数据。

      

        在T3时刻，因为采用了读写提交的隔离级别，所以事务2不会读取到事务1中未提交的库存1，所以在T3时刻事务2扣除库存后还剩下库存为1，然后提交事务。则库存在T4中就变成了1。在T5时刻，事务1回滚，因为第一类丢失更新已经克服，所以最后的库存结果也变为了1。

      3、可重复读。

      克服读写提交中出现的不可复读的现象，因为在读写提交的时候，可能出现一些值的变化，影响当前事务的执行。

      

      

      

      4、串行化

      最高的隔离级别，会要求所有的SQL按照顺序执行，这样就可以克服上述隔离级别出现的问题，所以能够保证数据的一致性。

 

InnoDB 与 MyISAM

 

1. InnoDB 支持事务，MyISAM 不支持事务。这是 MySQL 将默认存储引擎从 MyISAM 变成 InnoDB 的重要原因之一；

2. InnoDB 支持外键，而 MyISAM 不支持。对一个包含外键的 InnoDB 表转为 MYISAM 会失败；

3. InnoDB 是聚集索引，MyISAM 是非聚集索引。聚簇索引的文件存放在主键索引的叶子节点上，因此 InnoDB 必须要有主键，通过主键索引效率很高。但是辅助索引需要两次查询，先查询到主键，然后再通过主键查询到数据。因此，主键不应该过大，因为主键太大，其他索引也都会很大。而 MyISAM 是非聚集索引，数据文件是分离的，索引保存的是数据文件的指针。主键索引和辅助索引是独立的。

4. InnoDB 不保存表的具体行数，执行 select count(*) from table 时需要全表扫描。而MyISAM 用一个变量保存了整个表的行数，执行上述语句时只需要读出该变量即可，速度很快；

5. InnoDB 最小的锁粒度是行锁，MyISAM 最小的锁粒度是表锁。一个更新语句会锁住整张表，导致其他查询和更新都会被阻塞，因此并发访问受限。这也是 MySQL 将默认存储引擎从 MyISAM 变成 InnoDB 的重要原因之一；

如何选择：

1. 是否要支持事务，如果要请选择 InnoDB，如果不需要可以考虑 MyISAM；

2. 如果表中绝大多数都只是读查询，可以考虑 MyISAM，如果既有读写也挺频繁，请使用InnoDB。

3. 系统奔溃后，MyISAM恢复起来更困难，能否接受，不能接受就选 InnoDB；

4. MySQL5.5版本开始Innodb已经成为Mysql的默认引擎(之前是MyISAM)，说明其优势是有目共睹的。如果你不知道用什么存储引擎，那就用InnoDB，至少不会差。

 

 

 

• 第一类丢失更新
  撤销一个事务的时候，把其它事务已提交的更新数据覆盖了。这是完全没有事务隔离级别造成的。如果事务1被提交，另一个事务被撤销，那么会连同事务1所做的更新也被撤销。
• 脏读（Dirty Read）
  如果一个事务对数据进行了更新，但事务还没有提交，另一个事务就可以“看到”该事务没有提交的更新结果。这样就造成的问题就是，如果第一个事务回滚，那么第二个事务在此之前所“看到”的数据就是一笔脏数据。
• 不可重复读取（Non-Repeatable Read）
  不可重复读取是指同一个事务在整个事务过程中对同一笔数据进行读取，每次读取结果都不同。如果事务1在事务2的更新操作之前读取一次数据，在事务2的更新操作之后再读取同一笔数据一次，两次结果是不同的。所以Read Uncommitted也无法避免不可重复读取的问题。
• 第二类丢失更新
  它和不可重复读本质上是同一类并发问题，通常将它看成不可重复读的特例。当两个或多个事务查询相同的记录，然后各自基于查询的结果更新记录时会造成第二类丢失更新问题。每个事务不知道其它事务的存在，最后一个事务对记录所做的更改将覆盖其它事务之前对该记录所做的更改。
• 幻读（Phanton Read）
  幻读是指同样一个查询在整个事务过程中多次执行后，查询所得的结果集是不一样的。幻读针对的是多笔记录。在Read Uncommitted隔离级别下，不管事务2的插入操作是否提交，事务1在插入操作执行之前和之后执行相同的查询，取得的结果集是不同的，所以Read Uncommitted同样无法避免幻读。
  无事务隔离级别，则存在：第一类丢失更新、脏读、不可重复读、第二类丢失更新和幻读问题。
  Read Uncommitted存在：脏读、不可重复读、第二类丢失更新和幻读问题。
  Read committed存在：不可重复读、第二类丢失更新和幻读问题。
  Repeatable Read存在：幻读问题。
  Serializable 不存在问题。

 

 

二、mysql怎么实现的可重复读

MVCC多版本并发控制(Multi-Version Concurrency Control)是MySQL中基于乐观锁理论实现隔离级别的方式，用于实现读已提交和可重复读取隔离级别。

在《高性能MySQL》中对MVCC的解释如下

举例说明MVCC的实现

新建一张表test_zq如下

id

test_id

DB_TRX_ID

DB_ROLL_PT

MVCC逻辑流程-插入

在插入数据的时候，假设系统的全局事务ID从1开始，以下SQL语句执行分析参考注释信息：

1. begin;-- 获取到全局事务ID

2. insert into `test_zq` (`id`, `test_id`) values('5','68');

3. insert into `test_zq` (`id`, `test_id`) values('6','78');

4. commit;-- 提交事务

5. 复制代码

当执行完以上SQL语句之后，表格中的内容会变成：

id	test_id	DB_TRX_ID	DB_ROLL_PT
5	68	1	NULL
6	78	1	NULL

可以看到，插入的过程中会把全局事务ID记录到列 DB_TRX_ID 中去

MVCC逻辑流程-删除

对上述表格做删除逻辑，执行以下SQL语句（假设获取到的事务逻辑ID为 3）

1. begin；--获得全局事务ID = 3

2. delete test_zq where id = 6;

3. commit;

4. 复制代码

执行完上述SQL之后数据并没有被真正删除，而是对删除版本号做改变，如下所示：

id	test_id	DB_TRX_ID	DB_ROLL_PT
5	68	1	NULL
6	78	1	3

MVCC逻辑流程-修改

修改逻辑和删除逻辑有点相似，修改数据的时候 会先复制一条当前记录行数据，同事标记这条数据的数据行版本号为当前是事务版本号，最后把原来的数据行的删除版本号标记为当前是事务。

执行以下SQL语句：

1. begin;-- 获取全局系统事务ID 假设为 10

2. update test_zq set test_id = 22 where id = 5;

3. commit;

4. 复制代码

执行后表格实际数据应该是：

id	test_id	DB_TRX_ID	DB_ROLL_PT
5	68	1	10
6	78	1	3
5	22	10	NULL

MVCC逻辑流程-查询

此时，数据查询规则如下：

• 查找数据行版本号早于当前事务版本号的数据行记录

  也就是说，数据行的版本号要小于或等于当前是事务的系统版本号，这样也就确保了读取到的数据是当前事务开始前已经存在的数据，或者是自身事务改变过的数据

• 查找删除版本号要么为NULL，要么大于当前事务版本号的记录

  这样确保查询出来的数据行记录在事务开启之前没有被删除

根据上述规则，我们继续以上张表格为例，对此做查询操作

1. begin;-- 假设拿到的系统事务ID为 12

2. select * from test_zq;

3. commit;

4. 复制代码

执行结果应该是：

id	test_id	DB_TRX_ID	DB_ROLL_PT
6	22	10	NULL

这样，同一个事务中，就实现了可重复读。

三、幻读

什么是幻读，如下：

InnoDB实现的RR通过mvcc机制避免了这种幻读现象。

另一种幻读：

姑且把左边的事务命名为事务A，右边的命名为事务B。
事务B执行后，在事务A中查询没有查到B添加的数据行，这就是可重复读。
但是，在事务A执行了update后，再查询时就查到了事务A中添加的数据，这就是幻读。
这种结果告诉我们其实在MySQL可重复读的隔离级别中并不是完全解决了幻读的问题，而是解决了读数据情况下的幻读问题。而对于修改的操作依旧存在幻读问题，就是说MVCC对于幻读的解决是不彻底的。

快照读和当前读

出现了上面的情况我们需要知道为什么会出现这种情况。在查阅了一些资料后发现在RR级别中，通过MVCC机制，虽然让数据变得可重复读，但我们读到的数据可能是历史数据，不是数据库最新的数据。这种读取历史数据的方式，我们叫它快照读 (snapshot read)，而读取数据库最新版本数据的方式，叫当前读 (current read)。

select 快照读

当执行select操作是innodb默认会执行快照读，会记录下这次select后的结果，之后select 的时候就会返回这次快照的数据，即使其他事务提交了不会影响当前select的数据，这就实现了可重复读了。快照的生成当在第一次执行select的时候，也就是说假设当A开启了事务，然后没有执行任何操作，这时候B insert了一条数据然后commit,这时候A执行 select，那么返回的数据中就会有B添加的那条数据。之后无论再有其他事务commit都没有关系，因为快照已经生成了，后面的select都是根据快照来的。

当前读

对于会对数据修改的操作(update、insert、delete)都是采用当前读的模式。在执行这几个操作时会读取最新的版本号记录，写操作后把版本号改为了当前事务的版本号，所以即使是别的事务提交的数据也可以查询到。假设要update一条记录，但是在另一个事务中已经delete掉这条数据并且commit了，如果update就会产生冲突，所以在update的时候需要知道最新的数据。也正是因为这样所以才导致幻读。

• 在快照读读情况下，mysql通过mvcc来避免幻读。
• 在当前读读情况下，mysql通过next-key来避免幻读

四、如何解决幻读

很明显可重复读的隔离级别没有办法彻底的解决幻读的问题，如果我们的项目中需要解决幻读的话也有两个办法：

• 使用串行化读的隔离级别
• MVCC+next-key locks：next-key locks由record locks(索引加锁/行锁) 和 gap locks(间隙锁，每次锁住的不光是需要使用的数据，还会锁住这些数据附近的数据)的结合，next-key lock 会锁定范围和自身行，比如select...where id<6，锁定的是小于6的行和等于6的行

Next-Key Lock即在事务中select时使用如下方法加锁，这样在另一个事务对范围内的数据进行修改时就会阻塞：

1. select * from table where id<6 lock in share mode;--共享锁

2. select * from table where id<6 for update;--排他锁

实际上很多的项目中是不会使用到上面的两种方法的，串行化读的性能太差，而且其实幻读很多时候是我们完全可以接受的。

关于next-key locks请参考https://www.cnblogs.com/zhoujinyi/p/3435982.html

参考文章：

https://juejin.im/post/5c68a4056fb9a049e063e0ab

https://zhuanlan.zhihu.com/p/35500144

https://www.jianshu.com/p/69fd2ca17cfd

《高性能MySQL》