MySQL学习

木瓜盖
103 阅读25分钟

转载： javaguide.cn/home.html

xiaolincoding.com/mysql/lock/…

MYSQL基础架构

image.png

MySQL主要由下面几部分构成

  • 连接器：身份认证和权限相关（登录MySQL的时候）
  • 查询缓存： 执行查询语句的时候，会先查询缓存（MySQL8.0版本后移除，功能不实用）
  • 分析器： 没有命中缓存的话，SQL语句就会经过分析器，分析器说白了就是要先看你的SQL语句要干嘛，再检查你的SQL语句语法是否正确
  • 优化器： 按照MySQL认为最优的方案去执行
  • 执行器： 执行语句，然后从存储引擎返回数据。执行语句之前会先判断是否有权限，如果没有权限的话，就会报错
  • 插件式存储引擎： 主要负责数据的存储和读取，采用的是插件式架构，支持InnoDB、MyISAM、Memory等多种存储引擎。在 MYSQL 5.5.5之前，MyISAM是MySQL的默认存储引擎。5.5.5版本之后，InnoDB是MySQL的默认存储引擎

执行一条select语句，期间发生了什么？

连接器

  • 服务器与客户端进行TCP三次握手建立连接
  • 校验客户端的用户名和密码
  • 检验正确，读取用户权限，后面的权限逻辑判断基于此时读到的权限

查询缓存

执行查询语句的时候，会先查询缓存（MySQL8.0版本后移除，功能不实用，当数据有更新的时候，缓存就会被删除）

解析SQL

  • 词法分析，根据输入的字符串识别关键字，构件SQL语法树
  • 语法分析，根据语法规则判断输入的SQL语句是否满足SQL语法

执行SQL

  • 预处理阶段： 检查SQL语句中的表或者字段是否存在，将select 中的符号，扩展为表上的所有列
  • 优化器： 为SQL语句制定执行计划，比如选择索引。
  • 执行器： 优化器确定了执行方案，Mysql开始执行语句，从存储引擎读取记录，返回给客户端。执行器分为三种方式执行过程分别是主键索引查询、全表扫描、索引下推， SQL语句的执行过程总结：
  1. 权限校验
  2. 查询缓存（8.0版本后被删除）
  3. 分析器进行词法分析和语法分析
  4. 优化器选择最优的方案执行语句
  5. 执行器执行语句

update语句具体执行流程

update users A set a.username='张三' where a.id= 1

  • 查询出id为1的这行数据，如果id=1这一行所在的数据也本来就在buffer pool中，就直接返回给执行器更新，如果不在buffer pool，从磁盘中读出数据页到buffer pool，返回记录给执行器。
  • 执行器得到聚簇索引记录后，会看一下更新前的记录和更新后的记录是否一样。如果一样就直接返回，如果不一样就会把更新前的记录和更新后的记录都当作参数传给InnoDB层，让InnoDB执行更新记录。
  • 调用InnoDB引擎API开启事务，InnoDB首先记录相应的undo log，然后将该条记录的username改为张三并将修改写入redo log中，redo log进入prepare状态等待bin log的写入。bin log写入完成提示redo log可以提交。
  • 将redo log 的状态从prepare状态转向commit状态
  • 更新完成
    两阶段提交防止redo log和bin log的写入不同步导致数据不一致
    update语句和select语句执行流程最主要的区别就是update语句会涉及到undo log、redo log、binlog

Mysql三大日志

  • undo log（回滚日志）：是Innodb存储引擎层生成的日志，实现了事务中的原子性，主要用于事务回滚和MVCC
  • redo log（重做日志）：是Innodb存储引擎层生成的日志，实现了事务中的持久性，主要用于故障恢复
  • binlog（归档日志）： 是Server层生成的日志，主要用于数据备份和主从复制。

undo log

作用

  • 实现事务回滚，保障事务的原子性。事务处理过程出现错误或者用户执行了ROLLBACK语句，MySQL可以利用undo log中的历史数据将数据恢复到事务开始之前的状态。
  • 实现MVCC（多版本并发控制）MVCC是通过ReadView+undo log 实现的。undo log为每条记录保存多份历史数据，MySQL在执行快照读的时候，会根据事务的Read View 里的信息，顺着undo log的版本链找到满足其可见性的记录。

