SQL数据库核心构成-原文

临键锁和间隙锁区别 ???

临键锁（Next-Key Lock）和间隙锁（Gap Lock）的主要区别在于锁定范围、锁定粒度、锁定效果、适用场景和锁定共存性。以下是详细介绍：

• 锁定范围。临键锁锁定的是索引记录本身以及后面的间隙；间隙锁仅锁定索引记录之间的间隙，不包括索引记录本身。
  • 临键锁：本身 + 后间隙
  • 间隙锁：记录之间的间隙
• 锁定粒度。临键锁是一种细粒度的锁定机制，每次锁定一个索引记录及其后面的间隙；间隙锁是一种粗粒度的锁定机制，每次锁定多个索引记录之间的间隙。
• 锁定效果。临键锁能保证在并发情况下，不会出现两个事务同时插入相同索引记录的情况；间隙锁能保证在并发情况下，不会出现两个事务同时插入相同索引记录之间的间隙的情况。
• 适用场景。临键锁适用于读操作和插入操作，可以有效避免脏读和不可重复读的问题；间隙锁适用于插入和删除操作，可以有效避免幻读的问题。
  • 临键锁：脏读 不可重复读
  • 间隙锁：幻读
• 锁定共存性。临键锁和间隙锁是一对互斥的锁，即同一个事务不能同时拥有临键锁和间隙锁，避免了死锁的可能性。

简而言之，临键锁旨在防止其他事务修改被锁定的记录，而间隙锁则旨在防止其他事务在被锁定的间隙内插入新的索引记录。

Next-key Lock锁（默认) 同时锁住数据+间隙锁 ???

聚簇索引和非聚簇索引

聚簇索引和非聚簇索引是数据库中两种不同类型的索引结构，它们在数据组织和访问方式上存在一些区别。

1. 数据组织方式：
   • 聚簇索引：聚簇索引的叶子节点存储行数据本身，而不仅仅是索引值。根据聚簇索引的排序顺序，表中的数据在物理上按照聚簇索引的顺序进行组织。因此，一个表只能有一个聚簇索引。
   • 非聚簇索引：非聚簇索引的叶子节点存储索引值和指向对应行的指针，而不包含行数据本身。数据在表中的存储顺序与非聚簇索引无关。
2. 访问方式：
   • 聚簇索引：由于聚簇索引中存储了行数据本身，因此通过聚簇索引可以直接定位到所需的行，这种方式被称为“聚簇索引搜索”。当需要检索大量连续数据时，聚簇索引能够提供较好的性能。
   • 非聚簇索引：对于非聚簇索引，首先会在索引中搜索对应的索引键，然后再根据指针找到对应的行数据。这种方式被称为“二次搜索”。非聚簇索引适用于需要快速定位到符合特定条件的行数据，而不需要检索大量连续数据的情况。
3. 存储空间：
   • 聚簇索引：聚簇索引的存储方式决定了表中的数据在物理上是按照索引顺序进行组织的，因此聚簇索引的存储空间相对来说更紧凑，占用的存储空间较少。
   • 非聚簇索引：由于非聚簇索引的叶子节点并不包含行数据本身，因此非聚簇索引通常需要额外的存储空间来存储索引值和指针。所以相对于聚簇索引，非聚簇索引占用的存储空间更多一些。

需要根据具体的场景和需求来选择使用聚簇索引还是非聚簇索引。聚簇索引适合需要按照特定顺序批量访问数据的查询操作，而非聚簇索引适合根据某列的值进行快速查找和检索的操作。

msyql设置自己的隔离级别

mysql设置自己的隔离级别

在MySQL中，设置事务的隔离级别可以使用SET TRANSACTION语句。隔离级别从低到高依次是：

1. READ UNCOMMITTED
2. READ COMMITTED
3. REPEATABLE READ
4. SERIALIZABLE

例如，要设置当前会话的隔离级别为REPEATABLE READ，可以使用以下SQL语句：

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
set transaction isolation level repeatable read;

如果你想要设置全局的默认隔离级别，可以使用以下SQL语句：

SET GLOBAL TRANSACTION ISOLATION LEVEL REPEATABLE READ;

请注意，设置全局隔离级别需要具有相应的权限。而且，设置全局隔离级别会影响所有新建立的连接和事务，但不会影响当前活跃的连接和事务。

mysql查看自己的隔离级别

在MySQL中，您可以通过以下SQL命令查看当前会话的事务隔离级别：

SELECT @@SESSION.TX_ISOLATION;

如果您想要查看系统默认的隔离级别，可以使用：

SELECT @@GLOBAL.TX_ISOLATION;

请注意，从MySQL 8.0开始，变量TX_ISOLATION已被弃用，新的变量名是TRANSACTION_ISOLATION。以下是查看当前会话的隔离级别的新命令：

SELECT @@SESSION.TRANSACTION_ISOLATION;

查看系统默认隔离级别的新命令：

SELECT @@GLOBAL.TRANSACTION_ISOLATION;
SELECT @@session.transaction_isolation;	--大小写都可以

-- 可重复读
REPEATABLE-READ

SQL数据库核心构成

mysql开启事务的3种方式

在 MySQL 中，开始一个事务有几种方式，主要取决于你使用的 MySQL 客户端或工具以及你的需求和偏好。

1. 使用 START TRANSACTION 或 BEGIN 命令： 最常见的方式是使用 START TRANSACTION 或其简写形式 BEGIN 来开始一个事务。如前面所述，在事务中执行一系列的 SQL 操作，然后通过 COMMIT 提交事务或者通过 ROLLBACK 回滚事务。

   START TRANSACTION;
   -- 或者使用 BEGIN;
   
   -- 在此执行一系列的 SQL 操作
   
   COMMIT; -- 提交事务
   -- 或者 ROLLBACK; 回滚事务
   

