MySQL「09」锁

Samson_bu
131 阅读11分钟

开启掘金成长之旅！这是我参与「掘金日新计划 · 12 月更文挑战」的第09天，点击查看活动详情

MySQL 服务端程序会同时处理多个客户端提交的 SQL 语句。 当出现并发访问时，服务端需要合理地控制资源访问，锁就是实现这一目的的数据结构。

根据加锁的范围，MySQL 中的锁可以分为三类：

  1. 全局锁
  2. 表级锁
  3. 行锁

01-全局锁

顾名思义，全局锁是对整个数据库实例加锁。

MySQL 提供了一个加全局读锁的命令：

FLUSH TABLES WITH READ LOCK

该命令会使当前数据库实例变成只读状态，即数据更新语句（增删改）、数据定义语句（DDL修改）、更新类事务的提交语句将会被阻塞。

释放全局锁：

UNLOCK TABLES

全局锁的一个典型应用场景是，做全局逻辑备份。 暂停数据库更新，全部数据导出后再恢复。 这样做的缺点也很明显：

  • 对于主库来说，数据库实例只读会使涉及更新的业务停摆；
  • 对于从库来说，数据库实例不能执行从主库同步过来的 binlog，导致主从延迟。

一种比设置整个数据实例只读的方式进行逻辑备份的方式是，借助 MVCC 创建一致性读视图。 此后，数据库可以继续更新，根据一致性读视图读取的数据也不会出现不一致。 MySQL 官方提供的 mysqldump 就可以通过 -single-transaction 在导数据之前开启一个一致性读视图。

但是 MVCC 不是所有的数据库引擎都支持，例如 MyISAM 这种不支持事务的存储引擎，就无法使用 mysqldump 的上述特性。 此时，为了不破坏备份数据的一致性，只能使用 FLUSH TABLES WITH READ LOCK 这种方式了。

将数据库设置为全局只读，还有另外一种方式，即 set global readonly = true，将 read_only 设置为全局开启。 但不建议这样做，有两点原因：

  1. readonly 会被用作其他的逻辑，例如判断主库、备库，所以不建议使用。
  2. 异常处理机制上与全局读锁有差异，客户端异常断开时，MySQL 会自动释放全局读锁；readonly 设置后，即使客户端发生异常断开，数据库也是只读状态，除非有其他客户端关闭只读设置。

02-表级锁

MySQL 中表级锁分为两种：

  1. 表锁
  2. 元数据锁

02.1-表锁

语法：

LOCK TABLES ... READ/WRITE

可使用 UNLOCK TABLES 释放锁，也可在客户端断开连接时自动释放锁。

表锁除了会限制别的线程读、写，也会限制当前线程后续的操作。 例如，

LOCK TABLES t1 READ, t2 WRITE;

会限制其他线程写 t1 表，读写 t2 表； 也会限制当前线程，只能读 t1 表，读写 t2 表，当前线程无法访问其他表，也不可写 t1 表。

02.2-元数据锁

元数据锁（Metadata Lock，MDL）自 MySQL 5.5 版本引入，其规则如下：

  • 对一个表格进行查询时，会对该表加 MDL 读锁；
  • 对表格结构变更时，会加 MDL 写锁。
  • 读锁、读锁之间不影响，读锁、写锁之间，写锁、写锁之间互斥。

MDL 锁在语句执行开始时申请，在事务结束后释放，这一过程由系统自动完成。 考虑如下场景，假设有一个长事务 TRX，它对表 T1 查询时申请到了 T1 的 MDL 读锁，但由于事务未提交，所以读锁一致没释放。 此时，其他会话尝试对表 T1 的表结构进行修改，尝试获取 T1 的写锁，会导致执行该会话的线程 thread1 被阻塞。 然后，thread1 阻塞还会导致其他尝试获取 T1 MDL 读锁的线程阻塞，导致 T1 这个表格在 TRX 完成之前完全不可读写。 而且，如果客户端有重试机制，再重启一个会话尝试请求，会导致服务端线程资源很快就耗尽了。