Buffer Pool

作用： 将查询记录缓存，当下次查询语句命中缓存，就不需要从磁盘获取数据，提高数据库的读写性能。

  • 当读取数据时，如果数据存在于Buffer Pool中，就不需要从磁盘读取数据
  • 当修改数据时，如果数据存在于Buffer Pool中，那直接修改Buffer Pool中数据所在的页，然后将其页设置为脏页，为了减少磁盘I/O，不会立即将修改写入磁盘，后续由后台线程选择一个合适的时机异步将修改写入到磁盘。

Buffer Pool和查询缓存的区别

Buffer Pool是位于存储引擎层的缓存而查询缓存是位于Server层的缓存

redo log

当我们断电重启，Buffer Pool是基于内存的，那么还没来得及落盘的脏页数据就会丢失。为了防止该问题，当有一条记录需要更新的时候，InnoDB引擎就会先更新内存，然后将本次修改以redo log的形式记录下来。后续，InnoDB引擎会在适当时候，由后台线程将缓存在Buffer Pool的脏页刷新到磁盘里。这就是WAL技术。
redo log是物理日志，记录了某个数据也做了什么修改，每当执行一个事务就会产生这样的一条或多条日志。
在事务提交时，只要先将redo log持久化到磁盘即可，可以不需要立即将缓存在Buffer Pool里的脏页数据持久化到磁盘。
当系统崩溃时，虽然脏页数据没有持久化，但是redo log已经持久化。MySQL重启可以根据redo log恢复数据。redo log保证了事务四大特性中的持久性

redo log 和undo log 的区别？

  • redo log记录了此次事务完成后的数据状态
  • undo log记录了此次事务开始前的数据状态

redo log同样需要持久化，为什么我们要多此一举使用redo log？

redo log虽然同样需要持久化，但是相比于持久化数据的效率大大提高。因为写入redo log的方式使用了追加操作，所以磁盘操作是顺序写，但是写入数据的操作是随机写。 顺序写的效率会更高。

redo log的作用

  • 实现事务的持久性
  • 将写操作从随机写变成了顺序写，提升了MySQL写入磁盘的性能。
    需要注意的是，redo log也不是直接写入磁盘的，rdo log也有自己的缓存，每当产生一条redo log，会先写入到redo log buffer，后续再持久化到磁盘。我们可以根据实际情况选择我们合适的持久化的时间。

redo log文件写满了怎么办？

redo log是以循环写的方式工作的，当文件写满了，就会回到开头重新写。

image.png

  • 红色部分表示记录新的更新操作
  • 蓝色部分表示待落盘的脏数据页记录 如果write pos追上了checkpoint，就意味着redo log文件满了，这时MySQL不能再执行新的更新操作，也就是说MySQL会被阻塞（针对并发量大的系统，适当设置redo log的文件大小很重要），此时会停下来将Buffer Pool中的脏页刷新到磁盘中，然后标记redo log哪些记录可以被擦除，接着对旧的redo log记录进行擦除，等擦除完旧记录腾出了空间，checkpoint就会往后移动，然后MySQL恢复正常运行。

binlog

为什么需要binlog？

MySQL在完成一条更新操作后，Server层还会生成一条binlog，等之后事务提交的时候，会将该事务执行过程中产生的所有binlog统一写入binlog文件。binlog文件是记录了所有数据库表结构变更和表数据修改的日志，不会记录查询类的操作。

binlog和redo log比较

bin log日志只能用于归档，而redo log可用于故障恢复。 binglog和redo log区别：

  • 使用对象不同：binlog是Server层实现的日志，redo log是Innodb存储引擎实现的日志
  • 文件格式不同：binlog是逻辑日志，redo log是物理日志
  • 写入方式不同： binlog是追加写，redo log是循环写
  • 用途不同： bin log 用于备份恢复、主从复制，redo log用于掉电等故障恢复

主从复制

