MySQL中的锁

By gavin
Posted on August 10, 2021

数据库锁设计的初衷是处理并发问题。作为多用户共享的资源，当出现并发访问的时候，数据库需要合理地控制资源的访问规则。而锁就是用来实现这些访问规则的重要数据结构。
根据加锁的范围，MySQL 里面的锁大致可以分成全局锁、表级锁和行锁三类。

一、全局锁

全局锁就是对整个数据库实例加锁。MySQL 提供了一个加全局读锁的方法，命令是 Flush tables with read lock (FTWRL)。当你需要让整个库处于只读状态的时候，可以使用这个命令，之后其他线程的以下语句会被阻塞：数据更新语句（数据的增删改）、数据定义语句（包括建表、修改表结构等）和更新类事务的提交语句。

全局锁的典型使用场景是，做全库逻辑备份。 也就是把整库每个表都 select 出来存成文本。

风险：
1.如果在主库备份，在备份期间不能更新，业务停摆
2.如果在从库备份，备份期间不能执行主库同步的binlog，导致主从延迟

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

全局锁主要用在逻辑备份过程中。对于全部是 InnoDB 引擎的库，建议你选择使用 –single-transaction 参数，对应用会更友好。

二、表级锁

MySQL 里面表级别的锁有两种：一种是表锁，一种是元数据锁（meta data lock，MDL)。

1、 表锁

表锁是在Server层实现的。ALTER TABLE之类的语句会使用表锁，忽略存储引擎的锁机制。

表锁的语法是 lock tables … read/write。 与 FTWRL 类似，可以用 unlock tables 主动释放锁，也可以在客户端断开的时候自动释放。需要注意，lock tables 语法除了会限制别的线程的读写外，也限定了本线程接下来的操作对象。
举个例子, 如果在某个线程 A 中执行 lock tables t1 read, t2 write; 这个语句，则其他线程写 t1、读写 t2 的语句都会被阻塞。同时，线程 A 在执行 unlock tables 之前，也只能执行读 t1、读写 t2 的操作。连写 t1 都不允许，自然也不能访问其他表。
在还没有出现更细粒度的锁的时候，表锁是最常用的处理并发的方式。而对于 InnoDB 这种支持行锁的引擎，一般不使用 lock tables 命令来控制并发，毕竟锁住整个表的影响面还是太大。

1.1、metadata lock

另一类表级的锁是 MDL（metadata lock)。

MDL 不需要显式使用，在访问一个表的时候会被自动加上。
MDL 的作用是并发情况下维护数据的一致性，保证读写的正确性。(避免加字段删字段导致查询结果异常)
因此，在 MySQL 5.5 版本中引入了 MDL，当对一个表做增删改查操作的时候，加 MDL 读锁；
当要对表做结构变更操作的时候，加 MDL 写锁。

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

事务中的 MDL 锁，在语句执行开始时申请，但是语句结束后并不会马上释放，而会等到整个事务提交后再释放。

给一个表加字段，或者修改字段，或者加索引，需要扫描全表的数据。
而实际上，即使是小表，操作不慎也会出问题。在修改表的时候会持有MDL写锁，如果这个表上的查询语句频繁，而且客户端有重试机制，也就是说超时后会再起一个新 session 再请求的话，这个库的线程很快就会爆满。

MDL 是并发情况下维护数据的一致性，在表上有事务的时候,不可以对元数据经行写入操作,并且这个是在server层面实现的

三、行锁

MySQL 的行锁是在引擎层由各个引擎自己实现的。
但并不是所有的引擎都支持行锁，比如 MyISAM 引擎就不支持行锁。
InnoDB 是支持行锁的，这也是 MyISAM 被 InnoDB 替代的重要原因之一。
InnoDB行锁包括 Record Lock 、 Gap Lock、 Next-Key Lock

在 InnoDB 事务中，行锁是在需要的时候才加上的，但并不是不需要了就立刻释放，而是要等到事务结束时才释放。这个就是两阶段锁协议。

知道了这个设定，对我们使用事务有什么帮助呢？那就是，如果你的事务中需要锁多个行，要把最可能造成锁冲突、最可能影响并发度的锁尽量往后放。

假设你负责实现一个电影票在线交易业务，顾客 A 要在影院 B 购买电影票。我们简化一点，这个业务需要涉及到以下操作：

  1. 从顾客 A 账户余额中扣除电影票价；
  2. 给影院 B 的账户余额增加这张电影票价；
  3. 记录一条交易日志。 试想如果同时有另外一个顾客 C 要在影院 B 买票，那么这两个事务冲突的部分就是语句 2 了。因为它们要更新同一个影院账户的余额，需要修改同一行数据。
    根据两阶段锁协议，不论你怎样安排语句顺序，所有的操作需要的行锁都是在事务提交的时候才释放的。所以，如果你把语句 2 安排在最后，比如按照 3、1、2 这样的顺序，那么影院账户余额这一行的锁时间就最少。这就最大程度地减少了事务之间的锁等待，提升了并发度。

1、 MySQL/InnoDB的加锁过程

Read Committed (RC)
Repeatable Read (RR)

InnoDB的加锁分析前提条件
前提一： 查询列是不是主键？
前提二： 当前系统的隔离级别是什么？
前提三： 查询列上有索引吗？
前提四： 查询列是唯一索引吗？
前提五： 两个SQL的执行计划是什么？索引扫描？全表扫描？

update t1 set update_time = now() where k = 10 ;

组合一： k列是主键，RC隔离级别
组合二： k列是二级唯一索引，RC隔离级别
组合三： k列是二级非唯一索引，RC隔离级别
组合四： k列上没有索引，RC隔离级别
组合五： k列是主键，RR隔离级别
组合六： k列是二级唯一索引，RR隔离级别
组合七： k列是二级非唯一索引，RR隔离级别
组合八： k列上没有索引，RR隔离级别
组合九： Serializable隔离级别

组合一: Read Committed 隔离级别，k列是主键，给定SQL：update t1 set update_time = now() where k = 10; 只需要将主键上 k = 10的记录加上X锁即可

组合二: Read Committed 隔离级别，k列有unique索引，unique索引上的k=10一条记录加上X锁，同时，会根据读取到的列，回主键索引(聚簇索引)，然后将聚簇索引上对应的主键索引项加X锁。

组合三: Read Committed 隔离级别，k列上有索引，那么对应的所有满足SQL查询条件的记录，都会被加锁。同时，这些记录在主键索引上的记录，然后将聚簇索引上对应的主键索引项加X锁。

组合四: Read Committed 隔离级别，若k列上没有索引，SQL会走聚簇索引的全扫描进行过滤，由于过滤是由MySQL Server层面进行的。因此每条记录，无论是否满足条件，都会被加上X锁。但是，为了效率考量，MySQL做了优化，对于不满足条件的记录，会在判断后放锁，最终持有的，是满足条件的记录上的锁，但是不满足条件的记录上的加锁/放锁动作不会省略。同时，优化也违背了2PL的约束。

组合五: Repeatable Read 隔离级别，k列是主键列，给定SQL update t1 set update_time = now() where k = 10; 只需要将主键上 k = 10的记录加上X锁即可。

组合六: Repeatable Read 隔离级别，k列有unique索引，unique索引上的k=10一条记录加上X锁，同时，会根据读取到的列，回主键索引(聚簇索引)，然后将聚簇索引上对应的主键索引项加X锁。

组合七：Repeatable Read 隔离级别，k列有索引， 通过索引定位到第一条满足查询条件的记录，加记录上的X锁，加GAP上的GAP锁，然后加主键聚簇索引上的记录X锁，然后返回；然后读取下一条，重复进行。直至进行到第一条不满足条件的记录，此时，不需要加记录X锁，但是仍旧需要加GAP锁，最后返回结束。

考虑到B+树索引的有序性，满足条件的项一定是连续存放的。如果要插入一条记录，肯定会插入在相同位置，为了保证两次查询查到的值一致，MySQL选择了用GAP锁，将 查询值范围前、查询值范围、查询值范围后 三个GAP给锁起来。

GAP锁的目的，是为了防止同一事务的两次当前读，出现幻读的情况。

| ``` |-----| 1 2 ``` | ``` k | 7 | 8 | 10 | 10 | 40 | 50 | primary id | 1 | 2 | 3 | 4 | 5 | 6 | ``` |

为了保证[8,2]与[10,3]间，[10,3]与[10,4]间，[10,4]与[40,5]不会插入新的满足条件的记录，MySQL选择了用GAP锁，将这三个GAP给锁起来。

组合八： 在Repeatable Read隔离级别下，如果进行全表扫描的当前读，那么会锁上表中的所有记录，同时会锁上聚簇索引内的所有GAP，杜绝所有的并发 更新/删除/插入 操作
聚簇索引上的所有记录，都被加上了X锁。其次，聚簇索引每条记录间的间隙(GAP)，也同时被加上了GAP锁。

组合九：Serializable隔离级别下直接用加锁的方式来避免并行访问。

在RC，RR隔离级别下，都是快照读，不加锁。
Serializable隔离级别，读不加锁就不再成立，所有的读操作，都是当前读。

四、 死锁

当并发系统中不同线程出现循环资源依赖，涉及的线程都在等待别的线程释放资源时，就会导致这几个线程都进入无限等待的状态，称为死锁。

当出现死锁以后，有两种策略：

  1. 一种策略是，直接进入等待，直到超时。这个超时时间可以通过参数 innodb_lock_wait_timeout 来设置。
  2. 另一种策略是，发起死锁检测，发现死锁后，主动回滚死锁链条中的某一个事务，让其他事务得以继续执行。将参数 innodb_deadlock_detect 设置为 on，表示开启这个逻辑。

在 InnoDB 中，innodb_lock_wait_timeout 的默认值是 50s，意味着如果采用第一个策略，当出现死锁以后，第一个被锁住的线程要过 50s 才会超时退出，然后其他线程才有可能继续执行。对于在线服务来说，这个等待时间往往是无法接受的。
可以考虑通过将一行改成逻辑上的多行来减少锁冲突。还是以影院账户为例，可以考虑放在多条记录上，比如 10 个记录，影院的账户总额等于这 10 个记录的值的总和。这样每次要给影院账户加金额的时候，随机选其中一条记录来加。这样每次冲突概率变成原来的 1/10，可以减少锁等待个数，也就减少了死锁检测的 CPU 消耗。

如果你的事务中需要锁多个行，要把最可能造成锁冲突、最可能影响并发度的锁的申请时机尽量往后放。