MySQL隔离级别和锁机制的深入讲解

原作者: [db:作者] 来自: [db:来源] 收藏邀请

简述:

我们的MySQL一般会并发的执行多个事务,多个事务可能会并发的对同一条或者同一批数据进行crud操作;可能就会导致我们平常所说的脏读、不可重复读、幻读这些问题.

这些问题的本质都是MySQL多事务并发问题,为了解决多事务并发问题,MySQL设计了锁机制、MVCC多版本并发控制隔离机制、以及事务隔离机制,用一整套机制来解决多事务并发所出现的问题.

1. 事务的四大特性

特性	特点
Atomicity(原子性)	事务是不可分割的,其对数据的修改,要么全都执行,要么全都不执行
Consistency(一致性)	在事务提交的前后的状态和数据都必须是一致的
Isolation(隔离性)	在多事务并发时,保证事务不受并发操作影响的"独立"环境执行,这就意味着事务处理过程中的中间状态对外部是不可见的,反之亦然
Druability(持久性)	指事务一旦提交,数据就持久化保存到磁盘中不会丢失

2.多事务并发带来的问题

问题	现象	描述
脏读	A事务正在对一条记录做修改,在A事务完成并提交前,这条记录的数据就处于不一致的状态(有可能回滚也有可能提交),与此同时,B事务也来读取同一条记录,如果不加控制,B事务读取了这些"脏"数据,并据此作进一步处理,就会产生未提交的数据以来关系	一个事务中读取到另一个事务尚未提交的数据,不符合一致性要求
不可重复读	一个事务在读取某些数据后的某个时间,再次读取以前读过的数据,却发现其读出的数据已经发生了改变或某些记录已经被删除了	一个事务中多次读取的数据不一致,原因是收到其他事务已提交update的干扰,不符合隔离性
幻读	一个事务按相同的查询条件重新读取以前查询过的数据,却发现其他事务插入满足其查询条件的新数据	一个事务中多次读取的数据不一致,原因是受其他事务已提交insert/delete的干扰,不符合隔离性

3.事务的隔离级别

脏读、不可重复读和幻读,其实都是MySQL读一致性问题,必须由数据库提供一定的事务隔离机制来解决.

隔离级别	脏读	不可重复读	幻读
Read uncommitted(读未提交)	√	√	√
Read committed(读已提交)	×	√	√
Repetatble read(可重复读)(MySQL默认)	×	×	√
Serializable(串行化)	×	×	×

查看当前数据库的事务隔离级别:show variables like ‘tx_isolation';

设置事务隔离级别:set tx_isolation='隔离级别'

4.演示不同隔离级别出现的问题

mysql版本:5.7.34

涉及表:

两个MySQL客户端