2. 使用 SET autocommit=0： 可以通过设置 autocommit 为 0 来手动禁用自动提交，从而开始一个事务。在这种模式下，每次执行一个语句都不会立即提交，直到你显式地执行 COMMIT 或 ROLLBACK。

   SET autocommit=0; -- 禁用自动提交，开始事务
   
   -- 在此执行一系列的 SQL 操作
   
   COMMIT; -- 提交事务
   -- 或者 ROLLBACK; 回滚事务
   

3. 使用 START TRANSACTION WITH CONSISTENT SNAPSHOT： 如果你需要使用一致性快照隔离级别，你可以使用 START TRANSACTION WITH CONSISTENT SNAPSHOT 来开始一个事务。这将使用当前数据库的一致性快照来启动事务。

   START TRANSACTION WITH CONSISTENT SNAPSHOT;
   
   -- 在此执行一系列的 SQL 操作
   
   COMMIT; -- 提交事务
   -- 或者 ROLLBACK; 回滚事务
   

这些是常见的几种开始事务的方式。选择哪种方式取决于你的需求和使用场景。

目录

一.SQL数据库的索引设计

1.B+树的结构

2.B+树的查找过程

1.单个查找ID=30的行数据

2.范围查找18<=ID<=49

3.B+树的特点优点

4.B+数3层大约可以存两千多万条数据

二.SQL数据库的事务与并发

1.事务自动提交模式

2.事务并发的四个问题

1)丢失更新

2)脏读

3)不可重复读

4)幻读

三.封锁

1.共享锁和排他锁

2.表锁

1)分类有三种

2)引擎默认用表锁

3)特点

4)什么时候用到表锁

3.行锁

1)分类

2)引擎默认用行锁

3)特点

4.页锁

5.如何上锁

四.三种级别的封锁协议

1. 一级封锁协议

2.二级封锁协议

3.三级封锁协议

4.两段锁协议(解决幻读问题)

5.可串行化调度(解决幻读问题)

五.事务的隔离级别编辑

1.读未提交(read uncommitted)

2.读已提交(read committed)

3.可重复读取(Repeatable Read)

4.可串行化(SERIALIZABLE)

编辑

六.MVCC

1.不属于mvcc的内容,但是需要知道的知识

2.必须知道的mvcc的特点

3.简单描述mvcc怎么做的

4.快照读

5.事务的ID

6.ReadView

7.快照

8.版本链

9.行隐藏的字段

10.mvcc实现RC和RR的唯一区别

11.寻找可读数据的规则

12.Undo log

13.记录行修改的具体流程

14.MVCC扩展细节

细节1:

细节2:

细节3:

细节4:delete行数据的删除

细节5:在可重复读的隔离级别下,mvcc是真的防止幻读了吗

1.快照读是怎么避免幻读的

2.当前读是怎么避免幻读的

3.幻读被彻底解决了吗？

七.mysql的innodb引擎是怎么实现四个隔离级别的

原文连接

————————————————

版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。

原文链接：https://blog.csdn.net/weixin_70280523/article/details/131262704

注意:

1.索引结构b+树,

2.事务,

3.并发的四个问题,

4.封锁和封锁协议,

5.事务的隔离级别,

6.mvcc,

以上6个数据库基础的知识是mysql和oracle共有的, 除了mvcc和事务的隔离级别的实现方式有不同之外,其他的都大致一样.

一.SQL数据库的索引设计

SQL数据库的索引结构有很多,下面介绍一种常用的索引结构–B+树

注意:mysql的索引默认是B+树结构,

oracle的索引结构可以选择B+树,也可以选其他结构.

1.B+树的结构：平衡多叉树

B+树,叫 平衡多叉树,由根节点,非叶子节点,叶子节点,组成

• 非叶子节点有多个
• 都是 从小到大 排序的

• 根节点: 只有一个
  非叶子节点:除了根结点和叶子节点都是非叶子节点
• 叶子节点:存的是行数据,
• 所有的叶子节点串起来组成双向链表.

2.B+树的查找过程

索引原本是和表一起在磁盘中的,但是为了查找效率,在用到表的时候,会把此表的索引(除了叶子节点层)先加载到内存.

先说索引是简单的主键id情况

1.单个查找ID=30的行数据

1)把索引(除了叶子节点层)加载到内存.

2).找到根节点大于等于15,小于56的区间,(在内存)

3).再找到非叶子节点大于等于20,小于49的区间,(在内存)

5).去磁盘中遍历[20,30]这个子叶节点,找到id=30这行的数据.(在磁盘)

2.范围查找18<=ID<=49

1)把索引(除了叶子节点层)加载到内存.

2).找到根节点大于等于15,小于56的区间,(在内存)

3).再找到非叶子节点大于等于15,小于20的区间,(在内存)

4).遍历[15,18]这个子叶节点,找到id=18这行的数据,(在磁盘)

5).从id=18这行数据开始顺着双向链表往后遍历查找,直到找到id=49这行数据的位置,然后把id=18和id=49和它俩之间的记录全部读出来.(在磁盘)

再说索引是字符串或者主键id和字符串组成联合索引的情况,这个我放到mysql专栏了.

3.B+树的特点优点

1.更少的查找次数

平衡树查找操作的时间复杂度等于树高 h，而树高大致为 O(h)=O(logdN)，其中 d 为每个节点的出度。

红黑树的出度为 2，而 B+ Tree 的出度一般都非常大，所以红黑树的树高 h 很明显比 B+ Tree 大非常多，检索的次数也就更多。

2.利用计算机预读特性

为了减少磁盘 I/O，磁盘往往不是严格按需读取，而是每次都会预读。预读过程中，磁盘进行顺序读取，顺序读取不需要进行磁盘寻道，并且只需要很短的旋转时间，因此速度会非常快。

操作系统一般将内存和磁盘分割成固态大小的块，每一块称为一页，内存与磁盘以页为单位交换数据。

数据库系统将索引的一个节点的大小设置为页的大小的倍数，使得一次 I/O 就能完全载入一个节点，并且可以利用预读特性，相邻的节点也能够被预先载入。

因此,以mysql为例,会把一个B+数节点的大小默认设为16KB.

