【mysql数据库】事务的隔离级别和MVCC和锁

一、事务的隔离级别和MVCC

1、事务的特性

原子性（Atomicity）、一致性（Consistency）、隔离性（Isolation）和持久性（Durability）。

2、隔离级别

（1）脏读（Dirty Read）：当前事务能够看到别的事务中未提交的数据。A事务读取B事务尚未提交的数据并在此基础上操作，而B事务执行回滚，那么A读取到的数据就是脏数据。

（2）不可重复读（Non-repeatable Reads） ：一个事务对同一行数据重复读取两次，但是却得到了不同的结果。事务T1读取某一数据后，事务T2对其做了修改，当事务T1再次读该数据时得到与前一次不同的值。

（3）幻读：在其中一个事务中读取到了其他事务新增的数据，仿佛出现了幻影现象。

 

show variables like 'tx_isolation'语句可以查看当前设置的隔离级别

隔离级别	脏读	不可重复读	幻读
Read UnCommitted(读未提交)	可能	可能	可能
Read Committed(读已提交)	不可能	可能	可能
Repeatable Read (重复读)【mysql的默认隔离级别】	不可能	不可能	可能
Serializable(序列化)	不可能	不可能	不可能

当InoDB的隔离级别是 Repeatable Read(可重复读)级别时。

InnoDB采用了“一致性非锁定读”的机制，提高了数据库并发性。一致性非锁定读表示如果当前行被施加了排它锁，那么当需要读取行数据时，则不会等待行上锁的释放，而是会去读取一个快照数据。InnoDB中一致性非锁定读的过程之所以称为非锁定读，是因为它不需要等待被访问的行上排他锁的释放。其实快照就是该行所对应的之前版本已提交的数据，即历史数据，快照的实现是由事务日志所对应的undo段来完成的。

 

3、MVCC的实现原理

3.1、数据页面中的记录和Undo日志组成版本链

2.2、一致性视图

2.3、MVCC（多版本控制）基于版本链和一致性视图去实现不同隔离级别下数据读

不同隔离级别下的查询，基于创建的一致性视图中的m_ids列表和min_trx_id和undo日志的版本链进行比较，读取不同版本的数据返回给客户端

基于主键进行查询的多版本控制

• Read_unCommitted（读未提交）隔离级别：不创建一致性视图，读取版本链中最新的数据返回给客户端

• Read_Committed（读已提交）隔离级别：每次向数据库查询数据时，都会创建一致性视图，从版本链中，读取小于当前一致性视图中min_trx_id的，且不再m_ids列表中的事务id的版本数据，返回给客户端。

• Repeatable Read(重复读)隔离级别：开启查询事务，只创建一次一致性视图，从版本链中，读取该小于一致性视图中min_trx_id的，且不再m_ids列表中的事务id的版本数据，返回给客户端。

 

基于二级索引查询的多版本控制

二级索引页面的page Header部分有一个名为PAGE_MAX_TRX_ID的属性，该属性维护的是修改该页面的最大事务ID。

如果通过二级索引，进行查询的时候，会判断当前ReadView中的min_trx_id是否大于PAGE_MAX_TRX_ID，如果大于则代表，该页面的所有数据，对当前查询全部可见。如果小于PAGE_MAX_TRX_ID则进行回表操作，按主键查询的方式，判断数据的可见性。

 

 

 

二、锁

。InnoDB存储引擎既支持行级锁，也支持表级锁，但默认情况下采用行级锁。

1、锁等待和死锁

 

2、锁的分类

 

 

3、sql语句加锁分析

锁加在索引上

转载：https://blog.csdn.net/qq_43631716/article/details/117458900

InnoDB的行锁是通过给索引上的索引项加锁来实现的. 如果SQL语句未命中索引，则走聚簇索引的全表扫描，表上每条记录都会上锁，导致并发能力下降，增大死锁的概率，因此需要为表合理的添加索引，线上查询尽量命中索引。

1、加锁案例一

 

 2、加锁案例二

 

 

 

 

 

 

 

 

 

2、锁的兼容性

兼容性/互斥性	X	IX	S	IS
X	不兼容	不兼容	不兼容	不兼容
IX	不兼容	兼容	不兼容	兼容
S	不兼容	不兼容	兼容	兼容
IS	不兼容	兼容	兼容	兼容

、

3、解决事务并发问题的方案

4、行级别的锁

根据对数据上锁所锁定的范围不同，InnoDB行级锁种类又可以分为：
（1）单个行记录的记录锁（Record Locks）：锁定索引中一条记录，比如id=5的记录。
（2）区间锁（Gap Locks，又称间隙锁）：锁定一个范围。区间锁是锁定索引记录之间的区间，或锁定在第一个或最后一个索引记录之前的区间上。
（3）Next-key Lock：锁定一个范围的记录并包含记录本身（上面两者的结合）。Next-key Lock是记录锁与区间锁的组合，当InnoDB扫描索引记录时，会先对选中的索引记录加上记录锁，再对索引记录两边的区间加上区间锁。

 

重点1:【行级锁是通过索引上的索引项添加的】
未命中索引，所有扫描行，会被加锁。（InnoDB的行级锁是通过给索引上的索引项加锁来实现的，只有通过索引条件检索数据才使用，不通过索引条件查询的时候，InnoDB使用的是表级锁，而不是行级锁）

 

 

重点2:【区间锁 Lock-Grap】 

在MySQL的Repeatable-Read这个事务级别，为了避免幻读现象，引入了区间锁。它只锁定行记录数据的范围，不包含记录本身，不允许在此范围内插入任何数据。如图3-7所示，表t2有主键，score字段上有索引idc_score，在事务中查询t2表中score<80的记录，在上面加了一个共享锁（lock in share mode）。

 

 

 

 

 

 

 

 

5、加锁示意和锁结构

5.1、行锁加锁示意

5.2、锁结构

5.6、不同的语句在不同隔离级别下加锁情况

5.6.1、Select语句