投稿
  1. 首页
  2. 随笔

MySQL事务隔离及MVCC原理

七秀技能 • 2022-12-17 • 随笔 • 阅读 12

MySQL事务隔离及MVCC原理,第1张

MySQL事务隔离及MVCC原理 MySQL事务隔离及原理 一、 事务（Transaction）及其ACID属性

事务是由一组SQL语句组成的逻辑处理单元，事务具有以下4个属性，通常简称为事务的ACID属性

  • 原子性（Atomicity）：事务是一个原子 *** 作单元，其对数据的修改，要么全都执行，要么全都不执行
  • 一致性（Consistent）：在事务开始和完成时，数据都必须保持一致状态。这意味着所有相关的数据规则都必须应用于事务的修改，以保持数据的完整性；事务结束时，所有的内部数据结构（包括B树索引或双向链表）也都必须是正确的。
  • 隔离性（Isolation）：数据库系统提供了一定的隔离机制，保证事务在不受外部并发影响的“独立”环境执行，这意味着事务处理过程中的中间状态对外部是不可见的，反之亦然。
  • 持久性（Durable）：事务完成之后，它对于数据的修改是永久性的，及时出现系统故障也能够保持。
二、 并发事务处理带来的问题

  • 脏读（Dirty Reads）：读取了其他并发事务未提交的数据

  • 不可重复读（Non-Repeatable Reads）：读取了其他并发事务提交的数据，针对update和delete

  • 幻读（Phanton Reads）：读取了其他并发事务提交的数据，针对insert

三、事务隔离级别

MySQL的事务隔离级别为可重复读，Oracle为读已提交

隔离级别越好，并发性越差

相关示例

对于不同的隔离级别，V1,V2,V3的值为多少

读未提交，V1 = 20 , V2 = 20 , V3 = 20

读已提交，V1 = 18 , V2 = 20 , V3 = 20

可重复读，V1 = 18 , V2 = 18 , V3 = 20

可序列化，V1 = 18 , V2 = 18 , V3 = 20

四、MySQL数据库如何实现事务隔离级别（RR）

  • LBCC （lock-based Concurrent Control）

  • MVCC （Multi-Version Concurrent Control）（针对LBCC的延伸）

MySQL两者公用

4.1 InnoDB Locking

下面的锁是针对一个数据不同纬度的划分，有重叠部分

  • Shared and Exclusive Locks 乐观锁、互斥锁
  • Intention Locks 意向锁
  • Record Locks 行锁
  • Gap Locks 间隙锁
  • Next -Key Locks Record Locks + Gap Locks
  • Insert Intention Locks 插入意向锁
  • AUTO-INC Locks自增锁
  • Predicate Locks for Spatial Indexes 空间索引预测锁

MyISAM 支持表锁

Innodb 支持表锁、行锁

4.2 MySQL InnoDB 基于锁的隔离级别方式

4.2.1 Lock Mode锁的模式 4.2.1.1 共享锁（行锁）-- Shared Locks

又名读锁，对某一资源加共享锁，自身可以读该资源，其他人也可以读该资源（也可以在继续加共享锁，即共享锁可多个共存），但无法修改。要想修改就必须等所有共享锁都释放完之后才能进行。

加锁 lock in share mode

select * from table lock in share mode;

释放锁：

commit;
rollback;

加读锁的意义是防止并发事务去修改该数据

4.2.1.2 排他锁（行锁）-- Exclusive Locks

对某一资源加排它锁，自身可以进行增删改查，其他人无法进行任何 *** 作

注意：排它锁不能与其他锁并存

加锁：

  • 自动：DML语句默认会加排它锁

  • 手动:select * from user where id = 1 for update;

释放锁：

commit;
rollback;
4.2.1.3 意向锁 --Intention Locks

意向共享锁 --Intention Shared Locks

表示事务准备给数据行加共享锁之前 ——数据行加共享锁的前提是获取此表的IS锁

意向排它锁 --Intention Exclusive Locks

表示事务准备给数据行加排他锁之前 ——数据行加排他锁的前提是获取此表的IX锁

注意：均为表锁，无法手动创建

意向锁不是用来锁定数据的，而是用来查看有没有已经锁定的数据，类似一个信号灯

为什么要使用意向锁

用来帮我们快速的判断当前表里面有没有已经锁定的数据，帮助我们在锁表 *** 作的时候，能够进行快速的判断。

举个例子：比如你想给每个员工都涨薪资，你首先得先对这张表进行锁定，再去修改数据。由于MySQL是行锁，你要给表加锁首先得确定该表中的每一行都没有锁，与其这样一行一行遍历判断该表有没有锁，不如再加行锁之前就加一个意向锁，需要对表加锁的时候直接判断该表是否有意向锁即可。

