Mysql事务隔离原理MVCC\LBCC

最新推荐文章于 2024-07-08 19:28:21 发布

方猿

最新推荐文章于 2024-07-08 19:28:21 发布

阅读量392

点赞数

分类专栏：数据库 MySql 文章标签： mysql

本文链接：https://blog.csdn.net/renshengrushui/article/details/118312628

版权

数据库同时被 2 个专栏收录

4 篇文章 0 订阅

订阅专栏

MySql

2 篇文章 0 订阅

订阅专栏

前言：之前几篇文章讲解了事务的基本知识，事务四大特性ACID，事务隐患及事务隔离级别，以及Spring的事务传播行为。对于四种隔离级别只知概念，不清楚其原理。目前本人是个刚刚奋起的小菜鸟，只能先从网上搜查资料并整理记录。该篇文章多数转载，仅有小部分为个人拙见。仅作个人笔记使用。
原文地址：https://www.cnblogs.com/yelbosh/p/5813865.html

文章目录

一、总述

本文主要是针对MySQL/InnoDB的并发控制和加锁技术做一个比较深入的剖析，并且对其中涉及到的重要的概念，如多版本并发控制（MVCC），脏读（dirty read），幻读（phantom read），四种隔离级别（isolation level）等作详细的阐述，并且基于一个简单的例子，对MySQL的加锁进行了一个详细的分析。本文将围绕以下几个问题进行展开讲解。

什么是MVCC（多版本并发控制）？如何理解快照读（snapshot read）和当前读（current read）？
什么是隔离级别？脏读？幻读？InnoDB的四种隔离级别的含义是什么？
什么是死锁？
InnoDB是如何实现MVCC的？
一个简单的sql在不同场景下的加锁分析
一个复杂的sql的加锁分析

二、MVCC：Multi-Version Concurrent Control 多版本并发控制

2.1 为什么要有MVCC

从我们的直观理解上来看，要实现数据库的并发访问控制，最简单的做法就是加锁访问，即读的时候不能写（允许多个线程同时读，即共享锁，S锁），写的时候不能读（一次最多只能有一个线程对同一份数据进行写操作，即排它锁，X锁）。这样的加锁访问，其实并不算是真正的并发，或者说它只能实现并发的读，因为它最终实现的是读写串行化，这样就大大降低了数据库的读写性能。加锁访问其实就是和MVCC相对的LBCC，即基于锁的并发控制（Lock-Based Concurrent Control），是四种隔离级别中级别最高的Serialize隔离级别。为了提出比LBCC更优越的并发性能方法，MVCC便应运而生。

2.2 MVCC介绍

定义：MVCC是为了实现数据库的并发控制而设计的一种协议。协议！！！
几乎所有的RDBMS都支持MVCC。它的最大好处便是，读不加锁，读写不冲突。只是读不加锁。写时还是会加写锁的。

在MVCC中，读操作可以分成两类，快照读（Snapshot read）和当前读（current read）。快照读，读取的是记录的可见版本（可能是历史版本，即最新的数据可能正在被当前执行的事务并发修改），不会对返回的记录加锁；而当前读，读取的是记录的最新版本，并且会对返回的记录加锁，保证其他事务不会并发修改这条记录。

在MySQL InnoDB中，简单的select操作，如 select * from table where ? 都属于快照读；属于当前读的包含以下操作：

select * from table where ? lock in share mode; （加S锁）
select * from table where ? for update; （加X锁，下同）
insert, update, delete操作

针对一条当前读的SQL语句，InnoDB与MySQL Server的交互，是一条一条进行的，因此，加锁也是一条一条进行的。先对一条满足条件的记录加锁，返回给MySQL Server，做一些DML操作；然后再读取下一条加锁，直至读取完毕。需要注意的是，以上需要加X锁的都是当前读，而普通的select（除了for update）都是快照读，每次insert、update、delete之前都是会进行一次当前读的，这个时候会上锁，防止其他事务对某些行数据的修改，从而造成数据的不一致性。我们广义上说的幻读现象是通过MVCC解决的，意思是通过MVCC的快照读可以使得事务返回相同的数据集。如下图所示：

