《MySQL45讲》读书笔记(七)：全局锁，表锁，行锁，间隙锁

发表于 | 分类于
字数统计: 5.7k

此文为极客时间：MySQL实战45讲的 6、7、20 节锁相关部分的总结

一、全局锁

1.概述

全局锁就是对整个数据库实例加锁。通过使用 Flush tables with read lock（FTWRL）语句加锁，此后整个库都会处于只读状态，这时，其他线程的数据定义语句（DDL），数据操作语句（DML）以及更新类事务的提交语句都会被阻塞。

也就是说，加了全局锁以后，其他线程不能对数据增删改，也不能对表增删改。

另外值得一提的是，在此之前，数据库会等待 FTWRL 操作前的所有读写操作完成，事务提交完毕；并且把脏页的数据从缓存刷入磁盘，保证数据的一致性。

全局锁的典型使用场景是对数据库进行整库备份。

2.全局锁的缺陷和解决方案

全局锁有以下问题：

  • 如果你在主库上备份，那么在备份期间都不能执行更新，业务基本上就得停摆；
  • 如果你在从库上备份，那么备份期间从库不能执行主库同步过来的 binlog，会导致主从延迟。

但是，如果不加锁，备份时如果有表的数据发生了变更，那么备份后的数据库就不能保证整个库的表都是在同一个逻辑时间点，可能会导致使用备份恢复数据库的时候，发生类似“我扣款了但是我下的单没了”，或者“我下单了但是没扣款”这样的问题。

根据前文提到的数据库事务相关知识，我们知道，要保证一个事务进行中，其他事务的提交不影响会到这个事务，也就是要求事务执行前后的数据要一致，就需要开启可重复读的隔离级别。根据这个原理，官方自带的逻辑备份工具 mysqldump 当使用参数 –single-transaction 的时候，导出数据之前就会启动一个事务，来确保拿到一致性视图。而且由于 MVCC 的支持，这个过程中数据是可以正常更新的。

single-transaction 虽好，但是对于像 MyISAM 这样不支持事务的引擎而言就享受不到这样的便利，所以还是需要 FTWRL 语句。这也是为什么推荐使用 innodb 引擎的原因之一。

3. 为何不能使用readonly替代

全局锁的效果表现为全库变为只读，但是同样的效果，set global readonly=true 却不是一个合适的替代方案：

  1. global 不宜改动：在有些系统中，readonly 的值会被用来做其他逻辑，比如用来判断一个库是主库还是备库。因此，修改 global 变量的方式影响面更大。
  2. 在异常处理机制上有差异：如果执行 FTWRL 命令之后由于客户端发生异常断开，那么 MySQL 会自动释放这个全局锁，整个库回到可以正常更新的状态。而将整个库设置为 readonly 之后，如果客户端发生异常，则数据库就会一直保持 readonly 状态，这样会导致整个库长时间处于不可写状态，风险较高。
  3. 无法限制超管操作：只会禁止普通用户权限的 mysql 写操作，不能限制 super 权限用户的写操作。

二、表级锁

表级锁是针对数据库表的锁，分为两种：表锁和元数据锁（MDL）。

1.表锁

表锁的语法是lock tables … read/write他与全局锁类似，也可以通过unlock tables提前释放锁，也可以在客户端断开连接的时候自动释放锁。

如语法所说，表锁区分读锁和写锁，与全局锁不同的是，表锁除了限制别的线程的操作外，也会限制本线程的操作：

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

2.元数据锁

元数据锁跟表锁相比，最显著的差别在于元数据锁不需要显式的调用，在访问表的时候会自动加上。

元数据锁的作用在于保证读写的正确性。假设在没有加表锁的情况下，一个线程对另一个线程正在查询的表结构做了修改，那么就会导致查询出了结构不正确的数据。为此 mysql 在5.5版本中引入了 MDL：当读的时候加 MDL 读锁，写的时候加 MDL 写锁。

申请 MDL 锁的操作会形成一个队列，队列中写锁获取优先级高于读锁。一旦出现写锁等待，不但当前操作会被阻塞，同时还会阻塞后续该表的所有操作。事务一旦申请到 MDL 锁后，直到事务执行完才会将锁释放。如果事务中包含 DDL 操作，mysql 会在 DDL 操作语句执行前，隐式提交commit，以保证该DDL语句操作作为一个单独的事务存在，同时也保证元数据排他锁的释放。

换句话说，元数据锁最主要的目的不是保证 select 与 update 之间的正确进行，而是保证 DDL 语句 和 DML/DQL 语句之间的正确进行。

其中：

  1. 读锁与读锁之间不互斥，因此可以同时有多个线程对同一张表进行查询；
  2. 读锁之写锁之间是互斥的，并且写锁获取优先级高，这可以避免一次事务的两次查询查到不一样的表结构；
  3. 写锁与写锁之间是互斥的，这可以用来保证要更改表结构的操作的安全性；