4.2.2 锁的算法 锁的区间

注意：锁的区间是根据数据库里的记录来进行划分的

测试数据

DROP TABLE IF EXISTS `test_transaction`;
CREATE TABLE `test_transaction`  (
  `id` int NOT NULL,
  `num` int NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;


INSERT INTO `test_transaction` VALUES (1, 1);
INSERT INTO `test_transaction` VALUES (5, 5);
INSERT INTO `test_transaction` VALUES (9, 9);
INSERT INTO `test_transaction` VALUES (11, 11);
4.2.2.1 Record Locks 记录锁

select * from table where id = 1 for update;

该语句锁定本条id = 1的记录

测试

开启两个事务，一个事务对id=1行的数据加锁，另一个事务更新改行数据

 
 begin;
 select * from test_transaction where id = 1 for update;
 
 
 begin;
 update test_transaction set num = 11 where id = 1;

可以看到另一个事务无法更新改行数据，被阻塞

4.2.2.2 Gap Locks 间隙锁

注意Gap Locks 只存在于RR隔离级别

select * from table where id > 5 and id < 9 for update;

会锁定（5,9）的开区间

测试

开启两个事务，一个事务对id > 5 and id < 9的数据加锁，另一个事务在该范围内插入数据

begin;
select * from test_transaction where id > 5 and id < 9 for update;


begin;
insert into test_transaction values(6,6);

可以看到事务2无法插入数据，被阻塞

思考下面语句会锁定哪里

select * from table where id > 15 for update;

测试

begin;
select * from test_transaction where id > 15 for update;


begin;
insert into test_transaction values(20,20);
insert into test_transaction values(13,13);
update test_transaction set num = 111 where id = 11;

通过上述测试结果可知，id为20和13都无法插入,可修改11

从而可以验证一个结论锁区间是根据数据库里的记录来划分的，由于数据库中的数据为1,5,9,11，所以id>15实际上锁定的是id > 11

4.2.2.3 Next -Key Locks --临建锁

select * from table where id > 5 and id < 11 for update

会锁定(5,9],(9,11]

测试

begin;
select * from test_transaction where id > 15 for update;


begin;
insert into test_transaction values(6,6);
insert into test_transaction values(8,8);

通过上述测试结果可知，id为6和8都无法插入

4.2.2.4 其他锁

  • 插入意向锁 Insert Intention Locks

  • 自增锁 AUTO-INC Locks

  • 空间锁 Predicate Locks for Spatial Indexes （基于R-Tree）

4.3 MVCC

在MySQL InnoDB存储引擎下RC,RR基于MVCC（多版本并发控制）进行并发事务控制。MVCC是基于“数据版本”对并发事务进行访问，指的是 “维持一个数据的多个版本，使得读写 *** 作没有冲突” 这么一个概念。仅仅是一个理想概念。用来解决读-写冲突的无锁并发控制。它实现有读写冲突时，做到不加锁，也能进行非阻塞并发读。MVCC是行锁的一个变种，但它在很多情况下，避免了加锁 *** 作，降低了开销

实现原理

它的实现原理主要是依赖记录中的 3个隐式字段，undo日志 ，Read View 来实现的。

隐式字段

每行记录除了我们自定义的字段外，还有数据库隐式定义的DB_TRX_ID,DB_ROLL_PTR,DB_ROW_ID等字段

  • DB_TRX_ID
    6byte，最近修改(修改/插入)事务ID：记录创建这条记录/最后一次修改该记录的事务ID
  • DB_ROLL_PTR
    7byte，回滚指针，指向这条记录的上一个版本（存储于rollback segment里）
  • DB_ROW_ID
    6byte，隐含的自增ID（隐藏主键），如果数据表没有主键，InnoDB会自动以DB_ROW_ID产生一个聚簇索引
  • 实际还有一个删除flag隐藏字段, 既记录被更新或删除并不代表真的删除，而是删除flag变了
undo日志

undo log主要分为两种：

  • insert undo log
    代表事务在insert新记录时产生的undo log, 只在事务回滚时需要，并且在事务提交后可以被立即丢弃
  • update undo log
    事务在进行update或delete时产生的undo log; 不仅在事务回滚时需要，在快照读时也需要；所以不能随便删除，只有在快速读或事务回滚不涉及该日志时，对应的日志才会被purge线程统一清除
purge
  • 从前面的分析可以看出，为了实现InnoDB的MVCC机制，更新或者删除 *** 作都只是设置一下老记录的deleted_bit，并不真正将过时的记录删除。
  • 为了节省磁盘空间，InnoDB有专门的purge线程来清理deleted_bit为true的记录。为了不影响MVCC的正常工作，purge线程自己也维护了一个read view（这个read view相当于系统中最老活跃事务的read view）;如果某个记录的deleted_bit为true，并且DB_TRX_ID相对于purge线程的read view可见，那么这条记录一定是可以被安全清除的。

MVCC控制方案的前提是基于UNDO_LOG版本链

UNDO_LOG版本链不是立即删除，MySQL确保版本链数据不再被引用后再进行删除

ReadView：

“快照读”SQL执行时MVCC提取数据的依据，主要是用来做可见性判断的, 即当我们某个事务执行快照读的时候，对该记录创建一个Read View读视图，把它比作条件用来判断当前事务能够看到哪个版本的数据，既可能是当前最新的数据，也有可能是该行记录的undo log里面的某个版本的数据。

快照读(使用MVCC)就是最普通的Select查询SQL语句，快照读的前提是隔离级别不是串行级别，串行级别下的快照读会退化成当前读，快照读的实现是基于多版本并发控制，即MVCC。既然是基于多版本，即快照读可能读到的并不一定是数据的最新版本，而有可能是之前的历史版本

当前读(Next Key locks：行锁+间隙锁)指代执行下列语句时进行数据读取的方式 insert、update、delete、select…for update、select …lock in share mode，为什么叫当前读？就是它读取的是记录的最新版本，读取时还要保证其他并发事务不能修改当前记录，会对读取的记录进行加锁

ReadView是一个数据结构，包含4个字段

字段含义m_ids当前活跃的事务编号集合min_trx_id（up_limit_id）最小活跃事务编号max_trx_id（low_limit_id）预分配事务编号，当前最大事务编号+1creator_trx_idReadView创建者的事务编号 版本链数据访问规则：

  • 判断当前事务id（trx_id）等于creator_trx_id？成立说明数据就是自己这个事务更改的，可以访问，否则进行下面的 *** 作

  • 判断trx_id < min_trx_id ? 成立说明数据已经提交，可以访问。否则进行下面的操作

  • 判断trx_id >= max_trx_id ? 成立说明该事务实在ReadView生成之后才开启，不允许访问

  • 判断min_trx_id <= trx_id < max_trx_id ,成立在m_ids数据中对比，不存在则代表数据已提交，可以访问，否则进行下面的操作

  • 读取UNDO_LOG版本链的之前版本数据进行比对，重复上述 *** 作直到读取数据。

读已提交（RC）：在每一次执行快照读时生成ReadView

可重复读（RR）：仅在第一次执行快照读时生成ReadView，后续快照读复用。（可能有例外）

连续多次快照读，ReadView会产生复用，没有问题

特例：当两次快照读之间存在当前读，ReadView会重新生成，导致产生幻读

案例 RR级别下 

Begin;
Select num from test_transaction where id = 1;
Select num from test_transaction where id = 1;
Commit;


Begin;
Select num from test_transaction where id = 1;
update test_transaction set num = Num + 1 where id = 1;
Select num from test_transaction where id = 1;
Commit;


update test_transaction set num = Num + 1 where id = 1;

*** 作1（事务A）

*** 作2（事务B）

*** 作3（事务C）

*** 作4（事务B）

*** 作5（事务A）

综上

我们可以看到Q1=3，Q2=1

解析

为什么Q1=3

首先进行update *** 作，应为当前读，读取最新版本数据也就是事务C新提交的数据，此时num = 2，加1之后变为3。之后select进行快照读，ReadView如下图所示，UNDO_LOG版本链为上图所示。首先读取最新版本1001的，发现等于creator_trx_id直接读取该数据为3.

为什么Q2=1

select进行快照读，ReadView如下图所示，UNDO_LOG版本链为上图所示。首先读取最新版本1001的，发现在up_limit_id和low_limit_id之间，同时m_ids存在该版本，所以该数据不能读。接着读取版本1002，发现大于等于low_limit_id，说明该事务实在ReadView生成之后才开启，不允许访问。然后读取版本999，该版本小于up_limit_id，可访问，所以读取该数据num = 1

欢迎分享，转载请注明来源：内存溢出

原文地址: https://outofmemory.cn/zaji/5684835.html

赞 (0)
打赏 微信扫一扫 微信扫一扫 支付宝扫一扫 支付宝扫一扫
七秀技能 七秀技能 一级用户组
0 0
上一篇 2022-12-17
下一篇 2022-12-17

发表评论

登录后才能评论

评论列表（0条）

保存