image.png
主从复制过程：

  • 写入bin log：主库写binlog日志，提交事务，并更新本地存储数据
  • 同步bin log： 从库会创建一个专门的I/O线程，连接主库的log dump线程，把binlog信息写入relay log的中继日志里，再返回给主库复制成功的响应。
  • 回放bin log：回放binlog，并更新存储引擎中的数据 完成主从复制之后，我们就可以实现读写分离，这样即使写请求会锁表或者锁记录，也不会影响读请求的执行。

从库是不是越多越好？

不是，因为从库数量增加，从库连接上来的I/O线程也比较多，主库也要创建同样多的log dump线程来处理复制的请求，对主库资源消耗比较高。一般情况2~3个从库就足够了

主从复制的模型

  • 同步复制：MySQL主库提交事务的线程要等待所有从库的复制成功响应，才返回客户端结果。保证了数据一致，但是性能很差，同时主库和从库任一数据库出问题都会使数据库无法正常读取。
  • 异步复制： MySQL主库提交事务的线程并不会等待binlog同步到各从库，就返回客户端结果，缺点：一旦主库宕机，数据就会发生丢失。
  • 半同步复制： 事务线程不用等待所有的从库复制成功响应，只要一部分复制成功响应回来就行，比如一主二从的集群，只要数据成功复制到任意一个从库即可返回。这种方式兼顾了异步复制和同步复制的优点，即使出现主库宕机，至少还有一个从库有最新的数据，不存在数据丢失的风险。

bin log刷盘时机

事务执行过程中，先把日志写到binlog cache，事务提交的时候，再把binlog cache写到binlog 文件中。一个事务的binlog是不能被拆开的，必须同时提交或刷盘。因为如果分开，备库执行就会把binlog分成多个事务执行，破坏了从库的事务原子性。

image.png
在事务提交的时候，执行器把binlog cache里的完整事务写入到binlog文件中，并清空binlog cache。

  • 图中的 write，指的就是把日志写入到 binlog 文件，但是并没有把数据持久化到磁盘，因为数据还缓存在文件系统的 page cache 里，write 的写入速度还是比较快的，因为不涉及磁盘 I/O。
  • 图中的 fsync，才是将数据持久化到磁盘的操作，这里就会涉及磁盘 I/O，所以频繁的 fsync 会导致磁盘的 I/O 升高。

两阶段提交

两阶段提交是分布式事务一致性协议，可以保证多个逻辑操作要么全部成功，要么全部失败。 作用：防止redo log 和bin log的逻辑不一致

两阶段提交过程

image.png

  • prepare 阶段：将XID（内部XA事务的ID）写入redo log，同时将redo log对应的事务状态设置为prepare，然后将redo log持久化到磁盘（不同的持久化方式可能会有差异）
  • commit阶段： 将XID写入到binlog，并将binlog持久化到磁盘，最后再将redo log的事务状态设置为commit。 两阶段提交是以 binlog 写成功为事务提交成功的标识，因为 binlog 写成功了，就意味着能在 binlog 中查找到与 redo log 相同的 XID

两阶段提交的问题

  • 磁盘I/O次数高，对于“双1”配置，每个事务提交都会进行两次 fsync（刷盘），一次是 redo log 刷盘，另一次是 binlog 刷盘。
  • 锁竞争激烈：两阶段提交虽然能够保证单事务两个日志的内容一致，但在多事务的情况下，却不能保证两者的提交顺序一致，因此，在两阶段提交的流程基础上，还需要加一个锁来保证提交的原子性。从而保证多事务的情况下，两个日志的提交顺序一致。

组提交

当有多个事务提交的时候，会将多个 binlog 刷盘操作合并成一个，从而减少磁盘 I/O 的次数，这就是组提交机制。 组提交会把commit阶段分为三个过程

  • flush 阶段：多个事务按进入的顺序将 binlog 从 cache 写入文件（不刷盘）；
  • sync 阶段：对 binlog 文件做 fsync 操作（多个事务的 binlog 合并一次刷盘）；
  • commit 阶段：各个事务按顺序做 InnoDB commit 操作； 上面的每个阶段都有一个队列，每个阶段有锁进行保护，因此保证了事务写入的顺序，第一个进入队列的事务会成为 leader，leader领导所在队列的所有事务，全权负责整队的操作，完成后通知队内其他事务操作结束。对每个阶段引入了队列后，锁就只针对每个队列进行保护，不再锁住提交事务的整个过程，可以看的出来，锁粒度减小了，这样就使得多个阶段可以并发执行，从而提升效率。

