MySQL-MVCC原理

原创已于 2022-03-24 19:36:29 修改 · 置顶 · 1.8k 阅读

9 ·

CC 4.0 BY-SA版权

文章标签：

#mysql #数据库 #sql

于 2022-01-29 23:17:04 首次发布

MySQL 专栏收录该内容

4 篇文章

订阅专栏

MySQL-MVCC

一、事务

简介

只有Innodb数据库引擎支持事务
事务处理维护数据库的完整性，保证成批的SQL语句要么全部执行，要么全部不执行
事务用来管理insert、update、delete语句

事务特性

一般来说，事务是必须满足4个条件（ACID）：原子性（Atomicity，或称不可分割性）、一致性（Consistency）、隔离性（Isolation，又称独立性）、持久性（Durability）。

原子性： 一个事务中的所有操作，要么全部完成，要么全部不完成，不会结束在中间的某个环节。事务在执行过程中发生错误，会被回滚到事务开始前的状态，就像这个事务从来没有执行过一样。
一致性： 在事务开始之前和事务结束以后，数据库的完整性没有被破坏。这表示写入的资料必须完全符合所有的预设规则，这包含资料的精确度、串联性以及后续数据库可以自发性地完成预定的工作。
隔离性： 数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。事务隔离分为不同级别，包括读未提交（Read uncommitted）、读提交（read committed）、可重复读（repeatable read）和串行化（Serializable）。
持久性： 事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失。

在 MySQL 命令行的默认设置下，事务都是自动提交的，即执行 SQL 语句后就会马上执行 COMMIT 操作。因此要显式地开启一个事务务须使用命令 BEGIN 或 START TRANSACTION，或者执行命令 SET AUTOCOMMIT=0，用来禁止使用当前会话的自动提交。*

事务可能出现的问题

脏读(Dirty Read):
脏读又称无效数据的读出，是指在数据库访问中，事务T1将某一值修改，然后事务T2读取该值，此后T1因为某种原因撤销对该值的修改，这就导致了T2所读取到的数据是无效的，值得注意的是，脏读一般是针对于update操作的
不可重复读(Non-repeatable read):

已知有两个事务A和B，A 多次读取同一数据，B
在A多次读取的过程中对数据作了修改并提交，导致A多次读取同一数据时，结果不一致.
幻读(Phantom Read):

已知有两个事务A和B，A从一个表中读取了数据，然后B在该表中插入了一些新数据，导致A再次读取同一个表,
就会多出几行，简单地说，一个事务中先后读取一个范围的记录，但每次读取的记录数不同，称之为幻象读。

不可重复读的和幻读很容易混淆，不可重复读侧重于修改，幻读侧重于新增或删除。解决不可重复读的问题只需锁住满足条件的行，解决幻读需要锁表。

事务问题的四种解决方案

Read uncommitted（读未提交）： 可能出现脏读、不可重复读和幻读。
Read committed（读提交）：可以避免脏读，但可能出现不可重复读和幻读。大多数数据库默认级别就是Read committed，比如Sql Server数据库和Oracle数据库。注意：该隔离级别在写数据时只会锁住相应的行。
Repeatable read（重复读）：可以避免脏读和不可重复读，但可能出现幻读。注意：①、事务隔离级别为可重复读时，如果检索条件有索引（包括主键索引）的时候，默认加锁方式是next-key锁；②、如果检索条件没有索引，更新数据时会锁住整张表。一个间隙被事务加了锁，其他事务是不能在这个间隙插入记录的，这样可以防止幻读。
Serializable（序列化）：可以避免脏读、不可重复读和幻读，但是并发性极低，一般很少使用。注意：该隔离级别在读写数据时会锁住整张表。

Oracle只提供Read commited 和Serializable 和自定义的Read only 级别，Mysql支持四种

事务控制语句：