注意，我们一般说在MyISAM中使用表锁，因为MyISAM在修改数据记录的时候会将整个表锁起来；而InnoDB使用的是行锁，即我们以上所谈的MVCC的加锁问题。但是，并不是InnoDB引擎不会使用表锁，比如在alter table的时候，Innodb就会将该表用表锁锁起来。

三、隔离级别

在SQL的标准中，定义了四种隔离级别。每一种级别都规定了，在一个事务中所做的修改，哪些在事务内和事务间是可见的，哪些是不可见的。低级别的隔离可以执行更高级别的并发，性能好，但是会出现脏读和幻读的现象。首先，我们从两个基础的概念说起：

脏读（dirty read）：两个事务，一个事务读取到了另一个事务未提交的数据，这便是脏读。
幻读（phantom read）：两个事务，事务A与事务B，事务A在自己执行的过程中，执行了两次相同查询，第一次查询事务B未提交，第二次查询事务B已提交，从而造成两次查询结果不一样，这个其实被称为不可重复读；如果事务B是一个会影响查询结果的insert操作，则好像新多出来的行像幻觉一样，因此被称为幻读。其他事务的提交会影响在同一个事务中的重复查询结果。

下面简单描述一下SQL中定义的四种标准隔离级别：

READ UNCOMMITTED (未提交读) ：隔离级别：0. 可以读取未提交的记录。会出现脏读。
READ COMMITTED (提交读) ：隔离级别：1. 事务中只能看到已提交的修改。不可重复读，会出现幻读。（在InnoDB中，会加行所，但是不会加间隙锁）该隔离级别是大多数数据库系统的默认隔离级别，但是MySQL的则是RR。
REPEATABLE READ (可重复读) ：隔离级别：2. 在InnoDB中是这样的：RR隔离级别保证对读取到的记录加锁 (记录锁)，同时保证对读取的范围加锁，新的满足查询条件的记录不能够插入 (间隙锁)，因此不存在幻读现象。但是标准的RR只能保证在同一事务中多次读取同样记录的结果是一致的，而无法解决幻读问题。InnoDB的幻读解决是依靠MVCC的实现机制做到的。
SERIALIZABLE （可串行化）：隔离级别：3. 该隔离级别会在读取的每一行数据上都加上锁，退化为基于锁的并发控制，即LBCC。
　　需要注意的是，MVCC只在RC和RR两个隔离级别下工作，其他两个隔离级别都和MVCC不兼容。

四、死锁

4.1 Mysql锁种类

