MYSQL-4.Mysql锁机制

xrl66

已于 2024-04-16 13:56:02 修改

阅读量1k

点赞数 21

分类专栏： Mysql 文章标签： mysql 数据库 java 架构

于 2024-03-28 23:36:51 首次发布

本文链接：https://blog.csdn.net/xrl00/article/details/137127433

版权

Mysql 专栏收录该内容

11 篇文章 1 订阅

订阅专栏

Mysql锁机制

作用

解决并发事务带来的问题，确保数据库中，多条工作线程并行执行时的数据安全性。

分类

锁粒度划分：
1. 表锁：
  - 全局锁：整个数据库只允许读，不允许写；
  - 元数据锁/MDL锁：基于表的元数据加锁，加锁后不允许其他事物操作；
  - 意向锁：InnoDB中为了兼容行锁和表锁设计的；
  - 自增锁/AUTO-INC锁：为了提升自增ID并发插入性能设计的；
2. 行锁：
  - 记录锁/Record锁：就是行锁；
  - 间隙锁/GAP锁：InnoDB中为了解决幻读问题的一种锁机制；
  - 临建锁/Next-Key锁：同时具备记录锁+间隙锁的功能；
3. 页面锁：Berkeley DB存储引擎支持的一种锁粒度;；
互斥性划分：
1. 共享锁/S锁：不同事物间不会相互排斥；
2. 排他锁/X锁：不同事物相互排斥，同时只能允许一个事物持有所；
3. 共享排他锁/SX锁：MySQL5.7引入，主要解决SMO（B+tree结构的变更操作）带来的问题；
操作类型划分：
1. 读锁：查询时使用；
2. 写锁：插入、删除、修改、DDL语句时使用；
加锁方式划分：
1. 显式锁：编写SQL时，通过关键字，手动指定加锁粒度；
2. 隐式锁：执行SQL时，数据库自动加锁；
锁思想划分：
1. 乐观锁；
2. 悲观锁；

各类锁分析

共享锁和排他锁

共享锁：不同事物间不会排斥（读操作），可以同时获取锁并执行；但当另一个事务尝试对具备共享锁的数据进行写操作时，会被共享锁排斥，直到持有共享锁的事物结束才能继续执行；
1. 使用：通过lock in share mode显示加锁如：select * from test where id = 1 lock in share mode;
排他锁：当一个事物获取到锁后，会排斥其他事物；
1. 使用：通过for update显示加锁如：select * from test where id = 1 for update;
2. 特殊情况：当一个事物T1在读操作时添加了锁 select * from test where id = 1 for update，此时另一个事物T2来读取这条数据select * from test where id = 1，可以读到数据（跟MVCC机制有关）；
锁释放：由Mysql自己完成，在不同的隔离级别下释放时机不同；
1. 读未提交：SQL语句执行完后释放锁；
2. 读已提及：SQL语句执行完后释放锁；
3. 可重复读：事物执行结束后释放锁；

共享排他锁：

MySQL5.7版本新增，主要为了解决SMO问题；

不会阻塞共享锁，但是会阻塞排他锁和共享排他锁；
SMO问题：若SQL的更新操作触发了B+Tree的叶子节点分裂或合并，那么mysql会对整个B+Tree加排他锁，咋样会导致这张表上所有的读写操作被阻塞；
sql执行期间的三个概念：
1. 读取操作：基于B+Tree去读取某些记录；
2. 乐观写入：不改变B+Tree的索引键；
3. 悲观写入：会改变B+Tree的索引键，并改变B+Tree结构；
MySQL5.7中读操作的执行流程:
1. 读取数据前，给B+Tree加一个共享锁；
2. 检索数据，并对所有走过的叶节点加一个共享锁；
3. 找到目标数据叶子节点后，先加一个共享锁，然后释放b步骤上加的共享锁；
4. 读取目标数据，读取完成后，释放目标叶子节点的锁；
MySQL5.7中乐观写入的执行流程:
1. 写入前给B+Tree加一个共享锁；
2. 检索数据，并对所有走过的叶节点加一个共享锁；
3. 找到目标数据叶子节点后，先加一个排他锁，然后释放b步骤上加的共享锁；
4. 修改数据后，释放排他锁；
MySQL5.7中悲观写入的执行流程:
1. 写入前给B+Tree加一个共享排他锁，之后执行过程中，会阻塞其他尝试更改树结构的事物；
2. 检索数据，找到后对其分支加上排他锁，释放a中加的共享排他锁。
3. 执行写入操作，之后释放分支上的排他锁；

表锁

以表作为锁的基础，将锁加在表上；

