深入了解mysql的隔离级别和锁机制

简述:
我们的MySQL一般会并发的执行多个事务,多个事务可能会并发的对同一条或者同一批数据进行crud操作;可能就会导致我们平常所说的脏读、不可重复读、幻读这些问题.
这些问题的本质都是MySQL多事务并发问题,为了解决多事务并发问题,MySQL设计了锁机制、MVCC多版本并发控制隔离机制、以及事务隔离机制,用一整套机制来解决多事务并发所出现的问题.

1. 事务的四大特性

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

3.事务的隔离级别

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

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

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

mysql版本:5.7.34
涉及表:
两个MySQL客户端
客户端A <===================> 客户端B(下面每张图片两个客户端皆以第一张图命名为准

1. 读未提交
   
   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. 读已提交
   

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. 可重复读

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. 串行化

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

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

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

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

• 合理使用索引字段加锁,缩小锁的范围
• 尽可能让所有锁都加到索引字段上,避免无索引行锁升级成表锁
• 尽可能减少查询范围,避免间隙过大的间隙锁
• 尽可能低级别事务隔离
• 尽可能控制事务大小,减少锁定资源量,涉及事务加锁的sql尽量放在事务最后执行,减少加锁的时间