共享/排他锁（S/X锁）
- 共享锁（S Lock）：允许事务读取一行数据，多个事务可以拿到一把S锁（即读读并行）；
- 排他锁（X Lock）：允许事务删除或更新一行数据，多个事务有且只有一个事务可以拿到X锁（即写写/写读互斥）；
意向锁（Intention Lock）
意向锁是一种表级别的锁，意味着事务在更细的粒度上进行加锁。
- 意向共享锁（IS Lock）：事务想要获得一张表中某几行的共享锁；
- 意向排他锁（IX Lock）：事务想要获得一张表中某几行的排他锁；
- 举个例子，事务1在表1上加了S锁后，事务2想要更改某行记录，需要添加IX锁，由于不兼容，所以需要等待S锁释放；如果事务1在表1上加了IS锁，事务2添加的IX锁与IS锁兼容，就可以操作，这就实现了更细粒度的加锁。
插入意向锁（Insert Intention Lock）
插入意向锁是间隙锁的一种，专门针对insert操作的。
即多个事务在同一个索引、同一个范围区间内插入记录时，如果插入的位置不冲突，则不会阻塞彼此；
举个例子：在可重复读隔离级别下，对PK ID为10-20的数据进行操作：
事务1在10-20的记录中插入了一行：
insert into table value(11, xx)
事务2在10-20的记录中插入了一行：
insert into table value(12, xx)
由于两条插入的记录不冲突，所以会使用插入意向锁，且事务2不会被阻塞。
自增锁（Auto-inc Locks）
自增锁是一种特殊的表级别锁，专门针对事务插入AUTO-INCREMENT类型的列。
即一个事务正在往表中插入记录时，其他事务的插入必须等待，以便第1个事务插入的行得到的主键值是连续的。
举个例子：在可重复读隔离级别下，PK ID为自增主键
表中已有主键ID为1、2、3的3条记录。
事务1插入了一行：
insert into table value(‘aa’)
得到一条（4,’aa’）的记录，未提交；
此时
事务2中插入了一行：
insert into table value(‘bb’)
这时会被阻塞，即用到了插入意向锁的概念。
记录锁（Record Locks）- locks rec but not gap
记录锁是的单个行记录上的锁，会阻塞其他事务对其插入、更新、删除；
间隙锁(Gap Lock)
间隙锁锁定记录的一个间隔，但不包含记录本身。
举个例子：
假如数据库已有ID为1、6两条记录，
现在想要在ID in （4，10）之间更新数据的时候，会加上间隙锁，锁住[4,5] [7,10] ,(不包含已有记录ID=5本身)
那么在更新ID=5的记录（只有一条记录）符合条件；
如果不加间隙锁，事务2有可能会在4、10之间插入一条数据，这个时候事务1再去更新，发现在(4,10)这个区间内多出了一条“幻影”数据。
间隙锁就是防止其他事务在间隔中插入数据，以导致“不可重复读”。
临键锁（Next-Key Lock）= Gap Lock + Record Lock
临建锁是记录锁与间隙锁的组合，即：既包含索引记录，又包含索引区间，主要是为了解决幻读。

4.2 Mysql死锁定义

死锁是指两个或两个以上事务在执行过程中因争抢锁资源而造成的互相等待的现象。

注意：两个sql更新同一条数据不会造成死锁，更新时会有写锁，另一个会等待。只有多个事务间互相等待才会出现死锁。

第一个死锁很好理解，而第二个死锁，由于在主索引（聚簇索引表）上仍旧是对两条记录进行了不同顺序的加锁，因此仍旧会造成死锁。死锁的发生与否，并不在于事务中有多少条SQL语句，死锁的关键在于：两个(或以上)的Session加锁的顺序不一致。因此，我们通过分析加锁细节，可以判断所写的sql是否会发生死锁，同时发生死锁的时候，我们应该如何处理。

五、InnoDB的MVCC实现机制

MVCC可以认为是行级锁的一个变种，它可以在很多情况下避免加锁操作，因此开销更低。MVCC的实现大都实现了非阻塞的读操作，写操作也只锁定必要的行。InnoDB的MVCC实现，是通过保存数据在某个时间点的快照来实现的。一个事务，不管其执行多长时间，其内部看到的数据是一致的。也就是事务在执行的过程中不会相互影响。下面我们简述一下MVCC在InnoDB中的实现。
　　InnoDB的MVCC，通过在每行记录后面保存两个隐藏的列来实现：一个保存了行的创建时间，一个保存行的过期时间（删除时间），当然，这里的时间并不是时间戳，而是系统版本号，每开始一个新的事务，系统版本号就会递增。在RR隔离级别下，MVCC的操作如下：

select操作
- a. InnoDB只查找版本早于（包含等于）当前事务版本的数据行。可以确保事务读取的行，要么是事务开始前就已存在，或者事务自身插入或修改的记录。
- b. 行的删除版本要么未定义，要么大于当前事务版本号。可以确保事务读取的行，在事务开始之前未删除。
insert操作。将新插入的行保存当前版本号为行版本号。
delete操作。将删除的行保存当前版本号为删除标识。
update操作。变为insert和delete操作的组合，insert的行保存当前版本号为行版本号，delete则保存当前版本号到原来的行作为删除标识。

由于旧数据并不真正的删除，所以必须对这些数据进行清理，innodb会开启一个后台线程执行清理工作，具体的规则是将删除版本号小于当前系统版本的行删除，这个过程叫做purge。