InnoDB的表锁：InnoDB的锁机制是通过聚簇索引实现的，若能命中索引则加的是行锁，若不能则加的是表锁，如：select * from test for update，无法命中索引，则会生成一个涵盖所有行数据的锁结构，这是一个伪表锁，但作用是相同的，锁了整张表；

MyISAM的表锁：需要通过语法显示加锁；

-- MyISAM引擎中获取读锁（具备读-读可共享特性）
LOCK TABLES `table_name` READ;

-- MyISAM引擎中获取写锁（具备写-读、写-写排他特性）
LOCK TABLES `table_name` WRITE;

-- 查看目前库中创建过的表锁（in_use>0表示目前正在使用的表锁）
SHOW OPEN TABLES WHERE in_use > 0;

-- 释放已获取到的锁
UNLOCK TABLES;

元数据锁（Meta Date Lock）：基于表的元数据加锁；主要保护表结构，分读锁和写锁，

写锁在执行DDL语句时会获取，比如向一张表创建/删除索引、修改一个字段名称/数据类型、增加/删除字段等情况；

读锁在执行CRUD这类SQL语句时，会自动获取DML读锁（共享锁），DML读锁不互斥，可以获取多次。
意向锁（Intention Lock）：意向锁是InnoDB中为了支持多粒度的锁，为了兼容行锁、表锁而设计的；
1. 行锁和表锁存在的冲突：当有一个事物T1对ID=1的数据加了一个行锁，此时来了一个事物T2，想要获取这张表的写锁，这时因为其他事物以及获取了锁，T2无法满足独占性，不能获取锁；
  所以，当T2获取表锁时，需要知道表中是否有其他事物存在操作，若采用遍历的方式，不仅效率低下而且可能存在其他事物对已经遍历过的数据加锁；
2. 解决：在事物T1对ID=1数据添加行锁前，会先加一个表级别的意向锁（读：共享意向锁、写：排他意向锁），当T2去尝试获取表锁时，会先去判断一下表上是否存在意向锁，是否与自身冲突；
自增锁（AUTO-INC Lock）：自增锁主要负责维护并发事务下自增列(AUTO_INCREMENT修饰)的顺序，为提升自增ID并发插入性能而设计的：
1. 三种插入类型：
  - 普通插入：INSERT INTO table_name(...) VALUES(...)；
  - 批量插入：INSERT ... SELECT ...插入查询出的数据；
  - 混合插入：INSERT INTO table_name(id,...) VALUES(1,...),(NULL,...),(3,...)指定一部分ID，一部分不指定；
2. 三种级别通过innodb_autoinc_lock_mode参数控制：
  - innodb_autoinc_lock_mode = 0：传统模式；
    
    互斥，当事物T1获取自增锁插入数据时，事物T2也要插入数据，T2需要阻塞等待；
  - innodb_autoinc_lock_mode = 1：连续模式（mysql8.0之前默认）；
    
    普通插入时：知道需要插入多少条数据，会直接分配范围自增值，假设事物T1需要插入式条数据且当前表中最大ID=88，mysql会将89-98这是个自增值分给T1不要再去获取自增值锁，该用轻量级锁Mutex-Lock防止自增值重复；
    
    批量插入和混合插入不能知道需要插入多少条需要自动分配自增ID的数据，不能使用这种预分配思想，需要获取自增锁；
  - innodb_autoinc_lock_mode = 2：交错模式（mysql8.0之后默认）；自增列的值交错插入，比如：存在事物T1、T2，给事物T1分配「1、3、5…」给T2分配「2、4、6…」;
    
    可能会出现空隙，比如T1只插入了两条数据，5就不会被使用到；
全局锁 ：基于整个数据库加锁，加上全局锁后整个数据库只允许读，不允许任何写操作，一般在对整库做数据备份时使用；
```
-- 获取全局锁的命令
FLUSH TABLES WITH READ LOCK;

-- 释放全局锁的命令
UNLOCK TABLES;
```

行锁：

InnoDB的行锁实现：InnoDB会将表数据存储在聚簇索引中，每条行数据都会存储在树中的叶子节点上，因此行数据相互直接是“分开的”，所以可以对每一条数据上锁;
记录锁（Record Lock）：实际上就是行锁，锁定的是表中具体的一行数据；
1. 使用，前提：sql可以命中索引，否则加的是表锁；
```
-- 获取行级别的 共享锁
select * from test where id = 1 lock in share mode;

-- 获取行级别的 排他锁
select * from test where id = 1 for update;
```
  当使用update修改数据时，若where后面的条件无法命中索引，则加的也是表锁；