为什么写锁会阻塞后续的读锁请求？这是因为申请 MDL 锁的操作会形成一个队列，队列中写锁获取优先级高于读锁，一旦出现 MDL 写锁等待，会阻塞后续该表的所有 CRUD 操作。

基于上述讨论，如何安全地为一个小表（体量较小）增加字段呢？

  1. 首先，需要解决长事务问题，如果长事务一致结束，MDL 锁就一直不会释放。 可以从 information_schema.innodb_trx 表格中获取当前正在执行的事务，可以等待事务结束在进行 DDL 变更，或直接 kill 正在执行的事务。 但这种方式对热点表不适用。热点表是指，可能其数据量不多，但是被访问的却十分频繁，所以即使 kill 掉，也会有新的事务开始。
  2. 其次，针对热点表，增加字段的合理机制是：在 DDL 修改语句中增加等待机制，在一段时间内能够获得 MDL 锁最好，如果没获得则在一段时间后重试。 这种机制相当于是一个碰运气的行为，等到恰好能够获得 MDL 写锁时进行 DDL 修改。

02.3-其他表级锁

意向锁（Intention Lock）主要用于快速检测是否存在行锁，同样氛围两类：

  • 意向共享锁，简称 IS 锁，对某条记录加 S 锁（后面会介绍）时，会先对其所在表加 IS 锁。
  • 意向排它锁，简称 IX 锁，对某条记录加 X 锁时，会先对其所在表加 IX 锁。

AUTO-INC 锁是 InnoDB 在为声明为 AUTO_INCREMENT 的主键赋值时使用的锁。 它是一个特殊的表级锁，而且并不是在事务提交后才释放，而是执行完插入语句后就会立即释放。 高并发场景下，大量 INSERT 数据会由于 AUTO-INC 锁的存在而导致并发度降低。 所以从 MySQL 5.1.22 版本开始，AUTO-INC 使用了一种更轻量级的锁实现，在 AUTO_INCREMENT 变量值增加以后，AUTO-INC 轻量级锁就被释放了，而不用等到整条语句插入完成。 是否使用轻量级锁可通过变量 innodb_autoinc_lock_mode 来控制：

  • 值为0时，表示不使用轻量级实现。
  • 值为1时，根据语句的不同，而选择不同的方式。 例如，普通插入语句采用轻量级实现（插入语句在执行前就可以确定具体要插入多少条记录），而 insert ... select 之类的语句（无法预计即将插入记录的数量），采用非轻量级实现。
  • 值为2时，表示使用轻量级锁实现。

由此可见，innodb_autoinc_lock_mode = 2 时，性能最好。

03-行锁

MySQL 中的行锁是放在存储引擎中实现的，所以并不是所有的引擎都支持行锁，例如 MyISAM 就不支持行锁。 InnoDB 支持行锁，这也是 InnoDB 替换 MyISAM 成为默认数据库引擎的重要原因之一。 如果存储引擎不支持行锁，并发控制时就只能使用表级锁，所以同一张表上最多只能有一个更新在执行，并发度比较低。

行锁分为两种类型：

  • 共享锁，shared lock，简称 S 锁。事务在读取某条记录时，需要先获得该记录的 S 锁。
  • 排它锁，exclusive lock，也称独占锁，简称 X 锁。事务在修改某条记录时，需要先获得该记录的 X 锁。

对某条记录加锁时，会先检查内存中是否存在与该记录相关联的锁结构。 锁结构中包含两个重要的信息：

  • trx_id 事务 ID，表示该锁由哪个事务创建。
  • is_waiting 标识符，表示当前事务是否在等待锁。值为 false 时，说明当前事务持有锁；为 true 时，表示等待锁，当前事务被阻塞。

我们以排它锁为例，简单介绍下请求加锁、阻塞，然后唤起的过程。 假设有两个事务 T1\T2，它们都想修改记录 R1。 T1 先检查内存中是否存在与 R1 相关联的锁结构，假设此时不存在，则创建一个锁结构，并将其 trx_id 设置为 T1，然后将 is_waiting 设置为 false。 然后，T2 尝试对 R1 修改，它先检查内存中是否存在锁结构，发现存在。则创建一个锁结构，并将其 trx_id 设置为 T2，然后将 is_waiting 设置为 true。 当 T1 结束后，将对应的锁结构释放，发现有等待的其他锁结构，则将它的 is_waiting 设置为 false，唤醒阻塞的事务。