4.B+数3层大约可以存两千多万条数据

对于 B+树而言，树的高度一般不超过 4 层，就 MySQL 的 InnoDB 存储引擎而言，一个结点默认的存储空间为 16Kb ( 可以通过这个命令查看SHOW GLOBAL STATUS like 'Innodb_page_size'; )，

得到如下参数：
变量名				变量值
Innodb_page_size	16384

MySQL 的 InnoDB 存储引擎的索引一般用 bigint 存储，占用 8 个 byte，

一个索引又会关联一个指向孩子结点的指针，这个指针占用 6 个 byte，

• 也就是说结点中的一个关键字大概要用 14 byte 的空间，而一个结点的默认大小为 16kb ，

那么一个结点可以存储关键的个数最多为 16384/14=1170 , 就相当于阶数 �=1170 ，

• 那么对于一颗高度为 3 的 B+树而言保守估计可以存储 1170×1170×16=21902400 个关键字，也就是两千多万条记录，

其中的 16 为假定每一个叶子结点包含的关键字的个数（由于包含 Data 指针，所以叶子结点可以容纳的关键字的个数会少一些），就这样我想你也看到了 B+树的强大了。3层的 B+树就可以存储两千多万的数据，牛逼不？

上面的B+数知识是oracle和mysql共用的,剩余的细节知识两者也是大差不差.下面B+树往下延伸的知识我放到oracle和mysql的文章专栏了.

二.SQL数据库的事务与并发

1.事务自动提交模式

MySQL 默认采用事务自动提交模式。也就是说，如果不显式使用START TRANSACTION语句来开始一个事务，那么一条单独的(查询,增加,删除,修改)的sql都会被当做一个事务自动提交。

不手动显示开启事务

手动显示开启事务

2.事务并发的四个问题

1)丢失更新

A 改–查 , B在A 的改–查 中间 改.

2)脏读：T1修改 又撤销

A 改–回滚 , B在A 的改–回滚 中间 读.

3)不可重复读：T2读，T1改了，T2在读

A 读–读 , B在A 的读–读 中间 改.

4)幻读：T1读，T2插入，T1在读（数量不对）

A count()读–count()读 , B在A 的count()读–count()读 中间 增.

上面四个问题是事务在并发情况下产生了不一致性问题,主要原因是破坏了事务的隔离性，

解决方法是通过并发控制来保证隔离性。并发控制可以通过封锁来实现，但是封锁操作需要用户自己控制，相当复杂。

因此,数据库管理系统提供了事务的隔离级别，让用户以一种更轻松的方式处理并发一致性问题

三.封锁

封锁协议是数据库自带的

是为了解决数据库的事务的并发出现的四种问题(丢失更新,脏读,不可重复读,幻读)

** 重要的图

锁从性质上来说可以分为两类:共享锁和排他锁

锁从封锁的颗粒度来说可以分为三类:表锁,行锁,页锁

需要知道的是,共享锁和排他锁只是锁的性质不同,它们加在表上就是表锁,加在行上就是行锁

1.共享锁和排他锁

共享锁(S)—就是读锁,得到数据的共享锁可以读这个数据.

排他锁(X)—就是写锁,得到数据的排他锁可以读和写这个数据.

锁的兼容关系如下:

一个事务拿到了共享锁(S),其他事务可以拿到共享锁(S),不能拿到排他锁(X).

一个事务拿到了排他锁(X),其他事务不能拿到共享锁(S),不能拿到排他锁(X).

2.表锁

1)分类有三种

1.共享锁(S):当表上有共享锁(S)时,其他的事务可以拿到表的共享锁(S),可以对表中的数据进行读操作,但是不能拿到表的排他锁(X),不能对表中数据进行修改.

2.排他锁(X):当表上有排他锁(X)时,其他的事务不能拿到表的共享锁(S)和排他锁(X).也就不能对表的数据进行读和写.

3.AUTO-INC Locks 锁:自增锁

Auto-Inc Lock 是一个特殊的表级锁，用于自增列插入数据时使用。 在插入一条数据的时候，需要在表上加个 Auto-Inc Lock，然后为自增列分配递增的值，在语句插入结束之后，再释放 Auto-Inc Lock。

在 MySQL 5.1.22 版本之后，又弄了个互斥量来进行自增减的累加。互斥量的性能高于 Auto-Inc Lock，因为 Auto-Inc Lock是语句插入完毕之后才释放锁，而互斥量是在语句插入的时候，获得递增值之后，就可以释放锁，所以性能更好。

但是我们还需要考虑主从的情况，由于并发插入的情况，基于 statement -based binlog 复制时，自增的值顺序无法把控，可能会导致主从数据不一致。

所以 MySQL 有个 innodb_autoinc_lock_mode 配置，一共有三个值：

0，只用 Auto-Inc Lock。
1，默认值，对于插入前已知插入行数的插入，用互斥量，对于插入前不知道具体插入数的插入，用 Auto-Inc Lock，这样即使基于 statement -based binlog 复制也是安全的。
2，只用互斥量。

2)引擎默认用表锁

Myisam

3)特点

1.对整张表加锁

2.开销小,加锁,解锁都需要性能开销

3.加锁快

4.无死锁

5.锁颗粒度大,发生锁冲突概率极高,并发性极低

4)什么时候用到表锁

平日的 update 、select 要用也是用行锁了，不可能用粒度粗的表锁。唯一能想到用上表锁的就是 DDL 语句了，比如 ALTER TABLE 的时候，应该锁定整个表，防止查询和修改，但是这个 server 已经提供了一个叫 MDL 的东西，即 Metadata Locks，所以已经用 MDL 来阻塞了，表锁也就排不上用场了。

真要用表锁，估计也就是数据恢复的时候，手动锁表还原数据了

3.行锁

1)分类

分类有下面几种:

共享锁(S),排他锁(X),

意向共享锁(IS)和意向排他锁(IX),

Record Lock锁(记录锁),

