数据库锁设计的初衷是处理并发问题。作为多用户共享的资源，当出现并发访问的时候，数据库需要合理地控制资源的访问规则。而锁就是用来实现这些访问规则的重要数据结构。

全局锁

属于Server层，对整个数据库实例加锁。此时整个数据库实例处于只读状态，无法对其进行增、删、改等其他操作。

  • 应用场景：

全局锁的典型使用场景是，做全库逻辑备份。MyISAM 这种不支持事务的引擎，如果备份过程中有更新，总是只能取到最新的数据，那么就破坏了备份的一致性。这时，我们就需要使用 FTWRL 命令了。

  • 开启全局锁（FTWRL）

具体执行语句： Flush tables with read lock

  • FTWRL VS set global readonly=ture

建议使用 FTWRL 方式，原因如下：

  1. 在有些系统中，readonly 的值会被用来做其他逻辑，比如用来判断一个库是主库还是备库。因此，修改 global 变量的方式影响面更大，我不建议你使用。
  2. 在异常处理机制上有差异。如果执行 FTWRL 命令之后由于客户端发生异常断开，那么 MySQL 会自动释放这个全局锁，整个库回到可以正常更新的状态。而将整个库设置为 readonly 之后，如果客户端发生异常，则数据库就会一直保持 readonly 状态，这样会导致整个库长时间处于不可写状态，风险较高。
  • mysqldump（官方自带逻辑备份工具）

官方自带的逻辑备份工具是 mysqldump。当 mysqldump 使用参数 –single-transaction 的时候，导数据之前就会启动一个事务，来确保拿到一致性视图。而由于 MVCC 的支持，这个过程中数据是可以正常更新的。

  • 使用前提：引擎要支持 可重复读 的事务隔离级别
  • 适用场景：全库逻辑备份

做全库逻辑备份，也就是把整库每个表都 select 出来存成文本。

  • 使用全局锁可能存在的问题：
    • 如果你在主库上备份，那么在备份期间都不能执行更新，业务基本上就得停摆；
    • 如果你在从库上备份，那么备份期间从库不能执行主库同步过来的 binlog，会导致主从延迟。
  • 全局锁解决是什么问题？

不使用全局锁，备份系统备份的得到的库不是一个逻辑时间点，这个视图是逻辑不一致的。可能会出现数据不一致的情况。

表级锁

属于Server层，这里需要特别注意，表锁是用来隔离表数据间操作的，而 元数据锁 是用来隔离 DDL和DML间数据操作的，这两个锁是不同的。

表锁

表锁一般是在数据库引擎不支持行锁的时候才会被用到的。

表锁的语法是 lock tables … read/write
与 FTWRL 类似，可以用 unlock tables 主动释放锁，也可以在客户端断开的时候自动释放。需要注意，lock tables 语法除了会限制别的线程的读写外，也限定了本线程接下来的操作对象。

  • 示例

如果在某个线程 A 中执行 lock tables t1 read, t2 write; 这个语句，则其他线程写 t1、读写 t2 的语句都会被阻塞。同时，线程 A 在执行 unlock tables 之前，也只能执行读 t1、读写 t2 的操作。连写 t1 都不允许，自然也不能访问其他表。

元数据锁（MDL）

MDL（metadata lock)是表结构的锁，不需要显式使用，在访问一个表的时候会被自动加上，当对一个表做增删改查操作(DML)的时候，加 MDL 读锁；当要对表做结构变更操作(DDL)的时候，加 MDL 写锁。MDL 的作用是，避免读写时，更改了表结构而出现的数据不一致问题，保证读写的正确性。

  • MDL读锁
    • 读锁之间不互斥，因此可以多个线程同时对一张表CRUD， 但是不能修改表结构。
  • MDL写锁
    • 读写锁之间是互斥的，主要作用是防止DDL和DML的并发冲突；
    • 写锁之间是互斥的，主要作用是保证表结构不能被并行修改。
  • 给小表加字段存在的问题

