MySQL 数据库 锁

MySQL8.0 InnoDb存储引擎

• 锁

  1. 乐观锁与悲观锁
  2. 共享锁与排他锁
  3. 死锁
  4. 间隙锁与行锁升级为表锁

  锁	innodb支持	加锁速度	粒度	开销	并发度	死锁
  * 行锁	是	慢	小	大	高	是
  页锁	BDB引擎 否	中	中	中	中	是
  表锁	是	快	大	小	低	否

  乐观锁：总是认为不会产生并发问题，每次去取数据的时候总认为不会有其他线程对数据进行修改，因此不会上锁，但是在更新时会判断其他线程在这之前有没有对数据进行修改，一般会使用版本号机制或CAS操作实现。

  悲观锁：总是假设最坏的情况，每次取数据时都认为其他线程会修改，所以都会加锁（读锁、写锁、行锁等），当其他线程想要访问数据时，都需要阻塞挂起。

  共享锁：又称为读锁，简称（S）锁，顾名思义，共享锁就是多个事务对于同一数据可以共享一把锁，都能访问到数据，但是只能读不能修改。

  排他锁：又称为写锁，简称（X）锁，顾名思义，排他锁就是不能与其他所并存，如一个事务获取了一个数据行的排他锁，其他事务就不能再获取该行的其他锁，包括共享锁和排他锁，但是获取排他锁的事务是可以对数据就行读取和修改。

  <a href="https://i.ibb.co/Hx0d4qX/mysql.png" title="死锁">死锁</a>：是指两个或两个以上的进程在执行过程中,因争夺资源而造成的一种互相等待的现象,若无外力作用，它们都将无法推进下去.此时称系统处于死锁状态或系统产生了死锁，这些永远在互相等待的进程称为死锁进程。
  https://blog.csdn.net/weixin_44337261/article/details/108970710

  间隙锁：当我们采用范围条件查询数据时，InnoDB 会对这个范围内的数据进行加锁。比如有 id 为：1、3、5、7 的 4 条数据，我们查找 1-7 范围的数据。那么 1-7 都会被加上锁。2、4、6 也在 1-7 的范围中，但是不存在这些数据记录，这些 2、4、6 就被称为间隙。

  当索引失效的时候，行锁会升级成表锁，索引失效的其中一个方法是对索引自动 or 手动的换型。a 字段本身是 integer，我们加上引号，就变成了 String，这个时候索引就会失效了。

  Navicat 客户端中演示

  CREATE TABLE `user` (
    `id` int unsigned NOT NULL AUTO_INCREMENT,
    `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT '' COMMENT '名字',
    `age` int DEFAULT '0' COMMENT '年龄',
    `gender` tinyint(1) DEFAULT '1' COMMENT '性别',
    `version` int DEFAULT '0' COMMENT '版本',
    PRIMARY KEY (`id`),
    KEY `index_name` (`name`),
    KEY `index_age` (`age`)
  ) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
  

  -- 乐观锁
  set @id = (SELECT version FROM `user` WHERE id = 1);
  
  -- 在此更新之前，其他的事务有改变此记录 (版本自增了)【另一个窗口中打开】
  -- UPDATE `user` SET version = version + 1;
  
  -- 延时操作
  select SLEEP(5);
  
  UPDATE `user` SET gender = gender + 1 WHERE id = 1 AND version = (SELECT @id);
  

  	读锁	写锁
  读锁	YES	NO
  写锁	NO	NO

  ### 验证读写锁优先级 ###
  BEGIN;
  SELECT * FROM `user` WHERE id = 1 FOR UPDATE;
  
  -- SELECT * FROM `user` WHERE id = 1 lock in SHARE mode;
  -- update `user` SET version = version + 1 WHERE id  = 1;
  
  SELECT SLEEP(5);
  -- COMMIT;
  
  ### A会话加锁之后，A会话可以写，其他会话无法执行 写 操作
  UPDATE `user` SET version = version + 1 WHERE id  = 1;
  DELETE FROM `user` WHERE id = 1;
  

  ### 死锁 ###
  -- 先执行
  BEGIN;
  SELECT * FROM `user` WHERE id = 1 FOR UPDATE;
  
  SELECT SLEEP(5);
  
  SELECT * FROM `user` WHERE id = 2 FOR UPDATE;
  
  COMMIT;
  
  -- 后执行 ERROR 1213 - Deadlock found when trying to get lock; try restarting transaction [死锁]
  BEGIN;
  SELECT * FROM `user` WHERE id = 2 FOR UPDATE;
  
  SELECT SLEEP(5);
  
  SELECT * FROM `user` WHERE id = 1 FOR UPDATE;
  
  COMMIT;
  
  ### 间隙锁 ###
  BEGIN;
  SELECT * FROM `user` WHERE id = 20 FOR UPDATE;
  -- COMMIT;
  
  -- id 20 - 30 区间会加上间隙锁
  BEGIN;
  SELECT * FROM `user` WHERE id = 30 FOR UPDATE;
  -- commit;
  
  
  insert into `user` VALUES(32, '张三', 18, 0, 0);
  

• 什么是事务

  1. 事务是数据库系统区别于其他一切文件系统的重要特性之一
  2. 事务是一组具有原子性的SQL语句，或是一个独立的工作单元

  • 一、事务的基本要素（ACID）

  1. 原子性（Atomicity）：事务开始后所有操作，要么全部做完，要么全部不做，不可能停滞在中间环节。事务执行过程中出错，会回滚到事务开始前的状态，所有的操作就像没有发生一样。也就是说事务是一个不可分割的整体，就像化学中学过的原子，是物质构成的基本单位。
  2. 一致性（Consistency）：事务开始前和结束后，数据库的完整性约束没有被破坏 。比如A向B转账，不可能A扣了钱，B却没收到。
  3. 隔离性（Isolation）：同一时间，只允许一个事务请求同一数据，不同的事务之间彼此没有任何干扰。比如A正在从一张银行卡中取钱，在A取钱的过程结束前，B不能向这张卡转账。
  4. 持久性（Durability）：事务完成后，事务对数据库的所有更新将被保存到数据库，不能回滚。

  • 二、事务的并发问题

  1. 脏读：事务A读取了事务B更新的数据，然后B回滚操作，那么A读取到的数据是脏数据
  2. 不可重复读：事务 A 多次读取同一数据，事务 B 在事务A多次读取的过程中，对数据作了更新并提交，导致事务A多次读取同一数据时，结果 不一致。
  3. 幻读：系统管理员A将数据库中所有学生的成绩从具体分数改为ABCDE等级，但是系统管理员B就在这个时候插入了一条具体分数的记录，当系统管理员A改结束后发现还有一条记录没有改过来，就好像发生了幻觉一样，这就叫幻读。

• 事务的隔离级别

查看系统变量配置
    mysqld --verbose --help

• 修改隔离级别

-- set session transaction isolation level  隔离级别参数
-- set session transaction isolation level read uncommitted; -- 读未提交
-- set session transaction isolation level read committed; -- 不可重复读
-- set session transaction isolation level repeatable read; -- 可重复读

show variables like '%isolation'; -- 查看当前会话隔离级别

• MVCC

MVCC，全称Multi-Version Concurrency Control，即多版本并发控制。MVCC是一种并发控制的方法，一般在数据库管理系统中，实现对数据库的并发访问，在编程语言中实现事务内存。

• InnoDB是一个多版本的存储引擎。它保留有关已更改行的旧版本的信息以支持事务功能，例如并发和回滚。该信息以称为回滚段的数据结构存储在撤消表空间中。请参阅第 15.6.3.4 节，“撤消表空间”。 InnoDB使用回滚段中的信息来执行事务回滚所需的撤消操作。它还使用这些信息来构建行的早期版本以实现一致的读取。请参阅 第 15.7.2.3 节，“一致的非锁定读取”。在内部，InnoDB为存储在数据库中的每一行添加三个字段：

• 一个 6 字节DB_TRX_ID字段指示插入或更新行的最后一个事务的事务标识符。此外，删除在内部被视为更新，其中设置了行中的特殊位以将其标记为已删除。

• DB_ROLL_PTR称为滚动指针的 7 字节字段。回滚指针指向写入回滚段的撤消日志记录。如果该行被更新，撤消日志记录包含在更新前重建该行内容所需的信息。

• 一个 6 字节的DB_ROW_ID字段包含一个行 ID，随着插入新行而单调增加。如果 InnoDB自动生成聚集索引，则该索引包含行 ID 值。否则，该 DB_ROW_ID列不会出现在任何索引中。

• 1.1 InnDB 中的 MVCC
  InnDB 中每个事务都有一个唯一的事务 ID，记为 transaction_id。它在事务开始时向 InnDB 申请，按照时间先后严格递增。而每行数据其实都有多个版本，这就依赖 undo log 来实现了。每次事务更新数据就会生成一个新的数据版本，并把 transaction_id 记为 row trx_id。同时旧的数据版本会保留在 undo log 中，而且新的版本会记录旧版本的回滚指针，通过它直接拿到上一个版本。
  所以，InnDB 中的 MVCC 其实是通过在每行记录后面保存两个隐藏的列来实现的。一列是事务 ID：trx_id；另一列是回滚指针：roll_pt。

• 2、undo log
  回滚日志保存了事务发生之前的数据的一个版本，可以用于回滚，同时可以提供多版本并发控制下的读（MVCC），也即非锁定读。
  根据操作的不同，undo log 分为两种： insert undo log 和 update undo log。

• 2.1 insert undo log
  insert 操作产生的 undo log，因为 insert 操作记录没有历史版本只对当前事务本身可见，对于其他事务此记录不可见，所以 insert undo log 可以在事务提交后直接删除而不需要进行 purge 操作。
  purge 的主要任务是将数据库中已经 mark del 的数据删除，另外也会批量回收 undo pages

  所以，插入数据时。它的初始状态是这样的：

  
  
  mvcc-1.png

• 2.2 update undo log
  UPDATE 和 DELETE 操作产生的 Undo log 都属于同一类型：update_undo。（update 可以视为 insert 新数据到原位置，delete 旧数据，undo log 暂时保留旧数据）。事务提交时放到 history list 上，没有事务要用到这些回滚日志，即系统中没有比这个回滚日志更早的版本时，purge 线程将进行最后的删除操作。
  一个事务修改当前数据：

  
  
  mvcc-2.png

  另一个事务修改数据：

  
  
  mvcc-3.png

  这样的同一条记录在数据库中存在多个版本，就是上面提到的多版本并发控制 MVCC。

  另外，借助 undo log 通过回滚可以回到上一个版本状态。比如要回到 V1 只需要顺序执行两次回滚即可。

• 3、read-view
  read view 是 InnDB 在实现 MVCC 时用到的一致性读视图，用于支持 RC（读提交）以及 RR（可重复读）隔离级别的实现。read view 不是真实存在的，只是一个概念，undo log 才是它的体现。它主要是通过版本和 undolog 计算出来的。作用是决定事务能看到哪些数据。每个事务或者语句有自己的一致性视图。普通查询语句是一致性读，一致性读会根据 row trx_id 和一致性视图确定数据版本的可见性。

• 3.1 数据版本的可见性规则
  read view 中主要包含当前系统中还有哪些活跃的读写事务，在实现上 InnDB 为每个事务构造了一个数组，用来保存这个事务启动瞬间，当前正活跃（还未提交）的事务。前面说了事务 ID 随时间严格递增的，把系统中已提交的事务 ID 的最大值记为数组的低水位，已创建过的事务 ID + 1记为高水位。
  这个视图数组和高水位就组成了当前事务的一致性视图（read view）

  这个数组画个图，长这样：

  
  
  read-view.png

  规则如下：

  • 如果 trx_id 在灰色区域，表明被访问版本的 trx_id 小于数组中低水位的 id 值，也即生成该版本的事务在生成 read view 前已经提交，所以该版本可见，可以被当前事务访问。

  • 如果 trx_id 在橙色区域，表明被访问版本的 trx_id 大于数组中高水位的 id 值，也即生成该版本的事务在生成 read view 后才生成，所以该版本不可见，不能被当前事务访问。

  • 如果在绿色区域，就会有两种情况：

    • trx_id 在数组中，证明这个版本是由还未提交的事务生成的，不可见

    • trx_id 不在数组中，证明这个版本是由已提交的事务生成的，可见

      落在绿色区域意味着是事务 ID 在低水位和高水位这个范围里面，而真正是否可见，看绿色区域是否有这个值。如果绿色区域没有这个事务 ID，则可见，如果有，则不可见。在这个范围里面并不意味着这个范围就有这个值，比如 [1,2,3,5]，4 在这个数组 1-5 的范围里，却没在这个数组里面。

      • 参考：https://blog.csdn.net/weixin_52622200/article/details/119741701

• 当前读

像select lock in share mode(共享锁), select for update ; update, insert ,delete(排他锁)这些操作都是一种当前读，为什么叫当前读？就是它读取的是记录的最新版本，读取时还要保证其他并发事务不能修改当前记录，会对读取的记录进行加锁。

• 快照度

像不加锁的select操作就是快照读，即不加锁的非阻塞读；快照读的前提是隔离级别不是串行级别，串行级别下的快照读会退化成当前读；之所以出现快照读的情况，是基于提高并发性能的考虑，快照读的实现是基于多版本并发控制，即MVCC,可以认为MVCC是行锁的一个变种，但它在很多情况下，避免了加锁操作，降低了开销；既然是基于多版本，即快照读可能读到的并不一定是数据的最新版本，而有可能是之前的历史版本，保证了 ACID 中的 I 特性（隔离性)。