锁：全局锁、表锁、行锁及临键锁

目录

• 1. 全局锁
  • 1.1 使用场景
  • 1.2 readonly和FTWRL区别
• 2. 表级锁
  • 2.1 表级锁类型
    • 2.1.1 表锁
    • 2.1.2 元数据锁（MDL）
    • 2.1.3 意向锁
    • 2.1.4 自增锁
  • 2.2 如何安全地给表新增字段
    • 2.2.1 新增字段阻塞场景
    • 2.2.2 新增字段安全方案
      • 2.2.2.1 方案
      • 2.2.2.2 online DDL可以解决表新增字段阻塞问题吗
• 3. 行级锁
  • 3.1 记录锁
    • 3.1.1 记录锁类型
    • 3.1.2 记录锁常见问题场景
      • 3.1.2.1 死锁
      • 3.1.2.2 热点行更新问题
  • 3.2 临键锁（Next-Key Lock）
    • 3.1 加锁基本单位
    • 3.2 范围
    • 3.3 加锁规则
  • 3.3 插入意向锁

1. 全局锁

对数据库实例加锁。

加锁flush tables with read lock，读取之外的DDL及DML都被阻塞。
释放锁unlock tables。

DDL（Data Definition Languages）语句
数据定义语言，这些语句定义了不同的数据段、数据库、表、列、索引等数据库对象的定义。常用的语句关键字主要包括 create、drop、alter等。