间隙锁（Gap Lock）：对行锁的一种补充，锁定的是间隙区间，可以被多个事物持有，用来解决幻读问题；
1. 间隙区间：假设现在有张表test只存在三条数据，ID分别为1、2、6，ID从2跳到了6，那么2-6两者之间的范围被称为间隙，间隙锁则主要锁定这块区域；
2. 使用：当存在两个事物T1、T2，在T1事物中执行select * from test where id = 3 loc in share mode;，查询一条不存在的数据并加锁，这时候其实就是加了间隙锁，锁定了(2-6)之间的区域，当T2事物想执行insert into test values(5)时，回呗阻塞，只有当事物T1结束后，才能继续执行插入操作；
3. 结论：当对一个不存在的数据加锁后，默认就是锁定前后两条数据之间的区间，当其他事务再尝试向该区间插入数据时，就会陷入阻塞，只有当持有间隙锁的事务结束后，才能继续执行插入操作。
临建锁（Next-Key Lock）：是一种由记录锁+间隙锁组成的锁，加锁后，既锁定左开右闭的区间，也会锁定当前行数据；一般情况当尝试对一条数据加锁时InnoDB默认加的是临建锁；
插入意向锁（Insert Intention Lock）：当事务执行插入语句阻塞时，就会生成一个插入意向锁，表示当前事务想对一个区间插入数据（目前的事务处于等待插入意向锁的状态）；它是一种隐式锁且不会排斥其他事物；
行锁粒度粗化（锁膨胀）：
1. 内存中存储锁对象的空间满了后，为了节省内存空间，行锁会粗化为表锁；
2. 当范围行写操作，由于要加的行锁较多，也可能会粗化成表锁；

页面锁、乐观锁和悲观锁

页面锁：锁住一页数据；
乐观锁：需自己实现，通过添加版本号version字段的方式实现，适用于读多写少的业务场景；
悲观锁：手动添加for update等；

死锁

Mysql的死锁现象：
```
-- 主键索引为user_name
SELECT * FROM `account`;
+-----------+---------+
| user_name | balance |
+-----------+---------+
|    a      | 6666666 |
|    b      | 8888888 |
+-----------+---------+

-- T1事务：a向b转账
UPDATE `account` SET balance = balance - 888 WHERE user_name = "b";
UPDATE `account` SET balance = balance + 888 WHERE user_name = "a";

-- T2事务：b向a转账
UPDATE `account` SET balance = balance - 666 WHERE user_name = "a";
UPDATE `account` SET balance = balance + 666 WHERE user_name = "b";
```
1. T1事物执行第一条sql，扣件b的余额，此时会默认加上排他锁；
2. T2事物执行第一条sql，扣件a的余额，此时页会默认加上排他锁；
3. T1事物执行第二条sql，增加a的余额，但由于a的锁被T2持有，所以会陷入阻塞等待；
4. T2事物执行第二条sql，增加b的余额，但由于b的锁被T1持有，所以也会陷入阻塞等待；
5. 死锁出现，Mysql自动检测死锁并介入ERROR 1213 (40001): Deadlock found when trying to get lock:try restarting transaction，强制回滚结束一个“死锁参与者（事物）”，打破死锁僵局，让另一个死锁能继续执行；
如何解决：
1. 两种解决死锁的方法：
  1. 锁超时机制：一个事物超过设定时间没有获取到锁，就会主动放弃等待；
  2. 外力打破僵局：外部介入，打破死锁闭环，让事物继续执行或中断；
2. Mysql锁超时机制：InnoDB中提供了锁超时机制，可通过show variables like 'innodb_lock_wait_timeout';查看锁超时时间，默认是50s；
Mysql死锁检测算法：InnoDB针对死锁问题研究了wait-for graph算法；
1. 启用后，Mysql会收集两个信息：
  1. 锁的信息链表：目前持有每个锁的事物是哪个；
  2. 事物等待链表：阻塞的事物要等待的锁是哪个；
2. 具体流程：
  1. 当一个事物需要阻塞等待锁时触发；
  2. 以该事物为起点，从锁的信息链表中，查找对应锁的持有者（事物）信息；
  3. 再根据锁的持有者信息，去事物等待链表中，查找持有者是否再等待获取其他锁；
  4. 若持有者在等待获取其他锁，则以持有者为起点继续查找，直到出现闭环或查找到未阻塞事物；
  5. 若出现闭环，则介入破坏闭环，选择一个事物进行回滚操作；回滚事物选择：
    - 选择数据更改最小的事物，也就是undo-log日志中数据量最小的事物，若数据量相同，则会选择回滚触发死锁的事物；