第二点很好理解，但是第一点可能有点难理解，我们举个例子：

假如A和B线程同时修改T表的两行数据，A 线程先分别获取 MDL 读锁和 MDL 写锁，然后 B 线程获取 MDL 读锁，但是 B 线程无法获取MDL写锁，所以 B 线程进入等待，等到 A 线程完成后B线程才获取写锁，继续操作。

3.元数据锁存在的问题

事务中的 MDL 锁，在语句执行开始时申请，但是语句结束后并不会马上释放，而会等到整个事务提交后再释放。所以可能会出现这样的问题：

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

针对以上的情况，我们有以下的改善方式：

  1. 假如不是热点表：如果要修改的表有长事务要进行，要么 kill 掉长事务，要么先暂停表结构的修改；
  2. 假如是热点表：为 alert 语句加上等待时间，避免长时间的阻塞后面的语句。

三、行锁

1.概述

顾名思义，行锁的作用是保证对同一行数据的并发修改可以正确进行。

假设我们有这样一条sql：

1
2
3
4
begin;
update t set a = 2 where id =  1;
update t set a = 3 where id =  2;
commit;

这个过程是这样的：

  1. 开启了一个事务；
  2. 第一条 sql 获得了 id = 1 这一行的行锁，执行完后不释放；
  3. 第二条 sql 获得了 id = 2 这一行的行锁，但是执行完也不释放；
  4. 提交事务，并且释放所有的行锁。

我们可以看到，行锁是需要的时候就加上，但是更新完后并不会立刻释放，而是等到事务提交后再统一释放。这就是行锁的两阶段锁。

基于两阶段锁的原理，对于一些 sql ，我们可以做一些优化，比如：

1
2
3
4
5
begin;
update t set a = 2 where id =  1;
update t set a = 3 where id =  2;
select * from t;
commit;

上面这条 sql 涉及到两个 update 的 sql，一条 select 的 sql。如果可能的话，我们可以把 select 的操作放到最前面，这样就能减少两条 update 的 sql 获取行锁的时间。

2.死锁与死锁处理策略

在学多线程的时候不难理解死锁的概念，两个线程互相持有对方所需要的锁就会导致死锁。我们拿数据库中的死锁举例：

image-20201022134524700

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

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

  1. 直接进入等待，直到超时。

    这个超时时间可以通过参数 innodb_lock_wait_timeout 来设置。

  2. 发起死锁检测，主动回滚死锁链条中的某一个事务，让其他事务得以继续执行。

    将参数 innodb_deadlock_detect 设置为 on，表示开启这个逻辑。

设置超时时间看上去是个好选择，但是实际上这个时间很难把握。innodb 的默认超时时间是50s，这个时间几乎是不可能接受的。但是如果将时间设置果断，可能又会导致正常的等待锁释放的过程受到影响。

因此，还是需要采用第二种策略，死锁检测。这个也是 innodb 默认开启的。死锁检测的问题在于检测过程也是一个非常消耗性能的过程。当多个事务同时更新一条数据的时候，每个线程都要把所有争夺这把锁的线程检测一遍。这也是有时候 cpu 利用率极高，但是每秒仍然执行不了几个事务。

3.死锁检测的优化

说明的是，并非所有的访问都需要加锁，对于读和一般不太可能出现死锁的情况是不会进行检测的，比如：

B在等A，D在等C，现在来了一个E，发现E需要等D，那么E才判断跟D、C是否会形成死锁，这个检测不用管B和A

死锁检测的处理过程是很难优化的，我们只能想办法避免这个过程。为此，我们有以下几种方式来优化死锁检测：

  • 关掉不必要是死锁检测。如果你能确保这个业务一定不会出现死锁，可以临时把死锁检测关掉。但是这种操作本身带有一定的风险，因为业务设计的时候一般不会把死锁当做一个严重错误，毕竟出现死锁了，就回滚，然后通过业务重试一般就没问题了，这是业务无损的。而关掉死锁检测意味着可能会出现大量的超时，这是业务有损的；
  • 在客户端做控制并发度。即控制客户端的并发线程。但是这个方法并不可靠，因为客户端的数量并不可控，即使每个客户端只允许存在三个并发线程，一旦客户端达到1000个，最后还是会可能达到3000个的并发线程；
  • 在服务端做并发控制。如果你有中间件，可以考虑在中间件实现；如果你的团队有能修改 MySQL 源码的人，也可以做在 MySQL 里面。基本思路就是，对于相同行的更新，在进入引擎之前排队。这样在 InnoDB 内部就不会有大量的死锁检测工作了。但是这个方案有很高的技术要求。
  • 分散锁冲突。即在逻辑上把一行数据改成多行数据，比如对一个总金额字段改为多个金额字段的和。这样在操作的时候锁冲突就会由一个锁分散到多个锁。但是这个方案需要修改业务逻辑，也涉及到一些其他代码的改动。

