mysql隔离级别select语句影响_MySql性能（2）— 隔离级别带来的性能影响-CSDN博客

本文链接：https://blog.csdn.net/weixin_29830833/article/details/113553887

什么叫做快照读和当前读

快照读：select 操作；

当前读：insert、delete、update、select...for update、select ... in share mode

1. 隔离级别之MVCC

READ-UNCOMMITTED

读未提交RU，在该隔离级别下会读到未提交事务所产生的数据更改，这意味着会读到脏数据。

READ-COMMITTED

读已提交RC，在这一隔离级别下，可以在SQL级别做到一致性读，每次SQL语句都会产生新的ReadView。这就意味着两次查询之间有别的事务提交了，是可以读到不一致的数据的。

REPEATABLE-READ

可重复读RR，在第一次创建ReadView后(例如事务执行的第一条SELECT语句)，这个ReadView就会一直维持到事务结束，也就是说，在事务执行期间可见性不会发生变化，从而实现了事务内的可重复读。

SERIALIZABLE

序列化的隔离级别是最高等级的隔离级别，即将快照读升级为当前读(使用共享锁)，实现读写互斥。

在上面的叙述中，RC和RR均使用了MVCC技术。但是RC事务中SQL读取的是最新快照，而RR事务读取的是唯一快照。于是RR便解决了不可重复读的问题。

依靠MVCC机制，RR的快照读可以解决不可重复读和大多数情况下的幻读。

考虑这么一种情况：

1. SessionA开启事务

sessionA事务开启后，事务号为当前版本号 001

-- 确保当前隔离级别是不可重复读

set session transaction isolation level repeatable read;

-- 查看当前版本

select @@transaction_isolation;

-- 开启事务

BEGIN;

-- 进行快照读

SELECT * FROM USER_T;

a88a0d645228

当前数据库的隔离级别为—RR.png

a88a0d645228

此时数据库只有一条数据.png

2. SessionB插入数据

SessionB开启事务，此时事务号为当前版本号-002

插入查找，该行的版本号为事务号即-002

BEGIN;

-- 插入数据

insert user_t(id,user_name,password,age)

values('2','李丽','123','1');

COMMIT;

3. SessionA再次进行快照读

sessionA的事务号为-001，它的快照读只会读取表中行号小于等于001的数据，即只会读到一条数据，即避免了不可重复读+幻读；

-- 进行快照读

SELECT * FROM USER_T;

a88a0d645228

并发事务的情况下，不会影响其他sessionA事务.png

进行了Update操作，实际上执行了2个操作，即：

插入一条新数据，此时的行号为当前事务号-001；

删除原数据，将当前的事务号作为删除标识号-001；

-- 虽然快照读没有读到数据，但是使用update(当前读)可以修改数据

UPDATE user_t SET user_name='小三' where id='2';

再次进行快照读，读取表中行号小于等于001的记录，此时就是读取到了2行。即同一个事务中，出现不同的结果集。

-- 再次进行快照读。

SELECT * FROM USER_T;

a88a0d645228

快照读是出现幻觉—即出现幻读.png

需要注意的是：MVCC并不能解决幻读，事务A开始时快照读，事务B插入一条数据，事务A再次快照读是读取不到新数据的，但是事务A可是去修改新数据，而后进行快照读，便可以读取到两条数据，好像出现了幻读。

MVCC底层原理

MVCC的实现依赖：事务ID(DB_TRX_ID)和回滚指针(DB_ROLL_PTR)以及undo log和Read view实现的。

数据库每条记录中隐含包括3个列：这里用到的是事务ID(DB_TRX_ID)和回滚指针(DB_ROLL_PTR)

a88a0d645228

事务库记录的隐藏列.png

MVCC的实现依赖的是undo log，事务中去增删改某列，该列会产生undo log，而该列回滚指针(DB_ROLL_PTR)会指向产生的undo log，每列的undo log都会连成一条链表，称为版本链。通过undo log中的事务ID(DB_TRX_ID)可以进行回滚或者返回某个时刻快照。

例如：事务1对记录进行了修改：

a88a0d645228

事务1对age进行修改.png

例如：事务2再次对记录进行了修改：

a88a0d645228

事务2对name进行修改.png

而Read View就是事务进行快照操作时生产的读视图(Read View)。

2. 间隙锁的作用

上述可以看到，即使快照读没有读取到表中的数据，我们使用update操作依旧将表中的数据进行了更新，即当前读看到最新的数据。

而InnoDB为了解决当前读幻读问题，引入了间隙锁。

时间上在事务中对一行加了行级锁之后，其他事务是不能改变它的。即当前读不存在不可重复读的情况。

而为了解决幻读，InnoDB锁的不止是一行，一般我们进行范围操作或没有使用唯一索引进行操作时，它会锁住多行数据。

在数据库参数中，控制间隙锁的参数是：innodb_locks_unsafe_for_binlog，这个参数默认值是OFF，也就是启用间隙锁，他是一个boolean值，当值为true时表示disable间隙锁。那为了防止间隙锁是不是直接将innodb_locaks_unsafe_for_binlog设置为true就可以了呢？不一定！而且这个参数会影响到主从复制及灾难恢复，这个方法还尚待商量。

上述的描述中，可以得到的信息就是：间隙锁可以解决主从复制的一致性问题。

mysql数据库的主从复制依靠的是binlog。而在mysql5.0之前，binlog模式只有statement格式。这种模式的特点：binlog的记录顺序是按照数据库事务commit顺序为顺序的。

a88a0d645228

事务的并发操作.png

而实际上，在statement模式下，binlog记录的日志为先插在删。就会导致master节点执行先删在插，但是slave节点却执行的是先插在删，最终导致数据的不一致。

而间隙锁就会使得sessionB的阻塞。此时会等待sessionA的提交sessionB才会提交，保证了主从的一致性。

而上面也说，在mysql5.0版本之前，binlog只存在statement模式，所以为了主从一致性，mysql默认的隔离策略为可重复读。

在mysql5.0版本之后，binlog提供了row模式：即基于行的复制。故解决了sql主从节点执行不一致的问题。

3. 间隙锁的危害

在RR级别中，启用间隙锁的情况下，事务之间可能会互相影响，那么我们测试几种特殊的情况，以优化项目中sql语句。

事务A没有使用索引来操作表，将会对整个表加锁；

加锁的基本单位是(next-key lock)，它是前开后闭的原则；