BEGIN 或 START TRANSACTION 显式地开启一个事务；
COMMIT 也可以使用 COMMIT WORK，不过二者是等价的。COMMIT 会提交事务，并使已对数据库进行的所有修改成为永久性的；
ROLLBACK 也可以使用 ROLLBACK WORK，不过二者是等价的。回滚会结束用户的事务，并撤销正在进行的所有未提交的修改；
SAVEPOINT identifier，SAVEPOINT 允许在事务中创建一个保存点，一个事务中可以有多个 SAVEPOINT；
RELEASE SAVEPOINT identifier 删除一个事务的保存点，当没有指定的保存点时，执行该语句会抛出一个异常；
ROLLBACK TO identifier 把事务回滚到标记点；
SET TRANSACTION 用来设置事务的隔离级别。InnoDB 存储引擎提供事务的隔离级别有READ UNCOMMITTED、READ COMMITTED、REPEATABLE READ 和 SERIALIZABLE。

MYSQL 事务处理主要有两种方法：

1、用 BEGIN, ROLLBACK, COMMIT来实现

BEGIN 开始一个事务
ROLLBACK 事务回滚
COMMIT 事务确认

2、直接用 SET 来改变 MySQL 的自动提交模式:

SET AUTOCOMMIT=0 禁止自动提交
SET AUTOCOMMIT=1 开启自动提交

二、InnoDB锁

InnoDB隔离级别默认为(Repeatable Read)由于Innodb锁的特性，是解决了幻读的问题的。

行锁（共享锁与排它锁）

行锁也分为共享锁与排它锁。InnoDB的行锁是通过给索引上的索引项加锁来实现的。只有通过索引条件进行数据检索InnoDB才使用行级锁，否则InnoDB将使用表锁(锁住索引的所有记录)。

共享锁(Share Lock)

共享锁又称为读锁，是读取操作创建的锁。其他用户可以并发读取数据，但任何事务都不能对数据进行修改（获取数据上的排他锁），直到已释放所有共享锁。如果事务T对数据A加上共享锁后，则其他事务只能对A再加共享锁，不能加排它锁。获得共享锁的事务只能读数据，不能修改数据。

用途： 主要是用来确认记录是否存在，并确保没有人对这个记录进行是修改，如果当前事务需要对该记录进行修改则需要使用select ... for update获取排它锁，否则容易造成死循环！

语法：selcet ... lock in share mode

排它锁(exclusive lock)

排它锁又称为写锁，简称X锁，排它锁不能与其他锁并存，如一个事务获取了一个数据行的排它锁，其他事务就不能再获取该行的锁（共享锁、排它锁），只有该获取了排它锁的事务是可以对数据行进行读取和修改（其他事务要读取数据可来自快照）

语法：select ... for update(update,delete,insert自动加了排它锁)

为了运行表锁与行锁共同存在，实现多粒度机制，InnoDB还有两种内部使用的意向锁

意向锁-表锁机别

意向锁属于表锁级别。InnoDB自动加的，不需要用户干预。

意向共享锁（IS）	意向排它锁（IX）
事务打算给数据行加行共享锁，必须先取得该表的IS锁，意向共享锁之间是可以相互兼容的	事务打算给数据行加行排他锁，必须先取得该表的IX锁，意向排它锁之间是可以相互兼容的

意义： 当事务想去进行锁表时，可以先判断意向锁是否存在，存在时则可快速返回该表不能启用表锁。说白了就是这张表如果存在已经被行锁锁住了，就不能加表锁了。

表锁

InnoDB的行锁是通过给索引上的索引项加锁来实现的。只有通过索引条件进行数据检索InnoDB才使用行级锁，否则InnoDB将使用表锁(锁住索引的所有记录)。

也可以通过命令加锁 lock tables xx read/write;(和MyISAM设置表锁一样)

自增锁