MySQL存储引擎架构

MySQL存储引擎采用的是插件式架构，支持多种存储引擎，我们甚至可以为不同的数据库表设置不同的存储引擎以适应不同场景的需要。存储引擎是基于表的，而不是数据库。我们也可以通过实现MySQL的存储引擎的标准接口来编写一个属于自己的存储引擎。

MyISAM和InnoDB的区别是什么？

  • 是否支持行级锁： MyISAM只有表级锁，而InnoDB支持行级锁和表级锁，默认为行级锁
  • 是否支持事务： MyISAM不提供事务支持。 InnoDB提供事务支持，实现了SQL标准定义的四个隔离级别，具有提交和回滚事务的能力。InnoDB默认使用的可重复读隔离级别是可以解决幻读问题发生的
  • 是否支持外键： MyISAM不支持，而InnoDB支持
  • 是否支持数据库异常崩溃后的安全恢复： MyISAM不支持，而InnoDB支持
  • 是否支持MVCC： MyISAM不支持，而InnoDB支持
  • 索引实现不一样： 虽然MyISAM引擎和InnoDB引擎都是使用B+Tree作为索引结构，但是两者的实现方式不太一样。InnoDB引擎中，其数据文件本身就是索引文件。相比MyISAM，索引文件和数据文件是分离的，其表数据文件本身就是按B+Tree组织的一个索引结构，树的叶节点data域保存了完整的数据记录。
  • InnoDB 的数据是按「页」为单位来读写的，在表中数据量大的时候，为某个索引分配空间的时候就不再按照页为单位分配了，而是按照区（extent）为单位分配。

索引

索引底层结构

哈希表

通过键(key)即可快速取出对应的值(value)，因此哈希表可以快速检索数据（接近 O（1）），通过哈希散列算法我们可以快速找到key对应的index。 链地址法解决哈希冲突问题：多个不同的 key 最后得到的 index 相同，冲突的key会放在链表中。

为什么 MySQL 没有使用哈希作为索引的数据结构呢？

哈希表的数据是随机存放的，在查询顺序范围数据或对数据进行排序的效率不高。

B树和B+树的异同

  • B+Tree 相比于 B 树和二叉树来说，最大的优势在于平均查询效率很高，因为即使在数据量很大的情况，查询一个数据的磁盘 I/O 依然维持在 3-4次。而B树的检索效率比较不稳定。
  • B树的所有节点既存放数据也存放KEY，而B+树只有叶子节点存放KEY和数据，其他节点只存放KEY。
  • B树的叶子节点都是独立的，B+树叶子节点之间有一条引用链指向相邻的叶子节点
  • B树的检索过程对查询范围内的每个节点的关键字做二分查找，可能还没有到叶子节点就结束了。而B+树的检索效率较稳定，任何查找都是从根节点到叶子节点，检索顺序也很明显,更适合 MySQL 中常见的基于范围的顺序查找。

InnoDB和MyISAM索引B+树的区别

MyISAM索引文件和表数据文件分离，B+树的叶子节点的data域是数据记录的地址，在索引检索的时候，首先按照搜索算法找出索引的KEY的位置，取出数据记录的地址，再根据地址找出相应的数据 这被称为“非聚簇索引（非聚集索引） ”。 InnoDB索引文件本身就是表数据文件，索引检索可以根据索引的KEY直接找出数据，而这个KEY本身就是主键。因此 InnoDB 表数据文件本身就是主索引。这被称为“聚簇索引（聚集索引） ”。 其余的索引都为辅助索引，辅助索引的叶子节点是主键的值，因此辅助索引需要先取出主键的值再走一遍主索引。所以不推荐主键设计过长或者非递增的字段。会导致主索引频繁分裂