3. 开启关闭：可以通过innodb_deadlock_detect=on|off参数来控制是否开启死锁检测机制；
如何避免死锁：
1. 合理设置索引结构，使sql能精准定位数据，从而减小锁粒度；
2. 根据业务，合理降低隔离级别，从而减小锁的限制；
3. 拆分业务粒度，尽量将大事物拆分成多个小事物，缩短事物持有锁的时间；
4. 将（对账、刷数据等）大事物放到系统使用量低的时候去执行；
5. 尽量不手动在事物中获取排他锁；
6. 当死锁发生并被mysql自行解决后，这个死锁绝对还会再次出现，需要去寻找产生死锁原因并解决；

锁状态

MySQL整体的锁状态可以通过show status like 'innodb_row_lock_%';命令查询；
在这里插入图片描述
Innodb_row_lock_current_waits：当前正在阻塞等待锁的事务数量。
Innodb_row_lock_time：MySQL启动到现在，所有事务总共阻塞等待的总时长。
Innodb_row_lock_time_avg：平均每次事务阻塞等待锁时，其平均阻塞时长。
Innodb_row_lock_time_max：MySQL启动至今，最长的一次阻塞时间。
Innodb_row_lock_waits：MySQL启动到现在，所有事务总共阻塞等待的总次数。

内存结构：

在这里插入图片描述

锁的事物信息：记录生成锁结构的事物指针；
索引信息：行锁特有，记录加锁行数据属于哪个索引、哪个节点的指针；
锁粒度信息：表锁记录的时加锁表信息；行锁记录信息较多，有三个较为重要：
1. Space ID：加锁行数据，所在表空间ID；
2. page number：加锁行数据，所在的页号；
3. n_bits：使用的比特位，对一页数据中，加了多少个锁；
锁类型：使用了一个32bit的type_mode来表示，可拆分成lock_mode、lock_type、rec_lock_type三部分，如：阻塞等待的行级排他临键锁结构 00000000000000000000000（临键锁）1（阻塞）0010（行级锁）0011（排他锁）：
1. lock_mode：使用1-4位，表示锁模式；
  - 0000/0：表示锁结构是共享意向锁；
  - 0001/1：表示锁结构是排他意向锁；
  - 0010/2：表示锁结构是共享锁；
  - 0011/3：表示锁结构是排他锁；
  - 0100/4：表示锁结构是自增锁；
2. lock_type：使用5-8位，表示锁的类型；
  - 0001：表示是表级锁；
  - 0010：表示是行级锁；
3. rec_lock_type：使用10-32位，表示行锁具体类型；
  - 临键锁：高23位全位0；
  - 间隙锁：第10位为1；
  - 记录锁：第11位为1；
  - 插入意向锁：第12位为1；
  - …
4. is_waiting：使用第9位，表示锁的状态是等待还是持有；
  1. 0：表示持有状态，无需阻塞等待；
  2. 1：表示等待状态，需要阻塞等待；
其他信息：用于辅助锁机制的信息，如每一个事务和锁的持有、等待关系等，将所有的事务、锁连接起来，就可以形成死锁检测机制中的事物等待链表、锁的信息链表。
锁的比特位：当一张表里有3条数据时，会形成一个比特数组：00000，首位和末位即表示无穷小、无穷大两个间隙；当事物T1对1，3两条数据加锁了，此时比特数组会变为01010，表示T1锁定了两条数据；

`InnoDB`的锁实现：

通过聚簇索引实现；
若是基于非聚簇索引字段作为条件进行写操作时，会加两个锁，一个锁是聚簇索引中的主键，另一个是非聚簇索引中的辅助索引键，加的顺序是：先加主键锁，再加辅助锁；

但在语句执行时，判断的顺序为：先判断非聚簇索引键有没有锁，再判断聚簇索引键有没有锁，碰到锁则阻塞等待；当都无锁时，这时才会开始加锁；
mysql的锁合并：mysql对数据加锁，并为每一条数据生成一个锁结构，而是会将满足一定条件的行记录放入到同一个锁结构中，合并条件如下；
1. 对不同记录加锁的事物是同一个；
2. 需要加锁的记录在同一个页中；
3. 加锁类型相同；
4. 锁的等待状态相同；
Mysql获取锁的过程：
1. 当一个事物要获取行锁时，检查内存中是否存在这条数据的锁结构；
  1. 存在：将对应is_waiting的比特位改为1，表示当前事物在阻塞等待该锁，待其他事物释放锁后，会唤醒当前阻塞事物，如何将is_waiting改为0，继续执行sql;
  2. 如何检查内存中是否存在这条数据的锁结构：根据锁粒度信息判断；
释放锁过程：一般由mysql自己完成，事物结束后会自动释放，释放时会检查内存中释放有事物正在等待获取当前释放的锁，如果有责唤醒对应事物；