4.行锁的锁定范围

虽然叫行锁，但是锁并不是只锁一条数据，有些时候行锁会锁住多条数据，在一些没指明主键的情况，行锁会直接转为表锁。

我们举个例子:

假设有个表单 products ，里面有 id 跟 name 二个字段。

  1. 明确指定主键，并且有能查到，加行锁

    1
    2
    SELECT * FROM products WHERE id='3' FOR UPDATE;
    SELECT * FROM products WHERE id='3' and type=1 FOR UPDATE;

  2. 明确指定主键，不能查到，不加锁

    1
    SELECT * FROM products WHERE id='-1' FOR UPDATE;

  3. 不指明主键，加表锁

    1
    SELECT * FROM products WHERE name='Mouse' FOR UPDATE;

  4. 主键不明确，加表锁

    1
    2
    SELECT * FROM products WHERE id<>'3' FOR UPDATE;
    SELECT * FROM products WHERE id LIKE '3' FOR UPDATE;

实际上，不难看出，如果没有使用索引，行锁很容易就会上升到表锁。

四、间隙锁

1.行锁的局限性

假设有表 t，t 有 id 和 d 两个字段。现有数据（0，5）和（2，1）。

我们开启一个事务，加上写锁，进行了三次查询，这样每次都是“当前读”;

1
2
3
4
5
6
begin;
update t set id = 10 where d = 5
select * from t where d = 5 for update; # Q1
select * from t where d = 5 for update; # Q2
select * from t where d = 5 for update; # Q3
commit;

其中，在 Q1 和 Q2 的间隙有一个事务进行了更新：

1
update t set d = 5 where id = 2; # Q4

在 Q2 和 Q3 的间隙进行了一次插入：

1
insert into t values(3,5); # Q5

现在 sql 的执行顺序是：Q1 -> Q4 -> Q2 -> Q5 -> Q3

在这种情况下，三条查询的 sql 分别查询出了一条、两条、三条数据：

  • Q1：查到了（10，5）
  • Q2：查到了（0，5），（2，5）
  • Q3：查到了（0，5），（2，5），（3，5）

其中，Q3 查到了一条原本不存在的数据，也就是幻读。

我们可以看到，虽然在事务一开始的时候就已经拿到了（0，5）的行锁，但是由于没有锁住其他的行，导致最后在 binlog 里出现了严重的数据不一致问题：

1
2
3
update t set d = 5 where id = 2;
insert into t values(3,5);
update t set id = 10 where d = 5 # 最后事务提交的时候行锁才释放

也就是说，当从库同步的时候，或者通过 binlog 备份的时候，原本在数据中的数据，应该只有（0，5）这行的 id 被改成了5，但是最后备份的时候（2，5）和（3，5）的 id 也要被改成了5，先不说是否能成功，这种情况显然是不允许出现的。

由于没锁住其他的行，导致了 binlog 上因为顺序导致的数据不一致问题，那么如果把所有扫描到的行都加上锁，那么 Q1 和 Q2 之间的更新就会被放到 Q3 执行事务提交完后，反应到 binlog：

1
2
3
insert into t values(3,5);
update t set id = 10 where d = 5 # 最后事务提交的时候行锁才释放
update t set d = 5 where id = 2; # 拿到写锁后才能更新

我们可以看到，及时这样，insert 语句还是会引起 binlog 上的数据不一致，最根本的原因在于，加锁的时候（3，5）这行还不存在，所以锁加不到这行数据上。这就是幻读引起的问题。

因此，需要一种新的锁，来锁住行与行的间隙，来防止意外的插入，这就是间隙锁。

2.间隙锁

行锁只能锁住行，但是新插入记录这个动作，要更新的是记录之间的“间隙”，所以可能会出现幻读的情况。因此，为了解决这个问题，InnoDB 只好引入新的锁，也就是间隙锁 (Gap Lock)。

顾名思义，间隙锁（next-key lock），锁的就是两个值之间的空隙。

在最开始，表 t 一共有两条（0，5），（2，5）数据，那么就有三个前开后闭的区间/间隙（-∞，0 ]，（0，2 ]，（2，+ supremum )。其中，supremum 是 innodb 允许的索引最大值，这是一个概念上的数，可以认为是正无穷。

当我们执行第 select * from t whrere d = 5 for update的时候，除了为扫描到的两条数据加上行锁外，还会为这三个区间加上间隙锁。

3.间隙锁引起死锁问题

间隙锁跟间隙锁之间是不冲突的，有冲突的是往间隙插入值的行为。也就是说，同样的一个间隙，两条 sql 分别加上了间隙锁，这样就很可能引起死锁。

举个例子：

在一开始的时候，表有（0，5）和（2，1）这两条数据，sessionA 执行了：

1
2
3
4
begin;
select * from t where id = 1 for update; # Q1
insert into t values(1,1) # Q2
commit;

sessionB 在 sessionA 开始事务后也开启了事务：

1
2
3
4
begin;
select * from t where id = 1 for update; # Q3
insert into t values(1,1) # Q4
commit;

现在，语句的执行顺序的：Q1 -> Q3 -> Q4 -> Q2。

在这种情况下，不等到 Q4 执行，就已经形成死锁了：

  1. session A 执行 Q1 语句，由于 id=1 这一行并不存在，因此会加上间隙锁 (0,2);
  2. session B 执行 Q3 语句，同样会加上间隙锁 (5,12)，间隙锁之间不会冲突，因此这个语句可以执行成功；
  3. session B 试图插入 Q4，被 session A 的间隙锁挡住了，只好进入等待；
  4. session A 试图插入一行 Q2，被 session B 的间隙锁挡住了。

至此，两个 session 进入互相等待状态，形成死锁。当然，InnoDB 的死锁检测马上就发现了这对死锁关系，让 session A 的 insert 语句报错返回了。

间隙锁在解决了一些问题的情况下，又引入了一些新的问题，所以我们也有其他的选择，并非一定需要引入间隙锁：

隙锁是在可重复读隔离级别下才会生效的。所以，你如果把隔离级别设置为读提交的话，就没有间隙锁了。但同时，你要解决可能出现的数据和日志不一致问题，需要把 binlog 格式设置为 row。这也是现在不少公司使用的配置组合。

读提交每次每次 sql 执行的时候都是当前读，所以影响数据的一致性；而 binlog 设置为 row 以后记录的就是每一条数据的变化，因而备份的时候不会导致数据不一致。

五、总结

根据加锁的范围，MySQL 里面的锁大致可以分成全局锁、表级锁和行锁三类，其中针对行锁难以解决的幻读问题，又添加了间隙锁。

全局锁：

  1. 通过 Flush tables with read lock（FTWRL）语句加锁，可以主动释放或者在客户端断开连接时释放；
  2. 会限制其他线程的 DDL 和 DML 操作，但不会限制自己的；
  3. 一般用于 MyISAM 之类不支持事务的引擎进行整库备份。若 innodb 的话，可以开启事务为可重复读，接着 MVCC 实现无全局锁备份。

表级锁：

  1. 分为表锁和元数据锁，两者都区分读锁和写锁；
  2. 表锁：
    • 表锁和全局锁一样，可以主动释放或者在客户端断开连接时释放；
    • 表锁是显式使用的，不但限制其他线程对表的操作，也会限制本线程对表的操作；
  3. 元数据锁：
    • 是一种典型的读写锁，每一条 DDL 都会自动加上元数据锁；
    • 作用是确保 DDL 和 DML/DQL 语句的正确执行；
    • 元数据锁可能会因为无法获取锁而引起阻塞，最好的办法是避免长事务和 DDL 的同时存在，对于热点数据由于可能存在频繁的事务开启，所以最好为 DDL 语句添加超时时间，再无法修改表结构的情况下避免阻塞。

行级锁：

  1. 存在两阶段锁的机制：即需要就先获取锁，而使用后不释放锁，直到事务结束再统一释放锁。

    因此，优化思路是在执行 sql 的时候把需要获取行锁的语句放在后面，避免过长时间的占用锁；

  2. 可能会存在死锁问题，处理策略有两种：

    • 调整超时时间。innodb_lock_wait_timeout 默认为50s。但是调长容易引起阻塞，调短可能影响正常的锁等待；
    • 死锁检测。每次获取锁都检测是否有死锁，有就回滚其中一个事务。innodb_deadlock_detect 是默认开启的，但是容易死锁检测在并发线程多的情况下非常消耗性能。

  3. 死锁检测的优化思路有四种：

    • 关掉不必要是死锁检测。可能引起大量超时，导致操作丢失；
    • 在客户端做控制并发度。限制客户端线程并发数，但是无法限制客户端数量，所以不是很有效；
    • 在服务端做并发控制。使用中间件或者修改 mysql 源码，让争夺锁的线程进入等待队列；
    • 分散锁冲突。将一个字段在逻辑上分为多个字段，避免多个线程争夺同一把锁。

  4. 锁同时存在的情况下，并不是只要满足粒度最小的锁不互斥就可以了，而是必须满足全部全部不互斥才行。

间隙锁：

  1. 锁索引之间的间隙，是前开后闭的区间；
  2. 间隙锁之间不冲突，但是插入间隙的行为是冲突的，所以当两个事务同时为一个间隙加锁容易引起死锁；
  3. 能解决幻读，但不是必须的。读已提交 + 把 binlog 格式设置为 row 也可以防止幻读。
0%