image.png
session A 先启动，这时候会对表 t 加一个 MDL 读锁。由于 session B 需要的也是 MDL 读锁，因此可以正常执行。
之后 session C 会被 blocked，是因为 session A 的 MDL 读锁还没有释放，而 session C 需要 MDL 写锁，因此只能被阻塞。
如果只有 session C 自己被阻塞还没什么关系，但是之后所有要在表 t 上新申请 MDL 读锁的请求也会被 session C 阻塞。前面我们说了，所有对表的增删改查操作都需要先申请 MDL 读锁，就都被锁住，等于这个表现在完全不可读写了。
如果某个表上的查询语句频繁，而且客户端有重试机制，也就是说超时后会再起一个新 session 再请求的话，这个库的线程很快就会爆满。
你现在应该知道了，事务中的 MDL 锁，在语句执行开始时申请，但是语句结束后并不会马上释放，而会等到整个事务提交后再释放。

  • 申请MDL锁的操作会形成一个队列，队列中写锁获取优先级高于读锁。一旦出现写锁等待，会阻塞后续该表的所有操作。
  • 如何安全的给小表加字段

首先我们要解决长事务，事务不提交，就会一直占着 MDL 锁。

  • 方式一：先kill长事务，再修改表（不推荐）

在 MySQL 的 information_schema 库的 innodb_trx 表中，你可以查到当前执行中的事务。如果你要做 DDL 变更的表刚好有长事务在执行，要考虑先暂停 DDL，或者 kill 掉这个长事务。但这时候 kill 可能未必管用，因为新的请求马上就来了。

  • 方式二：（推荐）

在 alter table 语句里面设定等待时间，如果在这个指定的等待时间里面能够拿到 MDL 写锁最好，拿不到也不要阻塞后面的业务语句，先放弃。之后开发人员或者 DBA 再通过重试命令重复这个过程。
MariaDB 已经合并了 AliSQL 的这个功能，所以这两个开源分支目前都支持 DDL NOWAIT/WAIT n 这个语法。

  1. ALTER TABLE tbl_name NOWAIT add column ... 
  2. ALTER TABLE tbl_name WAIT N add column ...

行锁

属于引擎层，行锁就是针对数据表中行记录的锁。分为读锁和写锁，读-读不冲突，其他情况都冲突。 如 MyISAM 引擎就不支持行锁，这也是其被InnoDB 替代的重要原因之一。

两阶段锁协议

在 InnoDB 事务中，行锁是在需要的时候才加上的，但并不是不需要了就立刻释放，而是要等到事务结束时才释放，这个就是两阶段锁协议。
因此，如果你的事务中需要锁多个行，要把最可能造成锁冲突、最可能影响并发度的锁尽量往后放，以减少锁占用时间，从而减少阻塞。

死锁

image.png
事务 A 在等待事务 B 释放 id=2 的行锁，而事务 B 在等待事务 A 释放 id=1 的行锁。 事务 A 和事务 B 在互相等待对方的资源释放，就是进入了死锁状态。

  • 解决死锁的两种策略：
    • 被动死锁检测

直接进入等待，直到超时。这个超时时间可以通过参数 innodb_lock_wait_timeout 来设置。
在 InnoDB 中，innodb_lock_wait_timeout 的默认值是 50s， 意味着，当出现死锁以后，第一个被锁住的线程要过 50s 才会超时退出，然后其他线程才有可能继续执行。
但是，我们又不可能直接把这个时间设置成一个很小的值，比如 1s。这样当出现死锁的时候，确实很快就可以解开，但如果不是死锁，而是简单的锁等待呢？所以，超时时间设置太短的话，会出现很多误伤。

  • 主动死锁检测（推荐）

每当一个事务被锁的时候，就要看看它所依赖的线程有没有被别人锁住，如此循环，最后判断是否出现了循环等待，也就是死锁。
通过 innodb_deadlock_detect 设置，默认开启。

  - **优点**：能够快速发现死锁并进行处理
  - **缺点**：额外增加了CPU的负担

每个新来的被堵住的线程，都要判断会不会由于自己的加入导致了死锁，这是一个时间复杂度是 O(n) 的操作。假设有 1000 个并发线程要同时更新同一行，那么死锁检测操作就是 100 万这个量级的。虽然最终检测的结果是没有死锁，但是这期间要消耗大量的 CPU 资源。因此，你就会看到 CPU 利用率很高，但是每秒却执行不了几个事务。

     - **如何尽量规避它的缺点？**
        - 方案一：关闭主动死锁检测