客户端A <===================> 客户端B(下面每张图片两个客户端皆以第一张图命名为准

读未提交

1.1 设置事务隔离级别set tx_isolation=‘read-uncommitted';

1.2 客户端A和客户端B各开启一个事务,

1.3 客户端A只做查询,客户端B对id = 1的记录做修改;

1.4 再两个事务都未提交的情况下,事务A读到了事务B修改后的数据

1.5 一旦客户端B的事务因为某种原因rollback,那么客户端A查询到的数据其实就是脏数据,不符合一致性的要求

读已提交

2.1 设置隔离级别读已提交:set tx_isolation=‘read-committed';

2.2 客户端A和客户端B各开启一个事务,

2.3 客户端A只做查询,客户端B对id = 1的记录做修改;

2.4 客户端B未提交事务时,客户端A不能查询客户端B未提交的数据,解决了脏读的问题

2.5 当客户端B提交事务后,客户端A再次对表进行查询,结果与上一步不一致,即产生了不可重复读的问题,不符合隔离性

可重复读

3.1 设置隔离级别可重复读:set tx_isolation=‘repeatable-read';

3.2 客户端A和客户端B各开启一个事务,

3.3 客户端B修改表中数据然后提交;

3.4 客户端A查询表中数据,并未出现与上一步不一致的问题,解决了不可重复读的问题

3.5 在客户端A中执行update account set balance = balance - 100 where id = 1;blance并未有变成800-100=700;而是使用客户端B提交后的数据来算的,所以是600;数据的一致性并没有被破坏;可重复读的隔离级别下使用的是MVCC机制,select操作不会更新版本号,是快照读(历史版本),保证同一事务下的可重复读;insert/update/delete会更新版本号,是当前读(当前版本)保证数据的一致性

3.6 客户端B重新开启一个事务插入一条数据后提交

3.7 在客户端A中重新查询表数据,并没有出现客户端B刚才新增的数据,没有出现幻读

3.8 验证幻读:在客户端A中,对id = 4 的数据做修改;可以更新成功;再次进行查询就能查询出客户端B新增的数据,出现幻读问题,不符合隔离性

串行化

4.1 设置隔离级别串行化:set tx_isolation=‘serializable';

4.2 客户端A和客户端B各开启一个事务,

4.3 客户端A先查询表中id = 1的数据

4.4 在客户端A事务未提交时,客户端B对表中id = 1 的数据做更新;由于客户端A的事务并没有提交,客户端B的更新动作将会阻塞至到客户端A提交事务或者超时,超时SQL报错:Lock wait timeout exceeded; try restarting transaction

4.5 在客户端B中更新id = 2 的数据却可以成功,说明在串行化的隔离级别下,innodb的查询也会被加上行锁;

4.6 如果客户端A执行的是一个范围查询,那么该范围内的所有行包括每行记录所在的间隙区间范围(就算该行未被插入也会加锁,这种是间隙锁)都会被加锁,此时如果客户端B对该范围内的数据做任何操作都会被阻塞;所以就避免了幻读;

4.7 串行化这种隔离级别并发性极低,所以再真实的开发很少会遇到,这也是MySQL为什么使用可重复读作为默认的隔离级别的重要原因

5.锁机制

MySQL默认的隔离级别是可重复读,可是还是会出现幻读问题;间隙锁再某种情况下可以解决幻读问题;

间隙锁

概述:间隙锁,锁的就是两个值之间的空隙.

假设表中数据如下:

那么间隙就有(4,10)、(10,15)和(15,正无穷)三个间隙;

1.1 设置隔离级别可重复读:set tx_isolation=‘repeatable-read';

1.2 客户端A和客户端B各开启一个事务,

1.3 在客户端A执行update account set balance = 1000 where id > 5 and id < 13 ;

1.4 在客户端A未提交的时候,客户端B是没有办法对这个范围包含的所有行记录(包括间隙行记录)以及行记录所在间隙里执行insert/update操作,即4<id<=15这个区间内都无法修改数据,id = 15 同样不能修改;

1.5 间隙锁只有在可重复读的隔离级别下才会生效

临建锁

概述:临建锁是行锁和间隙锁的结合,想上面那个4<id<=15就属于临建锁;

无索引行锁会升级成为表锁

3.1 客户端A和客户端B各开启一个事务,

3.2 在客户端A执行update account set balance = 1000 where name = ‘李四';

3.3 在客户端A未提交的时候,客户端B执行update account set balance = 800 where id = 15 ;同样会被阻塞至客户端A提交或者超时;

3.4 MySQL中的锁主要是加载索引字段上,如果使用再非索引字段上,行锁会升级成表锁;

排他锁

4.1 客户端A和客户端B各开启一个事务,

4.2 在客户端A执行select * from account where id = 1 for update ;

4.3 在客户端A未提交的时候,客户端B执行update account set balance = 800 where id = 1 ;会被阻塞至客户端A提交或者超时;

结论:Innodb引擎实现了行锁,虽然行锁机制实现方面所带来的性能损耗可能比表级锁定会更高,但是再整体并发处理能力肯定要强于表级锁;当系统并发量高的时候,行级锁和表级锁相比就会有比较明显的优势;但是行级锁使用起来也比表级锁复杂,当我们使用不当的时候,可能会使行锁的性能不仅不比表级锁的性能高,甚至可能会更差.

为什么行锁锁定的粒度小,开销反而会比表级锁的开销大?

因为表级锁只需要找到当前表就可以进行加锁,行锁的话需要对表中记录进行扫描,直至扫描到需要加锁的行才可以进行加锁,所以行锁的开销是比表级锁的开销要来得大的.

真实开发情况下对锁优化的一些建议: