深入研究Mysql锁机制

文章目录

• 锁的种类
• • 1、全局锁
  • 2、表级锁(lock tables)
  • 3、行锁
  • 4、死锁
  • 5、间隙锁
  • • 什么是幻读
    • 幻读有什么问题
    • • 问题1 对锁内容的破坏
      • 问题2 数据不一致
    • 间隙锁加锁原则
    • 间隙锁案例
    • • 案例1
      • 案例2
      • 案例3
      • 案例4
• 总结
• 引用

锁的种类

1、全局锁

 FTWRL(flash table with read lock):使整个库进入只读状态，多用于全部备份逻辑。将全库查询出来存储到文本中。

2、表级锁(lock tables)

 lock tables可以用 unlock tables 主动释放锁，也可以在客户端断开的时候自动释放。语法除了会限制别的线程的读写外，也限定了本线程接下来的操作对象。如果在某个线程 A 中执行 lock tables t1 read, t2 write; 这个语句，则其他线程写 t1、读写 t2 的语句都会被阻塞。同时，线程 A 在执行 unlock tables 之前，也只能执行读 t1、读写 t2 的操作。连写 t1 都不允许，自然也不能访问其他表。

 MDL(metadata lock) 不需要显式使用，在访问一个表的时候会被自动加上。MDL 的作用是，保证读写的正确性，防止表结构的修改。MDL页分为读锁和写锁，读锁之间是不互斥的，也就是说可以有多个线程执行增删改查，而执行了写锁(修改表结构)就会阻塞后面的读锁和写锁，需要等到当前MDL的写锁释放后面才可以执行。

3、行锁

 MySQL 的行锁是在引擎层由各个引擎自己实现的。但并不是所有的引擎都支持行锁，比如 MyISAM 引擎就不支持行锁。不支持行锁意味着并发控制只能使用表锁，对于这种引擎的表，同一张表上任何时刻只能有一个更新在执行，这就会影响到业务并发度。InnoDB 是支持行锁的，这也是 MyISAM 被 InnoDB 替代的重要原因之一。

 行锁顾名思义，就是将锁放在当前行上，不允许两个事务同时对一行数据执行更新操作。

 在Innodb中，行锁是执行sql语句中需要的时候才会阻塞，而只有在事务执行了commit提交或者设置自动提交才会释放锁。

4、死锁

 通常死锁是发生在行上，多个事务互相阻塞其他事务所需要操作的行数据导致死锁。例如下面实例。事务A在等待事务B释放id=2行的锁，同时事务B又在等待事务A释放id=1行的锁，因此发生死锁。

处理方案

• 通过参数 innodb_lock_wait_timeout 设置超时时间。
• 发起死锁检测，发现死锁后，主动回滚死锁链条中的某一个事务，让其他事务得以继续执行。将参数 innodb_deadlock_detect 设置为 on，表示开启这个逻辑。

5、间隙锁

 提到间隙锁，我们需要先了解一个概念，就是幻读。幻读指的就是在一个事务中的两次查询，第二次查询相比于第一次会多出一些新的数据。例如下面的图例。

表结构

CREATE TABLE `t_gap_demo`
(
    `id` int(11) NOT NULL,
    `c`  int(11) DEFAULT NULL,
    `d`  int(11) DEFAULT NULL,
    PRIMARY KEY (`id`),
    KEY `c` (`c`)
) ENGINE = InnoDB;

insert into t_gap_demo values 
		(0, 0, 0),
        (5, 5, 5),
        (10, 10, 10),
        (15, 15, 15),
        (20, 20, 20),
        (25, 25, 25);
           

什么是幻读

 事务A三次查询经过了事务B更新和事务C的插入操作，在查询3之后发现相比于查询2多了一条新数据(1,1,5)，这就是幻读。幻读指的是一个事务在前后两次查询同一个范围的时候，后一次查询看到了前一次查询没有看到的行。

• 幻读只有在可重复读的当前读才会发生，如果是快照读，考虑到上文的一致性视图介绍，如果该视图没有提交结束，是不会读到被修改的数据的。
• 幻读专指新插入的行数据。

幻读有什么问题

问题1 对锁内容的破坏

 事务A的第一条sql查询表示是要对所有d=5的数据加锁，而事务B和事务C执行后分别导致了d=5的数据多了两条，并且这两条数据是加锁之后才有的数据，并没有实际意义上的加锁。因此，对于d=5的数据加锁被破坏。

问题2 数据不一致

 锁的设计是为了保证数据的一致性。而这个一致性，不止是数据库内部数据状态在此刻的一致性，还包含了数据和日志在逻辑上的一致性。

 在执行数据备份时，需要根据binlog来执行，如果事务A开启后，对某一行执行操作，在操作之后事务B开始插入一行数据，那么在此事务A的查询就会多了一条。而在binlog中，由于事务A未被提交，并且事务A并没有锁住事务B要插入的行，那么如果事务B插入了一行正好是事务A先操作的行数，就会导致事务B新增的行数被改掉，最终的结果和原本执行的最后一次查询结果不一致。

 图中最终查找的结果是(1,1,5)就是sql插入进来的。而如果在binlog记录里面来看，执行顺序是执行sql3后面再执行sql2最终结果就是d=5的都被更新成d=100，所以查询结果是空数据，因此就会产生不一致问题。

 因此，我们要最事务B的新增操作进行限制，就需要加上间隙锁。