索引类型

一级索引：主键索引
二级索引：唯一索引、普通索引、联合索引、覆盖索引、前缀索引、全文索引

聚簇索引

定义：索引结构和数据一起存放的索引，InnoDB的主索引就是聚簇索引
优点： 查询效率高，对范围查询和排序查找优化
缺点： 依赖于顺序数据，更新代价大

非聚簇索引

定义：索引结构和数据分开存放的索引，MyIsAM的主索引和InnoDB的辅助索引就是非聚簇索引
优点：更新代价小 缺点：依赖于有序数据，可能会二次查询（回表）

最左前缀匹配原则

在使用联合索引时，MySQL会根据联合索引的字段顺序，从左到右依次去匹配，如果查询条件中存在与联合索引最左侧的字段相匹配的字段，则会使用该字段过滤一批数据，直至联合索引中全部索引匹配完成，或者在执行过程中遇到范围查询 如> < != between 以及以%开头的模糊查询，才会停止匹配

使用索引的建议

选择合适的字段创建索引

  • 不为NULL的字段：索引字段的数据应该尽量不为NULL
  • 被频繁查询的字段
  • 被作为条件查询的字段
  • 被频繁用作连接的字段
  • 频繁需要排序的字段
  • update语句的where条件建立索引，否则update语句将会全表扫描并对表里的索引项都加锁，相当于锁住了整张表。

被频繁更新的字段应该慎重建立索引

尽可能地考虑建立联合索引而不是单列索引

考虑在字符串类型的字段上使用前缀索引代替普通索引

索引失效的情况

  • 使用select *查询
  • 建立了联合索引，但查询条件未遵循最左匹配原则
  • 在索引列上进行计算、函数、类型转换等操作
  • 以%开头的LIKE查询
  • 查询条件中使用or，且or的前后条件有一个列没有使用索引
  • 隐式转换

事务

事务的特性

  • 原子性： 一个事务中的所有操作要么全部完成，要么全部不完成。例子：一个转账事务，顾客付了钱后出现错误购买失败了。那么商品仍然在商家中，顾客的钱也不会扣除。
  • 一致性： 事务操作前和操作后，数据满足完整性约束。例子： 转账操作，A用户拥有1000元，B用户也拥有1000元。A用户给B用户转账500元，转账后A用户为500元，B用户为1500元。总和仍为2000元，不会出现总和为1500元或者2500元的情况。
  • 隔离性： 数据库允许多个并发事务同时对其数据进行读写和修改的能力。隔离性可以防止多个事务并发执行导致数据的不一致。例如消费者购买商品这个事务是不影响其他消费者购买的。
  • 持久性： 事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失。

并发事务会引发什么问题？

脏读： 如果一个事务读到了另一个未提交事务修改的数据，就是脏读。
不可重复读： 在一个事务内多次读取同一个数据，如果出现前后两次读到的数据不一样的情况，就是不可重复读。
幻读： 在一个事务内多次查询某个符合查询条件的记录数量，出现前后两次查询到的记录数量不一样的情况，就发生了幻读现象。 例子：A事务第一次查询符合条件的记录是5条。此时B事务插入了一条新数据。A事务第二次查询符合条件的记录是6条，那么就出现了幻读现象。

事务的隔离级别

  • 读未提交：指一个事务还没提交时，它做的变更就能被其他事务看到。
  • 读已提交： 指一个事务提交之后，它做的变更才能被其他事务看到。
  • 可重复读： 一个事务执行过程中看到的数据，一直跟这个事务启动时看到的数据时是一样的。MySQL InnoDB引擎的默认隔离级别
  • 串行化： 对记录加上读写锁，在多个事务对这条记录进行读写操作时，如果发生了读写冲突，后访问的事务必须等前一个事务执行完成，才能继续执行。
    针对不同的隔离级别，并发事务时可能发生的现象也会不同。

image.png

InnoDB MVCC