03.1-读取记录时加锁操作

对读取的记录加 S 锁的语法如下：

SELECT ... LOCK IN SHARE MODE;

对读取的记录加 X 锁的语法如下：

SELECT ... FOR UPDATE;

03.2-修改记录时加锁操作

修改记录一般指 INSERT、DELETE、UPDATE。

  • INSERT，插入记录时一般不需要加锁，InnoDB 中设计了一种隐式锁，主要靠记录中的 trx_id 实现。
  • DELETE，删除一条记录时，先在 B+ 树中定位到记录的位置，然后执行 delete mark 操作。可以看作是一个获取该记录的 X 锁的锁定读。
  • UPDATE，更新一条记录时，分三种情况：
    1. 修改的列，占用空间不变化。可以看作是一个获取 X 锁的锁定读。
    2. 修改的列，占用空间发生变化（增大、减小）。先定位到记录，这个过程看作是获取 X 锁的锁定读。将记录彻底删除后，再插入一个新记录，这个过程看作是隐式锁。
    3. 修改的列的主键发生变化。相当于原记录删除后，再新插入一条记录。看作是 DELETE 和 INSERT 的组合。

03.3-常见的行级锁类型

常见的行级锁有如下几种类型：

  1. Record Lock，锁加在记录上。LOCK_REC_NOT_GAP

  2. Gap Lock，间隙锁，锁加在一个范围上，但不包括记录本身。LOCK_GAP 间隙锁仅仅是为了防止插入幻影记录而提出。 虽然存在 S 间隙锁、X 间隙锁，但它们的作用都是相同的。 间隙锁虽然不是加在某个记录上的，但与该记录是相关联的。 怎么理解这句话呢？例如有两条记录，它们的主键分别为 id1、id2，我们在 id2 这条记录上加间隙锁 gap_lock，则它表示向 (id1, id2) 内插入记录不能立即执行。 但是 gap_lock 不影响对 id2 加 record lock。

  3. Next-Key Lock，与 Gap Lock 搭配使用，锁定一个范围，加上记录本身。 next-key lock 是前面两种锁的组合。

  4. Insert Intention Lock，插入意向锁。 从前面的2、3的描述中可知，当我们向某个间隙锁、next-key 锁锁定的范围内插入记录时，需要等待间隙锁、next-key 锁持有事务提交后才可插入。 结合前面介绍行锁时锁结构时提到的，因申请锁而阻塞的，也需要在内存中创建一个锁结构。 所以，尝试向间隙锁、next-key 锁锁定的范围内插入记录，会生成一个锁结构，其 trx_id 为尝试插入记录的事务 id，is_waiting 值为 true，锁类型为插入意向锁。

在事务中，行锁在需要时才请求，但仅在事务提交时才会被释放。这就是两阶段锁协议。 有了这个协议，如果在某个事务中需要锁多行，则需要考虑将最可能造成锁冲突、最可能影响并发度的锁尽可能向靠近 commit 的地方放，为的是尽量晚得请求锁，尽量快得释放锁。

03.4-死锁和死锁检测

死锁是并发控制中常见的问题，描述的是并发的线程之间互相等待对方释放资源的状态。 在 MySQL 中，遇到死锁后一般有两种处理策略：

  1. 直接等待，直到超时。等待时间可以通过参数 innodb_lock_wait_timeout 控制，默认时间50s。
  2. 发起死锁检测。检测到死锁后，主动回滚死锁链条中的某一事务，使得其他事务得以执行。此功能在 innodb_deadlock_detect 值为 ON 时开启。

死锁检测有额外的代价，如果大量并发线程更新同一行时，每个被堵住的线程都要判断是否是自己的加入导致了死锁，这是一个 O(n) 复杂度的操作。 1000个并发线程，需要100万量级的检测，如果最终结果是没有死锁，会浪费大量 CPU 资源。 导致的现象就是，虽然 CPU 时间占满，但是每秒却执行不了几个事务。

avatar
文章
阅读
粉丝