针对自增列自增长的一个特殊的表级别锁，当当前的id自增后，但是在事务回滚后，这个id已经被使用过了，就会在表中出现id中间消失的状态。
可以通过命令查询 show variables like 'innodb_autoinc_lock_mode';
默认取值1 ，代表连续，事务未提交ID

InnoDB锁的实现方式

Next-key Lock 临键锁，InooDB行锁默认算法

当sql执行按照索引进行数据的检索时,查询条件为范围查找（between and、<、>等）并有数据命中则此时SQL语句加上的锁为Next-key locks，锁住索引的记录+区间。否则就是表锁哦。

临键锁也是解决了RR（Repeatable read）可重复读幻读的问题，原因是当前事务在这个区间做了查询就把当前区间给锁住了，其他事务就不能插入数据，也就不会出现幻读的情况了。

在这里插入图片描述

Gap Lock 间隙锁

当查询的记录不存在，临键锁就会退化为间隙锁。

在这里插入图片描述

Record Lock 记录锁

在这里插入图片描述

总结：

更新丢失：通过对当前修改线程加排他锁，其他线程就无法修改当前记录；
脏读：通过对修改记录的事务线程加排他锁实现，当前线程加了排他锁，其他线程就不能查询当前的记录；
不可重复读：多次查询的事务线程，对这个线程加上共享锁，这样其他线程想修改这条记录是无法修改的；
幻读：通过加上Next-key锁，对于多次查询的线程有了临检索就不会让新的数据插入到这个区间来。

死锁

多个并发事务

每个事务都持有锁（或已经在等待锁）

每个事务都需要再继续持有锁

事务之间产生加锁的循环等待，形成死锁

避免死锁

类似的业务逻辑以固定的顺序访问表和行（重点考虑）
大事务拆成小事务
同一个事务中，尽可能做到一次锁定锁需要的所有资源，减少死锁概率。
如果业务允许，降低隔离级别
为表添加合理的索引。如果不走索引会上升为表锁。

三、MVCC多版本并发控制

MVCC简介

MVCC（Multi-Version Concurrency Control）多版本并发控制，是用来在数据库中控制并发的方法，实现对数据库的并发访问用的。在MySQL中，MVCC只在读取已提交（Read Committed）和可重复读（Repeatable Read）两个事务级别下有效。其是通过Undo日志中的版本链和ReadView一致性视图来实现的。MVCC就是在多个事务同时存在时，SELECT语句找寻到具体是版本链上的哪个版本，然后在找到的版本上返回其中所记录的数据的过程。

版本链

最新记录+undolog

所有版本的数据都只会存一份，然后通过回滚指针连接起来，之后就是通过一定的规则找到具体是哪个版本上的数据就行了。

在这里插入图片描述

而具体版本链的比对规则如下，首先从版本链中拿出最上面第一个版本的事务ID开始逐个往下进行比对：

在这里插入图片描述

（其中min_id指向ReadView中未提交事务数组中的最小事务ID，而max_id指向ReadView中的已经创建的最大事务ID）

如果落在绿色区间（DB_TRX_ID < min_id）：这个版本比min_id还小（事务ID是从小往大顺序生成的），说明这个版本在SELECT之前就已经提交了，所以这个数据是可见的。若不在绿色区间且这个版本的事务本身就是当前SELECT语句所在事务（DB_TRX_ID==当前事务ID）的话，也是一样可见的；
如果落在红色区间（DB_TRX_ID > max_id）：表示这个版本是由将来启动的事务来生成的，当前还未开始，那么是不可见的；
如果落在黄色区间（min_id <= DB_TRX_ID <= max_id）：这个时候就需要再判断两种情况：
- 如果这个版本的事务ID在ReadView的未提交事务数组中，表示这个版本是由还未提交的事务生成的，那么就是不可见的；
- 如果这个版本的事务ID不在ReadView的未提交事务数组中，表示这个版本是已经提交了的事务生成的，那么是可见的。
如果在上述的判断中发现当前版本是不可见的，那么就继续从版本链中通过回滚指针拿取下一个版本来进行上述的判断。