主要通过 行的隐藏字段、Read View（快照）、undo log来实现。隐藏字段保存了最后修改该行的事务ID，在开启事务的时候 InnoDB会创建一个新的快照。 该快照维护了一个当前活跃的事务ID列表，即该事务对其他事务的修改不可见的列表。
1.如果查询的记录的事务ID<活跃事务ID列表的最小ID或者等于当前的事务ID即代表该条记录是可见的。
2.如果查询的记录>活跃事务ID列表，表示该条记录对本次事务是不可见的。根据该行的PTR指针指向的undo log取出旧版本的快照记录重新跳到第一步执行
3.如果查询的记录的事务ID在活跃事务ID列表之间，就要对活跃事务ID列表进行二分查找。如果可以找到，表明该条记录在创建快照前被修改未提交或者在创建快照后被修改。则表示该条记录是不可见的，我们需要根据该行的PTR指针指向的undo log取出的旧版本快照重新跳到第一步执行。如果找不到，表明该条记录在创建快照的时候已经被提交，则表示该条记录是可见的。

MVCC如何实现读已提交和可重复读

读已提交：MVCC在读已提交的情况下会在事务开启后每次查询数据都会生成一个快照。例子：事务A开启事务，进行第一次查询得到X=1，事务B开启事务将X修改为2，并提交。之后A事务再次查询会生成一个新的快照， 此时快照中的活跃事务ID列表中并不存在B事务，即B事务对该条记录的修改对于A事务是可见的。所以A事务第二次查询可以看见B事务修改后的数据即X=2. 可重复读：而在可重复读的情况下只在事务开启后第一次查询数据生成一个快照。 例子：事务A开启事务，进行第一次查询得到X=1，事务B开启事务将X修改为2，并提交。之后A事务再次查询仍然使用旧的快照，此时快照中的活跃事务ID列表中仍存在B事务，即B事务对该条记录的修改对于A事务是不可见的。所以A事务第二次查询结果仍然为X=1 所以读已提交和可重读的区别在于创建Read View的实际不同。

什么是当前读、快照读？

快照读：普通的select语句，执行方式是生成 readview，直接利用MVCC机制来读取，并不会对记录进行加锁。所以快照读读到的数据不一定是最新的数据 当前读：会对当前读取的数据进行加锁，并且是悲观锁。当前读会读到数据库中最新的版本数据。
例子：

  • select … lock in share mode 当前读，加读锁 ，也叫共享锁
  • select … for update 当前读，加写锁，又叫排他锁
  • innoDB 里面 update (排他锁)、insert (排他锁)、delete (排他锁)，都会自动给涉及的语句添加写锁。
  • 串行化事务的隔离级别

InnoDB如何在可重复读下防止幻读

快照读的情况：通过MVCC防止幻读。因此可重复读的情况下只在事务开启后第一次查询数据生成一个快照。新的事务插入数据对于该事务来说是不可见的。
当前读的情况：通过行锁+间隙锁实现防止幻读。在执行当前语句的时候会加上next-key lock（行锁+间隙锁）如果有其他事务在next-key lock锁范围内插入一条记录，那么这个插入语句会被阻塞。

MySQL 锁

全局锁

作用： 使得整个数据库处于只读状态，主要应用于全库逻辑备份。
缺点： 加上全局锁后，业务只能读数据，而不能更新数据，造成业务停滞。
当我们使用InnoDB引擎默认的事务隔离级别可重复读可以在备份数据库时更新表数据。因为MVCC和快照读的支持，我们只需要备份快照版本数据即可，不需要加上全局锁。但是MyISAM这种不支持事务的引擎，在备份数据库时就要使用全局锁。

表级锁

表锁

表锁会将整张表锁住，限制所有线程的对该表的读写操作。在InnoDB引擎中，我们应避免使用表锁，因为会影响并发性能，我们应使用InnoDB的行级锁。

元数据锁

元数据锁是为了保证当用户对表执行CRUD操作时，防止其他线程对这个表结构做了变更。

  • 对一张表进行CRUD操作时，加的是MDL读锁
  • 对一张表做结构变更操作的时候，加的是MDL写锁 MDL是在事务提交后才会释放。当我们有一个长事务一直未提交，那么此时对表结构做变更操作，就会导致线程阻塞。所以我们在更改表结构前，一定要先将事务提交。