Gap Lock锁(范围锁,间隙锁),

Insert Intention Locks(插入意向锁)

next-key Lock锁(临键锁)

1.共享锁(S)和排他锁(X)

• 上面提过就不说了

2.意向共享锁(IS)和意向排他锁(IX)

使用意向锁(Intention Locks)可以更容易地支持多粒度封锁。

在存在行级锁和表级锁的情况下，事务 T 想要对表 A 加 X 锁，就需要先检测是否有其它事务对表 A 或者表 A 中的任意一行加了锁，那么就需要对表 A 的每一行都检测一次，这是非常耗时的。

意向锁在原来的 X/S 锁之上引入了 IX/IS，用来表示一个事务想要在表中的某个数据行上加 X 锁或 S 锁。有以下两个规定:

一个事务在获得某个数据行对象的 S 锁之前，必须先获得表的 IS 锁或者更强的锁；
一个事务在获得某个数据行对象的 X 锁之前，必须先获得表的 IX 锁。

通过引入意向锁，事务 T 想要对表 A 加 X 锁，只需要先检测是否有其它事务对表 A 加了 X/IX/S/IS 锁，如果加了就表示有其它事务正在使用这个表或者表中某一行的锁，因此事务 T 加 X 锁失败。

各种锁的兼容关系如下:

x 独占锁，加上后。其他所有的锁，都不兼容。

加了IS，意向共享锁，除了 独占锁，不兼容。

• 其他锁的等级更高，肯定兼容

加了意向独占锁IX，共享锁 和 独占锁 不兼容。

• 意向共享锁 和 意向独占锁兼容。
  • 即：意 独占 不影响 意向共享。
  • 意向独占 和 意向 独占也可兼容。因为还没有真正获取到锁

加了共享锁

• 意向共享锁 和 共享锁兼容

3. Record Lock锁(记录锁)

只锁住一行记录,叫记录锁,其实就是共享锁(S)和排他锁(X)只锁住了一行数据记录.

注意,我们说的锁一行数据记录,它的原理其实是去锁这行数据的索引.

下面以两条 SQL 的执行为例，讲解一下 InnoDB 对于单行数据的加锁原理。

update user set age = 10 where id = 49;
update user set age = 10 where name = 'Tom';

第一条 SQL 使用主键索引来查询，则只需要在 id = 49 这个主键索引上加上写锁；

第二条 SQL 则使用二级索引来查询，则首先在 name = Tom 这个索引上加写锁，然后由于使用 InnoDB 二级索引还需再次根据主键索引查询，所以还需要在 id = 49 这个主键索引上加写锁，如上图所示。

也就是说使用主键索引需要加一把锁，使用二级索引需要在二级索引和主键索引上各加一把锁。

• 二级索引的叶节点包含主键值
• 聚簇索引:叶节点既包含主健也有数据行

4.Gap Lock锁(范围锁,间隙锁)

锁住一个范围.比如

select * 
from stu 
where id>1 and id < 5 
for update,

锁住id>1和id<5(不包括1和5本身)这个区间,保证这个区间不会有其他数据插入.

• 这也解释了，如果 就算没有 2 3 4 都没有值，也会锁住这个区域

不同的间隙锁锁住的区域重叠了也不会冲突,因为间隙锁的唯一目的就是防止其他事务插入数据到间隙中 ，所以即使两个间隙锁要锁住相同的间隙也没有关系，因为它们的目的是一致的，所以不冲突。

• 锁住一次 2-4 和 2-4 都是一样的

5.插入意向锁(Insert Intention Locks)

它也是一类间隙锁，但是它不是锁定间隙，而是等待某个间隙。比如上面举例的 id = 4 的那个事务 C ，由于被间隙锁给阻塞了，所以事务 C 会生成一个插入意向锁，表明等待这个间隙锁的释放。

并且插入意向锁之间不会阻塞，因为它们的目的也是只等待这个间隙被释放，所以插入意向锁之间没有冲突。

插入意向锁并没有什么用,只是排个队等待别人释放间隙锁.

6.Next-key Lock锁 (临键锁)

相当于记录锁+范围锁,同时锁住记录本身+间隙,锁的是一个左开又闭的范围,

select * from stu where id>2 for update

比如select * from stu where id>2 for update ,锁住了表中 id 范围为 (2, +∞] (包括2)的范围,

• 应该是 不包括2

select * from stu where id>1 and id < 5 for update

比如select * from stu where id>1 and id < 5 for update ,锁住id>1和id<5(包括1和5本身)这个区间,保证这个区间不会有其他数据插入.

2)引擎默认用行锁 nnoDB

• 上面 应该学过

innoDB

3)特点
1.对行数据加锁

2.性能开销大

3.加锁慢

4.有几率出现死锁

5.锁颗粒度最小,发生锁冲突概率最低,并发度最高

4.页锁
开销,加锁时间和锁粒度介于表锁和行锁之间,会出现死锁,并发处理能力一般(此锁不做介绍)

5.如何上锁

说明:正确的应该是说成拿到表或行的锁, 而不是说成给表和行加锁.虽然两种说法最后的结果都是一样的.

下面讲的还有其他锁,太深奥了,看不懂,下面就不举例介绍了.

四.三种级别的封锁协议

1. 一级封锁协议：修改加排它锁，出现脏读 不可重复读

解决事务并发的(丢失更新)问题

一级封锁协议
●事务在修改数据之前必须加排他锁，直到事务结束才释放。数据不一致性——不可重复读

可以读，但是和前面的数据不一致

2.二级封锁协议：读之前加共享锁，读完释放。

解决事务并发的(丢失更新)和(脏读)问题

3.三级封锁协议：事务结束后才释放 （幻读存在）

解决事务并发的(丢失更新)和(脏读)和(不可重复读)问题

4.两段锁协议(解决幻读问题)

解决事务并发的(丢失更新)和(脏读)和(不可重复读)和(幻读)问题

两段锁协议是对三级封锁协议的优化,它在三级封锁协议的基础上,允许事务没有结束也可以释放锁.