DML（Data Manipulation Language）语句
数据操纵语句，用于添加、删除、更新和查询数据库记录，并检查数据完整性，常用的语句关键字主要包括 insert、delete、udpate 和select 等。(增添改查）

DCL（Data Control Language）语句
数据控制语句，用于控制不同数据段直接的许可和访问级别的语句。这些语句定义了数据库、表、字段、用户的访问权限和安全级别。主要的语句关键字包括 grant、revoke 等。

1.1 使用场景

不支持mvcc的数据引擎做全库逻辑备份的时候，比如MyISAM引擎。而InnoDB引擎支持MVCC，可以直接通过mysqldump –single-transaction导数据前启动事务拿到一致性视图，期间数据能正常更新。

1.2 readonly和FTWRL区别

1. 系统变量read_only经常被拿去判断主从库；
2. 异常处理上，执行FTWRL后客户端异常断开，MySQL会自动释放全局锁，而readonly不会；

更详细的参考：readonly和FTWRL区别

2. 表级锁

2.1 表级锁类型

表锁、元数据锁、意向锁、自增锁

2.1.1 表锁

表锁的语法是lock tables … read/write。与 FTWRL 类似，可以用 unlock tables 主动释放锁，也可以在客户端断开的时候自动释放。

lock tables 语法除了会限制别的线程的读写外，也限定了本线程接下来的操作对象。举个例子, 如果在某个线程 A 中执行 lock tables t1 read, t2 write; 这个语句，则其他线程写 t1、读写 t2 的语句都会被阻塞。同时，线程 A 在执行 unlock tables 之前，也只能执行读 t1、读写 t2 的操作，并且也不能访问其他表。

2.1.2 元数据锁（MDL）

MDL 不需要显式使用，在访问一个表的时候会被自动加上。当对一个表做增删改查操作的时候,加MDL读锁；当要对表做结构变更操作的时候，加MDL写锁。

• 读锁之间不互斥，因此你可以有多个线程同时对一张表增删改查。

• 读写锁之间、写锁之间是互斥的，用来保证变更表结构操作的安全性。因此，如果有两个线程要同时给一个表加字段，其中一个要等另一个执行完才能开始执行。

2.1.3 意向锁

意向锁的存在是为了协调行锁和表锁的关系，用于优化InnoDB加锁的策略。

意向锁的主要功能就是：避免为了判断表是否存在行锁而去全表扫描。由InnoDB在操作数据之前自动加的，不需要用户干预；

意向共享锁（IS锁）：事务在请求S锁前，要先获得IS锁
意向排他锁（IX锁）：事务在请求X锁前，要先获得IX锁

2.1.4 自增锁

自增锁（Auto-Increment Lock）实现自增约束，当往表插入数据时会使用auto-inc锁来加锁，语句结束后释放锁（注意：自增锁不是在事务结束时释放）。

2.2 如何安全地给表新增字段

2.2.1 新增字段阻塞场景

DDL变更需要获取MDL写锁，若是此刻因其他事务持有MDL读锁导致获取阻塞时，会同时阻塞后续其他事务的DML操作获取MDL读锁。

假设如果某个表上的查询语句频繁，而且客户端有重试机制，超时后会再起一个新session请求，这个库的线程很快就会爆满。故，不能有长时间读锁占用，以下两种情况：

1. 数据量过大
   给一个表加字段/修改字段/加索引，需要扫描全表的数据，导致MDL读锁长期持有。
2. 长事务
   MDL锁是在事务提交后释放，若有长事务一直持有MDL读锁，也会导致DDL操作无法获取写锁。

2.2.2 新增字段安全方案

2.2.2.1 方案

1. 解决长事务。
   事务不提交，就会一直占着 MDL 锁。在 MySQL 的 information_schema 库的 innodb_trx 表中，查到当前执行中的事务。如果要做 DDL 变更的表刚好有长事务在执行，考虑不执行DDL，或者kill掉这个长事务。
2. 设置超时时间。
   set lock_wait_timeout = xx。 具体参考MySql Lock wait timeout exceeded该如何处理？

   lock_wait_timeout：数据结构ddl操作的锁的等待时间秒
   innodb_lock_wait_timeout：innodb的dml操作的行级锁的等待时间
   

2.2.2.2 online DDL可以解决表新增字段阻塞问题吗

online DDL简单执行过程如下：

1. 拿MDL写锁
2. 降级成MDL读锁
3. 真正做DDL
   1. 复制原表物理结构，创建新中间表
   2. 修改中间表的物理结构
   3. 把原表数据导入中间表
4. 升级成MDL写锁
   1. 删除原表
   2. rename中间表为原表
5. 释放MDL锁

第一步依然会阻塞，故online DDL无法解决之前的问题。
第三步中真正做DDL时，不是在当前表上做的，是新建的别的表，所以不需要写锁。
第四步中需要将旧表和新表换名字，这个过程必须要阻塞DML，所以需要加写锁。

整个步骤中的锁降级，一是为了避免其他线程拿到写锁，二是为了不阻塞DML请求。

3. 行级锁

行锁是在语句执行时才加上，但在事务结束时才释放。

对于高并发的行记录的操作语句就可以尽可能的安排到最后面，以减少锁等待的时间，提高并发性能。

• 记录锁: 把一条（行）记录锁上；
• 间隙锁: 锁定一个范围，但是不包含记录本身；
• 临键锁: 记录锁 + 间隙锁的组合，锁定一个范围，并且锁定记录本身。

3.1 记录锁

3.1.1 记录锁类型

记录锁（record lock），只锁一行记录，是行级锁的一种实现。分别共享锁和互斥锁。

3.1.2 记录锁常见问题场景

3.1.2.1 死锁

行级锁容易导致死锁问题，这里介绍下死锁概念。
死锁:当并发系统中不同线程出现循环资源依赖，涉及的线程都在等待别的线程释放资源时，就会导致这几个线程都进入无限等待的状态

** InnoDB解决策略**

1. 超时机制
   通过参数 innodb_lock_wait_timeout 来设置，事务阻塞innodb_lock_wait_timeout秒后会超时返回。
   默认值50s，时间过长或过短业务都无法接受。

2. 死锁检测
   发起死锁检测，发现死锁后，主动回滚死锁链条中的某个事物，让其他事务得以继续执行。
   参数innodb_deadlock_detect设置为on开启。

3.1.2.2 热点行更新问题

现象
CPU 利用率很高，但是每秒却执行不了几个事务。

原因
假如所有事务都要更新同一行的场景，每个新来的被堵住的线程，都要判断会不会由于自己的加入导致了死锁，这是一个时间复杂度是 O(n) 的操作。对于n个线程来说，总体时间复杂度为O(n^2)。所以虽然最终检测的结果是没有死锁，但是这期间要消耗大量的 CPU 资源。

措施

1. 关闭死锁检查
   不推荐，会等待到超时
2. 控制并发度
   (1) 客户端控制：不推荐，汇总到服务侧也很大
   (2) 中间件控制：针对某行修改进行检测，排队修改。与其mysql中排队拖累数据库，不如直接排队。
   (3) mysql源码：修改源码排队,难度大
3. 热点行一拆多
   比如一个数据a，可以拆为a1/a2/.../an，散列到不同的字段，需要时求和即为a。这样为增加业务复杂度，不过可以直接降低并发度。

3.2 临键锁（Next-Key Lock）

3.1 加锁基本单位

next-key lock  =  gap lock + row lock
执行时，先加gap lock，然后加row lock

3.2 范围

前开后闭区间

3.3 加锁规则

两个原则，两个优化，一个Bug。

• 原则 1
  加锁的基本单位是 next-key lock。

• 原则 2
  查找过程中访问到的对象才会加锁。

  这个“对象”指的是索引。 列上有普通索引就加在普通索引上；列上没有索引会走主键索引扫描，就加在主键索引上。比如一个SQL语句走覆盖索引没有回表，那么此时索引被锁的对应行主键依然不会被锁。
  lock in share mode 只锁覆盖索引，for update还会给主键索引上满足条件的行加上行锁。

• 优化 1
  索引上的等值查询，给唯一索引加锁的时候，next-key lock 退化为行锁。

• 优化 2
  索引上的等值查询，向右遍历时且最后一个值不满足等值条件的时候，next-key lock 退化为间隙锁。

• BUG
  非主键的唯一索引上的等值查询会访问到不满足条件的第一个值为止，结合优化2退化为间隙锁。

3.3 插入意向锁

插入意向锁（Insert Intention Lock）是一种间隙锁形式的意向锁，在insert 操作的时候产生。在多事务同时写入不同数据至同一索引间隙的时候，并不需要等待其他事务完成，不会发生锁等待。

假设有一个记录索引包含键值 4 和 7，两个不同的事务分别插入5 和 6，每个事务都会产生一个加在 4-7 之间的插入意向锁，获取在插入行上的排它锁，但是不会被互相锁住，因为数据行并不冲突。