意向锁

  • 在使用InnoDB引擎的表里对某些记录加上共享锁之前，需要在表级别加上一个意向共享锁
  • 在使用InnoDB引擎的表里对某些记录加上独占锁之前，需要现在表级别加上一个意向独占锁
    作用：快速判断表里是否有记录被加锁

AUTO-INC 锁

作用： 通过AUTO-INC锁实现给表主键的自动递增
AUTO-INC锁是特殊的表锁机制，锁不是在一个事务提交后释放，而是在执行完插入语句后就会立即释放。
在插入数据时，会加一个标记别的AUTO-INC锁，然后为被AUTO_INCREMENT修饰的字段赋值递增的值，插入语句执行完成，就会把AUTO-INC锁释放掉。
但是,AUTO-INC锁在对大量数据进行插入的时候，会影响插入性能，我们可以使用InnoDB提供的一种轻量级的锁实现自增。

行级锁

四种类型：

  • 记录锁，仅仅把一条记录锁上
  • 间隙锁：锁定一个范围，但是不包含记录本身
  • Next-Key Lock： 记录锁和间隙锁的组合，锁定一个范围，包括记录本身。MySQL的当前读就是通过该种方式解决幻读。
  • 插入意向锁，一种特殊的间隙锁，一个事务在插入一条记录的时候，需要判断插入位置是否已被其他事务加了间隙锁。如果有的话，那么此时会生成一个插入意向锁，表明有事务想在某个区间插入新记录，但是现在处于等待状态。

行级锁的加锁规则：
NEXT-KEY LOCK会退化成记录锁或者间隙锁的原因是，退化之后仍然可以解决幻读问题。 1.唯一索引等值查询：

  • 当查询的记录是存在的，在索引树上定位到这一条记录后，将该记录的索引中的NEXT-KEY LOCK会退化成记录锁
  • 当查询的记录是不存在的，在索引树找到第一条大于该查询记录的记录后，将该记录的索引中的NEXT-KEY LOCK会退化成间隙锁。
    2.非唯一索引等值查询：
  • 当查询的记录存在时，由于不是唯一索引，所以肯定存在索引值相同的记录，于是非唯一索引等值查询的过程是一个扫描的过程，直到扫描到第一个不符合条件的二级索引记录就停止扫描，在扫描的过程中，对扫描到的二级索引记录加的是next-key锁，而对于第一个不符合条件的二级索引记录，该二级索引的next-key锁会退化成间隙锁。同时，在符合查询条件的记录的主键索引上加记录所。
  • 当查询的记录不存在时，扫描到第一条不符合条件的二级索引记录，该二级索引的next-key锁会退化成间隙锁，因为不存在满足查询条件的记录，所以不会对主键索引加锁。
  1. 非唯一索引和主键索引的范围查询
  • 唯一索引在满足一些条件的时候，索引的next-key lock会退化成间隙锁或者记录锁
  • 非唯一索引范围查询，索引的next-key lock不会退化为间隙锁和记录锁。

死锁问题

image.png

select id from t_order where order_no = 1007 for update;

事务A在执行该条语句时加了X型的next-key锁，锁范围是(1006, +∞]. 事务B在执行select语句时也加了X型的next-key锁，锁范围是(1006, +∞]. 执行完两条select语句后持有锁的情况： 事务A和事务B都持有范围(1006, +∞]X型的next-key锁

image.png

为什么事务A和事务B可以同时持有X型的锁？
因为+∞ 并不是一个真实的记录，自然就不需要考虑 X 型与 S 型关系。 需要注意的是间隙锁之间是可以互相兼容的。而不是+∞的X型的NEXT-KEY锁是不兼容的。

事务A执行INSERT语句需要获取到插入意向锁，而插入意向锁的获取需要等待事务B的间隙锁释放，于是就会陷入等待。同理事务B也需要等待事务A的间隙锁释放。最终形成了死锁

avatar
文章
阅读
粉丝