加锁和解锁分为两个阶段进行。

就是事务运行分两个阶段

• 加锁阶段:事务执行开始,然后不断的加锁,但是途中不能释放锁.

• 解锁阶段:事务不断的释放锁,但是途中不能再加锁,直到事务结束.

可串行化调度是指，通过并发控制，使得并发执行的事务结果与某个串行执行的事务结果相同。

事务遵循两段锁协议是保证可串行化调度的充分条件。例如以下操作满足两段锁协议，它是可串行化调度。

lock-x(A)...
lock-s(B)...
lock-s(C)...

unlock(A)...
unlock(C)...
unlock(B)

但不是必要条件，例如以下操作不满足两段锁协议，但是它还是可串行化调度。

lock-x(A)...
unlock(A)...

lock-s(B)...
unlock(B)...

lock-s(C)...
unlock(C)

5.可串行化调度(解决幻读问题)

解决事务并发的(丢失更新)和(脏读)和(不可重复读)和(幻读)问题

什么是串行化,就是只有当A事务完全执行完毕,成功提交后,B事务才能执行,就是事务一个一个的执行.

可串行化调度就是允许在A事务执行的时候B事务也可以执行,但是要经过合理的调度,保证最终A事务和B事务的结果都是跟串行化一样是正确无误的.

百度解释:多个事务的并发执行是正确的，当且仅当其结果与按某一次序串行化执行这些事务时的结果相同。

优点:可串行化调度能保证并发的事务调度方式既能满足数据一致性需求，又能提高并发事务的执行效率。

五.事务的隔离级别

读未提交 提交读 重复读 串行化

1.读未提交(read uncommitted)

允许一个事务可以读取另一个未提交事务的数据.

只能解决(丢失更新)

2.读已提交(read committed)

允许一个事务可以读取另一个已经提交事务的数据

只能解决(丢失更新)和(脏读)问题

3.可重复读取(Repeatable Read)

在一个事务内部多次读取同一个数据对象,结果一致

只能解决(丢失更新)和(脏读)和(不可重复读)问题

4.可串行化(SERIALIZABLE)

强制事务串行执行,可以解决(丢失更新)和(脏读)和(不可重复读)和(幻读)问题。

**重要表格

数据库针对于事务的封锁协议	事务隔离级别	丢失更新	幻读	脏读	不可重复读
一级封锁协议	读未提交	×	√	√	√
二级封锁协议	读已提交	×	√	×	√
三级封锁协议	可重复读	×	√	×	×
两段锁协议+可串行化调度	可串行化	×	×	×	×

六.MVCC 多版本并发控制

MVCC是多版本并发控制 Multi-Version Concurrent Contrl。

Concurrent
adj.
并存的，同时发生的；同意的，一致的；（两个或两个以上徒刑判决）同时执行的；（三条或三条以上线）共点的，会合的
n.
共点；同时发生的事件

SQL数据库都有MVCC,只不过它们的实现方式略有不同,但是总体思路和下面讲的内容是一样的.

它是SQL数据库中的提高并发性能的一种方式，配合Undo log 和版本链，替代锁，去实现提交读 和 可重复读 这两种隔离级别。

• 提交读
• 可重复读

下面以mysql的InnoDB引擎的mvcc为例

读已提交 可重复读

MySQL Innodb存储引擎下，RC(读已提交和RR(可重复读)基于MVCC(多版本并发控制)进行并发事务控制;MVCC是基于“数据版本”对并发事务进行访问;

当前读 lock in share mode共享锁

读的都是当前最新的版本，会对当前读取的数据进行加锁，防止其他事务修改当前数据;是悲观锁的一种操作;以下语句: insert、update、delete、select…for update、selelct…lock inshare mode都是当前读(对当前数据加锁，防止其他事务修改)

快照读

最普通的不加锁的selelct查询语句;快照读的实现基于MVCC，读的不一定是最新数据，有可能是之前历史版本的数据

ReadView

是“快照读”SQL执行时MVCC提取数据的依据;本质是一个数据结构;

快照读 读试图 的4个参数

m ids:当前活跃的事务编号集合(开启但未提交的事务);
min trx id:最小活跃事务编号;小于它说明事务已经提交
max_trx_id:预分配事务编号，当前最大事务编号+1;大于它说明该事务是ReadView生成之后才开启，
creator_trx_id:ReadView创建者的事务编号;

ReadView

m ids={2.3.4},		开启还没有提交的事务为 2 3 4
min_trxid=2			最小的未提交的事务，为2 （小于2的都是提交的
max trx id=5		下次会被分配的事务编号（当前最大的+1）
creator trx id =4	创建者的事务编号

• 缩减为2个：只需要 m_ids:当前活跃的事务编号集合 也行
  • 未提交的 就是 m_ids的第一个
  • 下次将要分配的事务，就是 m_ids的最后一个 +1
• 创建者的事务编号 必须得有

版本链 UNDO_LOG

数据库中每行数据，除了肉眼可见的数据，还有几行隐藏字段;

db_trx_id:记录最近修改这条记录的事务id;
db_roll_pointer:回滚指针，指向这条记录的上一个版本;
db_row_id:隐含的自增ID(隐藏主键)，如果数据库没有主键，lnnodb会自动以db_row_id产生一个聚簇索引;

• 包含 原数据 （会使用原数据的聚簇索引 或 自己创建一个【没ID的情况下】）
• 事务ID。BD_ROW_ID 没关系
• 上个事务的指针

读视图：RC每次执行。RR第一次执行

RC读已提交 和 RR(可重复读)

不同隔离级别下，ReadView生成的时间不—样;
RC级别下，每次快照读都会生成一个ReadView;因此会出现“不可重复读”问题（事务A两次读取期间，事务B修改了数据，读取不一致);

RR级别下，仅在第一次执行快照读时生成ReadView,后面会延续使用(有特例: )

事务并发执行过程:RC级别下，每次快照读生成ReadView