ReadView

ReadView一致性视图主要是由两部分组成：所有未提交事务的ID数组和已经创建的最大事务ID组成（实际上ReadView还有其他的字段，但不影响这里对MVCC的讲解）。比如：[100,200],300。事务100和200是当前未提交的事务，而事务300是当前创建的最大事务（已经提交了）。当执行SELECT语句的时候会创建ReadView，但是在读取已提交和可重复读两个事务级别下，生成ReadView的策略是不一样的：读取已提交级别是每执行一次SELECT语句就会重新生成一份ReadView，而可重复读级别是只会在第一次SELECT语句执行的时候会生成一份，后续的SELECT语句会沿用之前生成的ReadView（即使后面有更新语句的话，也会继续沿用）。

原理 undolog + 版本链 + readview

MVCC解释： 并发访问（读或写）数据库时，对正在事务内处理的数据做多版本的管理。以达到用来避免写操作的堵塞，从而引发读操作的并发问题。（没有加锁的读-快照读）

InnoDB存储引擎在每行记录上存有三个默认字段：DB_TRX_ID、DB_ROLL_PTR、DB_ROW_ID;

DB_TRX_ID:记录创建的版本号
DB_ROLL_PTR:记录回滚的版本号
DB_ROW_ID:行自增的id

InnoDB在每行数据都增加三个隐藏字段，一个唯一行号，一个记录创建的版本号，一个记录回滚的版本号。

在这里插入图片描述

在多版本并发控制中，为了保证数据操作在多线程过程中，保证事务隔离的机制，降低锁竞争的压力，保证较高的并发量。在每开启一个事务时，会生成一个事务的版本号，被操作的数据会生成一条新的数据行（临时），但是在提交前对其他事务是不可见的，对于数据的更新（包括增删改）操作成功，会将这个版本号更新到数据的行中，事务提交成功，将新的版本号更新到此数据行中，这样保证了每个事务操作的数据，都是互不影响的，也不存在锁的问题。

在这里插入图片描述

MVCC插入流程
添加记录，设置DB_TRX_ID为当前事务ID，DB_ROLL_PTR为NULL
MVCC删除流程
将需要删除的记录的DB_ROLL_PTR设置为当前事务ID
MVCC更新流程
将原记录的DB_ROLL_PTR设置为当前事务ID，复制原记录生成新记录（此时同MVCC插入流程）
MVCC查询流程
1. 查询的数据行的DB_TRX_ID版本ID要小于当前事务ID
2. 查找数据行的DB_ROLL_PTR为NULL或大于当前事务版本号的记录，后面的事务删除的数据还未提交的仍可以找到。

四、Undo log

undo 意为取消，以撤销操作为目的，返回指定某个状态的操作

undo log 指事务开始之前，在操作任何数据之前，首先将需操作的数据备份到一个地方（Undo Log）

Undo Log 是为了实现事务的原子性而产物

事务处理过程中如果出现了错误或者用户执行了 ROLLBACK 语句,Mysql 可以利用Undo Log 中的备份将数据恢复到事务开始之前的状态。

UndoLog 在Mysql innodb 存储引擎中用来实现多版本并发控制

事务未提交之前，Undo 保存了未提交之前的版本数据，Undo中的数据可作为数据旧版本快照供其他并发事务进行快照读

在这里插入图片描述

快照读：
SQL 读取的数据是快照版本，也就是历史版本，普通的SELECT 就是快照读；innodb 快照读，数据的读取将由 cache( 原本数据) + undo( 事务修改前的数据) 两部分组成（即前面的MVCC和undo log组成）

当前读：
SQL 读取的数据是最新版本。通过锁机制来保证读取的数据无法通过其他事务进行修改；UPDATE 、DELETE 、INSERT 、SELECT … LOCK IN SHARE MODE 、SELECT … FOR UPDATE 都是当前读。