其他

InnoDB使用自动行级锁定。即使在仅插入或删除单行的事务中，也可能会遇到死锁。这是因为这些操作并不是真正的“原子”操作；它们会自动对插入或删除的行的（可能是多个）索引记录设置锁定。
您可以使用以下技术来处理死锁并减少发生死锁的可能性：

使用 SHOW ENGINE INNODB STATUS命令以确定最近死锁的原因。这可以帮助您调整应用程序以避免死锁。
如果频繁出现死锁警告引，请通过启用innodb_print_all_deadlocks 配置选项来收集更广泛的调试信息。有关每个死锁的信息，而不仅仅是最新的死锁，都记录在MySQL error log 中。完成调试后，请禁用此选项。
如果事务由于死锁而失败，在任何时候，请重试一遍，死锁并不可怕。
请保持插入或更新事务足够小，避免锁被一个事务长时间占用，以此减少冲突概率。
进行一系列相关更改后立即提交事务，以减少冲突的发生。特别是，不要长时间未提交事务而使交互式 mysql会话保持打开状态。
如果您使用锁定读取（SELECT ... FOR UPDATE或 SELECT ... LOCK IN SHARE MODE），请尝试使用较低的隔离级别，例如 READ COMMITTED。
在事务中修改处于多个表或同一表中的不同行集时，每次都要以一致的顺序去执行这些操作。这样事务会形成定义明确的队列而不会导致死锁。例如，将数据库操作组织到应用程序内的函数中，而不是在不同位置编码多个类似的INSERT，UPDATE和DELETE语句序列。
对表中的数据建立合适索引，这样您的查询将会使用更少的索引记录，同样也会使用更少的锁。使用EXPLAIN SELECT以确定MySQL认为哪些索引最适合您的查询。
如果可以，请尽量少的使用锁，以允许 SELECT从一个旧的快照返回数据，不要添加条款FOR UPDATE或LOCK IN SHARE MODE给它。在READ COMMITTED这里使用隔离级别是件好事，因为同一事务中的每个一致性读取均从其自己的新快照读取。
如果没有其他办法，可以使用表级锁序列化事务。对事务表（例如InnoDB表）使用LOCK TABLES的正确方法是：SET autocommit = 0（not START TRANSACTION）后跟来开始事务，直到明确提交事务后才对LOCK TABLES调用 UNLOCK TABLES。例如，如果您需要写表 t1和从表中读取数据 t2，则可以执行以下操作：
```
SET autocommit=0;
LOCK TABLES t1 WRITE, t2 READ, ...;
... do something with tables t1 and t2 here ...
COMMIT;
UNLOCK TABLES;
```
表级锁可防止对表的并发更新，从而避免死锁，但代价是对繁忙系统的响应速度较慢。
序列化事务的另一种方法是创建一个仅包含一行的辅助“信号量”表。在访问其他表之前，让每个事务更新该行。这样，所有事务都以串行方式发生。注意，在这种情况下，InnoDB即时死锁检测算法也适用，因为序列化锁是行级锁。对于MySQL表级锁，必须使用超时方法来解决死锁。

详细讲解innodb实现mvcc过程：https://blog.csdn.net/fuzhongmin05/article/details/91351933
readview :https://blog.csdn.net/qq_37286668/article/details/111830990
https://zhuanlan.zhihu.com/p/166152616

方猿

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
Mysql事务隔离原理MVCC\LBCC

前言：之前几篇文章讲解了事务的基本知识，事务四大特性ACID，事务隐患及事务隔离级别，以及Spring的事务传播行为。对于四种隔离级别只知概念，不清楚其原理。目前本人是个刚刚奋起的小菜鸟，只能先从网上搜查资料并整理记录。该篇文章多数转载，仅有小部分为个人拙见。仅作个人笔记使用。原文地址：https://www.cnblogs.com/yelbosh/p/5813865.html文章目录一、总述二、MVCC：Multi-Version Concurrent Control 多版本并发控制2.1 为什.
复制链接

扫一扫