• 直接看图吧

事务Atrx _id = 1
begin
update stu set name=张三"
where id=1088
commit

事务B trx id = 2
begin
update stu set name="张小三·
where id=1088
comnit

RC每次执行

ReadView

m ids={2.3.4},		开启还没有提交的事务为 2 3 4
min_trxid=2			最小的未提交的事务，为2 （小于2的都是提交的
max trx id=5		下次会被分配的事务编号（当前最大的+1）
creator trx id =4	创建者的事务编号


Readview
-------------
m_ids={3.4},		此时事务2已经提交。所以为3,4
min_trx_id=3		最小的为3
max_trx_id=5
creator trx id = 4

RR级别下，第一次快照

事务并发执行过程:RR级别下，只有第一次快照读生成ReadView

ReadView

m ids={2.3.4},		开启还没有提交的事务为 2 3 4
min_trxid=2			最小的未提交的事务，为2 （小于2的都是提交的
max trx id=5		下次会被分配的事务编号（当前最大的+1）
creator trx id =4	创建者的事务编号

哪怕第二次查询时，事务2 已经提交了，此时的快照 还是这样

版本链:更新数据的历史版本
当前数据行的最新数据,是已经修改了,可能提交了,也可能没提交,也可能准备要回滚,但是都可以作为当前数据行的最新数据，不管提没提交还是准备回滚,对mvcc的判断没有任何影响

id	姓名 都存一份

事务ID，为3
前一个 版本链条为：123

基于 UNDO_LOG 版本联，一直追踪到：TRX_ID 和 DB_ROLL_PTR都为null

pointer	指针

undo	撤销
v.
解开，松开；消除……影响或结果，使恢复原状；撤销（先前的计算机指令）；破坏，毁掉

版本链数据访问规则:
判断当前事务id等于creator_trx_id(4)吗?成立说明数据就是自己这个事务更改的，可以访问

判断trx id < min trx id(2)?成立说明数据已经提交了，可以访问

判断trx_id>=max_trx_id(5)?成立说明该事务是在ReadView生成以后才开启，不允许访问

判断min trx id(2)<= trx_id < max_trx_id(5)，成立在m_ids数据中对比，不存数据在的则代表数据是已提交的，可以访问。

因此根据ReadView可以读取到1号事务提交的数据，张三
	一直追踪到 最小的事务之前的 一个已提交的事务，这里为1，张三

快照读去读取删除和新增的数据

此图为MCVV用快照读去读取删除和新增的数据,修改在上一个图里

版本链:更新数据的历史版本
当前数据行的最新数据,是已经修改了,可能提交了,也可能没提交,也可能准备要回滚,但是都可以作为当前数据行的最新数据，不管提没提交还是准备回滚,对mvcc的判断没有任何影响

m ids={2.3.4},		开启还没有提交的事务为 2 3 4
min_trxid=2			最小的未提交的事务，为2 （小于2的都是提交的
max trx id=5		下次会被分配的事务编号（当前最大的+1）
creator trx id =4	创建者的事务编号


快照读结果,根据规则,会读出TRX_ID=1的数据,
	即：读到 事务2 的上一个 事务，为1，1是已提交的

ReadView 02
-----------------
m_ids=(3.4) 
min trx_id=3
max_trx_id=5
creator trx id -4

快照读结果,根据规则计算得出,读出的数据是TRX_ID=2的数据,但是由于此行数据的删除字节位已被标记为delete,因此查出的结果会为空.

ReadView	03
------------------
m_ids={4}
min_trx_id=4
max_trx_id=5
creator trx id =4

快照读结果,根据规则计算的出,读出的数据是TRX_ID=3的数据.

1.必须知道的mvcc的特点

1.mysql和oracle都默认开启使用了mvcc.是自带的, 这个关不掉,不用也得用.

2.mysql和oracle两者实现mvcc的方式虽然有点差别,但是核心设计方式都是一样的.

3.mvcc是不加锁的,所有加锁的都不是mvcc的内容.

4.mvcc就一个作用,就是只能 实现快照读的事务 并发隔离

5.因为只有(脏读/不可重复读)是快照读的问题,所以mvcc只能解决这两种问题.所以mvcc只能实现RC和RR这两种隔离级别.

2.简单描述mvcc怎么做的

每次select操作会访问数据库中的版本链记录，其他事务可以修改这条记录，而select根据当前隔离级别去版本链中找到对应的版本记录，实现了读-写，写-读的并发执行，提升了系统的性能。

• 读 已提交的情况下：
  • 因为每次都生成 快照读视图，只需要找到 最小的 版本链 记录-1即可
• RR级别下，只有第一次快照读生成ReadView
  • 以后再 查询，都是复用这个 试图的

3.当前读

当前读: 特殊的读操作，插入/更新/删除操作，属于当前读，需要加锁。

select * from table where ? lock in share mode;
select * from table where ? for update;
insert into table values (…);
update table set ? where ?;
delete from table where ?;

-- 所有以上的语句，都属于当前读，读取记录的最新版本。并且，读取之后，还需要保证其他并发事务不能修改当前记录，对读取记录加锁。

-- 其中，除了第一条语句，对读取记录加共享锁外，其他的操作，都加的是排它锁。

4.快照读

简单的select操作，属于快照读，不加锁。 不加读锁 读历史版本

5.事务的ID

事务的id是数据库自动给事务的.是一个自增数字,每开启一个新事物,这个数字就+1,然后给这个新的事务,当做这个新事务的id.

6.ReadView 4个参数

RC级别下,事务每次快照读都会自动生成一个ReadView.

RR级别下,事务只在第一次快照读自动生成一个ReadView,后面再快照读会复用此ReadView.

快照读的一种形式，MySQL执行MVCC提取数据的依据；本质是一个数据结构，其中包括几下几个部分：