就是如果你能确保这个业务一定不会出现死锁，可以临时把死锁检测关掉。但是这种操作本身带有一定的风险，因为业务设计的时候一般不会把死锁当做一个严重错误，毕竟出现死锁了，就回滚，然后通过业务重试一般就没问题了，这是业务无损的。而关掉死锁检测意味着可能会出现大量的超时，这是业务有损的。

        - 方案二： 并发控制

如果并发能够控制住，比如同一行同时最多只有 10 个线程在更新，那么死锁检测的成本很低，就不会出现这个问题。

           - 客户端并发控制
           - 数据库端并发控制，修改Mysql源码
           - MQ实现并发控制
        - 方案三：将一行改成逻辑上的多行来减少锁冲突（推荐）

以影院账户为例，可以考虑放在多条记录上，比如 10 个记录，影院的账户总额等于这 10 个记录的值的总和。这样每次要给影院账户加金额的时候，随机选其中一条记录来加。这样每次冲突概率变成原来的 1/10，可以减少锁等待个数，也就减少了死锁检测的 CPU 消耗。
这个方案看上去是无损的，但其实这类方案需要根据业务逻辑做详细设计。如果账户余额可能会减少，比如退票逻辑，那么这时候就需要考虑当一部分行记录变成 0 的时候，代码要有特殊处理。

间隙锁

顾名思义，间隙锁，锁的就是两条记录之间的空隙。产生幻读的原因是，行锁只能锁住已经存在的行，但是新插入的记录（记录还没存在就无法使用行锁），要更新的是记录之间的“间隙”。因此，为了解决幻读问题，InnoDB 引入了间隙锁 (Gap Lock)。属于引擎层 ，且在RR隔离级别下才有效。详

间隙锁和行锁合称 next-key lock，每个 next-key lock 是前开后闭区间。

  • 注意：

    • 间隙锁与间隙锁之间是不存在冲突关系的，跟间隙锁存在冲突关系的，是“往这个间隙中插入一个记录”这个操作；
    • 数据行是可以加上锁的实体，数据行之间的间隙，也是可以加上锁的实体；
    • 间隙锁是在RR下才会生效，如果把隔离级别设置为RC，就没有间隙锁了。但同时，你要解决可能出现的数据和日志不一致问题，需要把 binlog 格式设置为 row。这，也是现在不少公司使用的配置组合。

  • 间隙锁引入的问题：

间隙锁的引入，可能会导致同样的语句锁住更大的范围，这会影响并发度，同时也会造成死锁。

间隙锁的加锁规则

两原则、两优化、一“bug” 注意：MySQL 后面的版本可能会改变加锁策略，所以这个规则只限于截止到现在的最新版本，即 5.x 系列 <=5.7.24，8.0 系列 <=8.0.13

  • 原则一：加锁的基本单位是next-key lock；
    • “间隙”，其实根本就是由“这个间隙右边的那个记录”定义的
  • 原则二：查找过程中访问到的对象才会加锁。
  • 优化一：索引上的等值查询，给唯一索引加锁的时候，next-key lock退化为行锁；
  • 优化二：索引上的等值查询，向右遍历时且最后一个值不满足等值条件的时候，next-key lock 退化为间隙锁。
    • 非等值查询时，如果通过树搜索的方式定位记录，本质上用的是“等值查询”的方法，因此优化二是有效的。

  • 一bug：唯一索引上的范围查询会访问到不满足条件的第一个值为止。

    • 8.0.18已经修复

  • 特别说明：

    • 锁是加在索引上的，并非行。使用lock in share mode 来给行加读锁，如果使用了覆盖索引的优化，那么仅仅锁住的是覆盖索引中出现的字段对应的 next-key lock，而并非整行所有字段的 next-key lock。要锁住整行，要么放弃覆盖索引的优化，要么使用 for update。示例详见原稿“案例二”
    • delete 语句加锁的逻辑，其实跟 select … for update 是类似的
    • 分析加锁规则的时候可以用 next-key lock 来分析。但是要知道，具体执行的时候，是要分成间隙锁和行锁两段来执行的。示例详见原稿“案例八”