• 开启间隙锁

  数据库默认就是1即开启间隙锁。

  关闭需要编辑mysql配置文件在 [mysqld]后面加上innodb_locks_unsafe_for_binlog = 0。重启MySQL后生效

##innodb_locks_unsafe_for_binlog为OFF表示开启间隙锁
mysql> show variables like 'innodb_locks_unsafe_for_binlog';
+--------------------------------+-------+
| Variable_name                  | Value |
+--------------------------------+-------+
| innodb_locks_unsafe_for_binlog | OFF   |
+--------------------------------+-------+
1 row in set (0.01 sec)
           

 开启之后,会在整个表之间生成(-∞,0](0,5](5,10](10,15](15,20](20,25](25,+∞]7个间隙，如果对当前行执行读加锁 select for update，那么加锁的就不止是当前行，当前行所对应的间隙也会被锁住。这样的话上面sql3的语句就会被阻塞住，无法执行，解决了一致性问题。如果当前行没有加索引，那么就会导致全表扫描，也就会导致全表都会被锁住。

间隙锁加锁原则

 先讲一下间隙锁的加锁规则(这里给丁老师一个大大的瑞思拜，更详细的可以去翻看他的专栏)

• 原则 1：加锁的基本单位是 next-key lock。next-key lock 区间是前开后闭。
• 原则 2：查找过程中访问到的对象才会加锁。
• 优化 1：索引上的等值查询，给唯一索引加锁的时候，next-key lock 退化为行锁。
• 优化 2：索引上的等值查询，向右遍历时且最后一个值不满足等值条件的时候，next-key lock 退化为间隙锁。
• 一个 bug：唯一索引上的范围查询会访问到不满足条件的第一个值为止。

间隙锁案例

案例1

全表扫描

 由于字段d没有加索引，导致在查询时就需要全表扫描，因此加的间隙锁也是整个表数据，事务B所有的操作都会被阻塞等待事务A提交才可以执行。

案例2

等值查询

 查询值为16，先根据原则1，加锁基本单位是next-key lock，前开后闭，所以锁范围是(15,20]，在之后向右遍历到最后一个值20不满足索引查询条件，根据优化2内容需要退化成间隙锁。据原则2，访问到的数据都要加锁。

 因此加锁范围是(15,20),图中的10,15,20都可以成功执行，id=17的更新操作就会被阻塞。

案例3

使用覆盖索引

 这里使用lock in share mode 表示仅使用覆盖索引，初始加锁next-key lock 加锁范围是(0,5]，从0开只向右查找，由于c不是唯一索引，所以要继续向右找不满足c=5的行数，因此找到了数据c=10位置，根据优化2，需要退化成间隙锁，因此最终加锁范围是(0,10)。图中第一个能更新成功是因为是使用的唯一索引查询，而lock in share mode 并不会锁住唯一索引，因此是可以执行成功，而第二条则是在间隙锁范围内，所以阻塞。

注意：上面说的bug1唯一索引上的范围查询会访问到不满足条件的第一个值为止在这里也是可以复现的，将查询的d=5更换成id=5之后会发现还是会继续向右查找，据说是在Mysql8.0版本已经修复。

案例4

使用范围查询

 根据next-key lock原则，左开右闭，所以next-lock范围是(5,10],根据优化1索引上的等值查询，给唯一索引加锁的时候，next-key lock 退化为行锁，因此在id>=10这里的加锁范围变成了行锁，只锁住id=10的这一行。(如果id是普通索引，那么锁住的还是(5,10]的这个区间，插入(8,8,8)就会被阻塞)。对于id<12，这里同样先看next-lock范围左开右闭，因此加锁范围是(10,15]，所以最终的加锁范围是[10,15]。

总结

 该篇文章主要是对Mysql锁相关的知识点进行探索并且附上相关实践，文中这对间隙锁的案例基本上是来自丁老师的Mysql实战，强烈推荐的。Mysql是一个很庞大的体系，学习Mysql的架构对于后面的深入研究会有很大的帮助，在这里我列出了暂时想到的学习目录(可能会有改动)由浅到深，逐步击破。

• 索引以及查询优化
• Mysql 日志文件(binlog与redolog)
• mybatis框架
• mybatis plus以及tkmybatis使用
• mybatis 插件原理以及实战
• Mysql 分表分库实现
• 集群搭建
• 高可用架构设计

引用

1、Mysql实战45讲

2、高性能Mysql

3、Mysql官方手册

系列文章链接：

1. Mysql架构概览
2. Mysql事务隔离级别与MVCC

文章仅用于学习记录分享，如有问题，感谢纠正！！！