1. m_ids：当前活跃的事务ID集合（开启但未提交的事务ID）；
2. min_trx_ids:最小活跃事务ID，小于它说明事务已经提交；
3. max_trx_ids:预分配的事务ID（下一个事务开启分配的事务ID），即当前最大事务ID+1；大于它说明该事物是ReadView生成之后才开启；
4. creator_trx_id:ReadView创建者的事务编号；

7.快照

一条行数据当被修改(insert,update,delete)时,都会把原来的数据记录下来生成一个快照,记录到undo log 日志的版本链中.

8.版本链

MVCC 将使用到的版本快照存储在 Undo 日志中，该日志通过回滚指针(db_roll_pointer)把一个数据行(Record)的所有版本快照连接起来,称为版本链。

9.行隐藏的字段 db_row_id

MySQL数据库中，每条数据除肉眼可见的数据之外，还有几个隐藏字段：
1.db_trx_id:表示最近一次对本记录行作修改（insert | update）的事务ID。至于delete操作，InnoDB认为是一个update操作，不过会更新一个另外的删除位，将行表示为deleted。并非真正删除。

2.db_roll_pointer:回滚指针，指向指向这条数据的上一个版本记录；

3.db_row_id:随着新行插入而单调递增的行ID。理解：当表没有主键或唯一非空索引时**，innodb就会使用这个行ID自动产生聚簇索引**。如果表有主键或唯一非空索引，聚簇索引就不会包含这个行ID了。这个DB_ROW_ID跟MVCC没关系。

10.mvcc实现RC和RR的唯一区别

实际上，RC和RR两个隔离级别，实现的主要区别在于ReadView的时机不一样，RC隔离级别下，每次快照读都会生成一个ReadView；

而RR级别下，只有在第一次快照读时生成ReadView，后面会延续使用；事务根据生成的ReadView去版本链中寻找自己能够访问的数据，最后得到所读的数据；

11.寻找可读数据的规则

事务是如何根据生成的ReadView去版本链中寻找自己能够访问的数据的
判断规则如下：

1. 当前记录修改操作事务ID 等于 ReadView创建事务ID ? 等于说明是自身事务修改的数据，可以访问；

2. 判断 trx_id < min_trx_id(2) ? 如果小于说明事务提交了，可以访问；

3. 判断trx_id >= max_trx_id(5) ? 成立说明该事物是在ReadView生成之后才开启的，不允许访问，不成立继续向下判断；

4. 判断min_trx_id <= trx_id < max_trx_id,成立则在m_ids中对比，如果不存在说明事务已经提交，可以访问；

5. 不满足以上条件，根据回滚指针依次往前一个版本数据查找，直到找到满足条件的数据；

12.Undo log

Undo log中存储的是老版本数据。
大多数对数据的变更操作包括 insert/update/delete，在InnoDB里，undo log分为如下两类：
①insert undo log : 事务对insert新记录时产生的undo log, 只在事务回滚时需要, 并且在事务提交后就可以立即丢弃。
②update undo log : 事务对记录进行delete和update操作时产生的undo log，不仅在事务回滚时需要，快照读也需要，只有当数据库所使用的快照中不涉及该日志记录，对应的回滚日志才会被purge线程删除。

如下图:事务A的改动的这行数据的版本快照记录在undo log 中,现在事务回滚用不到它,而且也没有readview用到它了,因此这个事物A的版本快照就会被purge线程删除.

Purge线程

为了实现InnoDB的MVCC机制，删除操作都只是设置一下旧记录的deleted_bit，并不真正将旧记录删除。
为了节省磁盘空间，InnoDB有专门的purge线程来清理deleted_bit为true的记录。purge线程自己也维护了一个read view，如果某个记录的deleted_bit为true，并且DB_TRX_ID相对于purge线程的read view可见，那么这条记录一定是可以被安全清除的。

Purge
v.
清洗（组织中的异己分子）；清除，排除（不愉快的情况或感觉）；删除（不需要的东西） ；催泻，通便；<法律>弥补，消除（蔑视法庭罪）；（尤指由于患有贪食症）使吐出吃下的食物，催吐

n.
（对异己的）清洗，排除；泻药

13.记录行修改的具体流程

注意:(删除,新增,修改)都被看做是修改

假设有一条记录行如下，字段有Name和Honor，值分别为"curry"和"mvp"，最新修改这条记录的事务ID为1。

（1）现在事务A（事务ID为2）对该记录的Honor做出了修改，将Honor改为"fmvp"：

①事务A先对该行加排它锁

②然后把该行数据拷贝到undo log中，作为旧版本

③拷贝完毕后，修改该行的Honor为"fmvp"，并且修改DB_TRX_ID为2（事务A的ID）, 回滚指针指向拷贝到undo log的旧版本。（然后还会将修改后的最新数据写入redo log）

④事务提交，释放排他锁

    （2） 接着事务B（事务ID为3）修改同一个记录行，将Name修改为"iguodala"：

①事务B先对该行加排它锁

②然后把该行数据拷贝到undo log中，作为旧版本

③拷贝完毕后，修改该行Name为"iguodala"，并且修改DB_TRX_ID为3（事务B的ID）, 回滚指针指向拷贝到undo log最新的旧版本。

④事务提交，释放排他锁

14.MVCC扩展细节

细节1: 记录了数据的逻辑变化

一行数据有很多版本快照,但是只有最新版的快照(事务已经成功提交的)是真实存在的,之前的版本快照都不是真实存在的,用到的话是从最新版的快照根据undo log的操作记录倒推出来的,也就是说undo log主要记录了数据的逻辑变化，比如一条INSERT语句，对应一条DELETE的undo log，对于每个UPDATE语句，对应一条相反的UPDATE的undo log，这样在发生错误时，就能回滚到事务之前的数据状态。

细节2:数据的历史记录数据存在undo log日志

mysql的事务已提交的一行的数据存在磁盘中, 而这行数据的历史记录数据存在undo log日志中

细节3:事务没有提交之前 对外不可见

我通过代码测试验证得知,事务没有提交之前,它对行数据所做的任何增删改都是对外不可见的,都是在事务自己的事务空间里.

细节4:delete行数据的删除

先说结论:不会物理删除,而是会把这行数据的删除位标记为delete.

* delete删除一行数据A,发生的4个过程

1.复制一份这行数据A存进undo log日志中,以便后面回滚或者恢复数据.

2.在这行数据A的删除位标记为delete.

3.有一个垃圾链表,专门记录被删除数据的空间位置,在垃圾链表中记录这行数据A的空间位置,把这行数据A占用的空间标记为可重用空间,

4.之后有新插入行数据的时候,比如,子叶节点有4个数据,id分别是3,5,6,9,那么这个时候你把id=5的这行数据A删除了,插入进来一个id=4的,那么就会把这行数据A进行物理删除,把空间腾出来给新插入的id=4的行数据.

为什么数据库要这么设计：B+树 平衡

因为如果直接物理删除的话,那么这行数据的空间就被空出来了**,那么,数据库的索引结构B+数强大的平衡算法检测到这个空间没有数据后,为了数的平衡性,就会把其他的数据往这个空间移动,那么就会造成整个索引结构的变化,会产生大量的磁盘IO,这是很耗费时间并且影响性能的.**

** 细节5:在可重复读的隔离级别下,mvcc是真的防止幻读了吗

先说结论:

在可重复读的隔离级别下,mvcc只能很大程度上避免幻读,但是不能完全解决幻读.

下面说的都是在在可重复读的隔离级别下的.

1.快照读是怎么避免幻读的
mvcc使用Read View+undo log解决了快照读的幻读问题.

2.当前读是怎么避免幻读的
我们假设select … for update当前读是不会加锁的（实际上是会加锁的），在做一遍实验。

这时候，事务 B 插入的记录，就会被事务 A 的第二条查询语句查询到（因为是当前读），这样就会出现前后两次查询的结果集合不一样，这就出现了幻读。

所以，Innodb 引擎为了解决「可重复读」隔离级别使用「当前读」而造成的幻读问题，就引出了Next-key Lock锁 (临键锁=记录锁+间隙锁)。（额外提一句，读提交隔离级别，是没有临键锁的，只有记录锁）

• 真实测试 确实是 事务B被卡死，因为上面第一个查询不能加 for update

假设，表中有一个范围 id 为（3，5）临键锁，那么其他事务就无法插入 id = 4 这条记录了，这样就有效的防止幻读现象的发生。

比如select * from stu where id>2 for update ,锁住了表中 id 范围为 (2, +∞] (包括2)的范围

举个具体例子，场景如下：

事务 A 执行了这条当前读语句后，就在对表中的记录加上 id 范围为 (2, +∞] 的 next-key lock（next-key lock 是间隙锁+记录锁的组合）。

然后，事务 B 在执行插入语句的时候，判断到插入的位置被事务 A 加了 next-key lock，于是事物 B 会生成一个插入意向锁，同时进入等待状态，直到事务 A 提交了事务。这就避免了由于事务 B 插入新记录而导致事务 A 发生幻读的现象。

3.幻读被彻底解决了吗？查更查 中间插入

可重复读隔离级别下虽然很大程度上避免了幻读，但是还是没有能完全解决幻读。

我举例两个可重复读隔离级别发生幻读现象的场景。

第一个发生幻读现象的场景

在可重复读隔离级别下，事务 A 第一次执行普通的 select 语句时生成了一个 ReadView，之后事务 B 向表中新插入了一条 id = 5 的记录并提交。

接着，事务 A 对 id = 5 这条记录进行了更新操作，

在这个时刻，这条新记录的 db_trx_id 隐藏列的值就变成了事务 A 的事务 id，之后事务 A 再使用普通 select 语句去查询这条记录时就可以看到这条记录了，于是就发生了幻读。

第二个发生幻读现象的场景：两查 后加锁查

除了上面这一种场景会发生幻读现象之外，还有下面这个场景也会发生幻读现象。

T1 时刻：事务 A 先执行「快照读语句」：select * from t_test where id > 100 得到了 3 条记录。

T2 时刻：事务 B 往插入一个 id= 200 的记录并提交；

T3 时刻：事务 A 再执行「当前读语句」 select * from t_test where id > 100 for update 就会得到 4 条记录，此时也发生了幻读现象。

要避免这类特殊场景下发生幻读的现象的话，就是尽量在开启事务之后，马上执行 select … for update 这类当前读的语句，因为它会对记录加 next-key lock，从而避免其他事务插入一条新记录。

再说一下结论:MySQL 可重复读隔离级别并没有彻底解决幻读，只是很大程度上避免了幻读现象的发生。

七.mysql的innodb引擎是怎么实现四个隔离级别的

1.未提交读

innoDB采用一级封锁协议,就是通过给增删改sql加写锁(普通select不加锁),事务结束才释放写锁,来实现未提交读.

2.提交读

innoDB通过一级封锁协议+MVCC来实现提交读.

3.可重复读(mysql默认的隔离级别)

innoDB通过一级封锁协议+MVCC来实现提交读.

3.可串行化

先说明:innoDB通过一级封锁协议+MVCC+临键锁 很大程度上防止了幻读,相当于很大程度上可以保证可串行化,但是并不能100%保证串行化.

说法一:innoDB通过两段锁协议和可串行化调度来实现事务的串行化执行.

说法二:一个事务开始直接给表加锁,等这个事物完成后,另一个事物才能对表的数据进行操作,这样就实现了绝对的串行化.

其实可串行化是一个实现目标,是多方努力的结果,并不一定说是只加表锁,或者只加行锁,是多方协同结合着去实现可串行化的.

扩展:

1.一条普通的select有没有事务

1.mysql给每条sql都加了一个隐形事务,然后自动帮你提交.因此select也是有事务的,也会被分配一个事务id

2.但是由于select并不会改变数据,因此,select一执行完同时就瞬间commit提交事务了

2.一条普通的select有没有加锁

1.在mysql前三个隔离级别的实现下,普通的select都是什么锁都没加的,都是快照读.