数据库核心技术深度剖析：事务、索引、锁与SQL优化实战指南（第三节）----从全局锁到行锁的全面总结

Introduction：收纳技术相关的数据库知识 事务、索引、锁、SQL优化 等总结！

数据库锁

数据库锁可以按粒度、加锁算法、加锁策略、兼容性和其它等方面进行分类：

• 锁粒度

  • 行锁
    • 锁的是 某⾏数据 或 ⾏之间的间隙。由某些存储引擎实现，如InnoDB
    • 开销大，加锁慢；会出现死锁；锁定粒度小，发生锁冲突的概率低，并发度高
  • 表锁
    • 锁的是某个 table。由MySQL的SQL layer层实现的
    • 开销小，加锁快；不会出现死锁；锁定力度大，发生锁冲突概率高，并发度最低
  • 页锁
    • 开销和加锁速度介于表锁和行锁之间；会出现死锁；锁定粒度介于表锁和行锁之间，并发度一般
  • 全局锁
    • 锁的是整个 database。由MySQL的SQL layer层实现的

• 加锁算法

  • Record Lock（记录锁）
  • Gap Lock（间隙锁）
  • Next-Key Lock（临键锁）

• 加锁策略

  • 悲观锁
  • 乐观锁

• 兼容性

  • 排它锁
  • 共享锁
  • 意向锁

• 其它锁

  • 自增锁（AUTO-INC锁）

根据不同的存储引擎，MySQL中锁的特性可以大致归纳如下：

存储引擎	行锁	表锁	页锁
InnoDB	√	√
MyISAM		√
BDB	√	√	√

在 InnoDB 存储引擎中：

• SELECT 操作的不可重复读问题通过 MVCC 得到了解决
• UPDATE、DELETE 的不可重复读问题通过 Record Lock (记录锁)解决
• INSERT 的不可重复读问题是通过 Next-Key Lock (临键锁)解决

全局锁(Global-Level)

全局锁就是对整个数据库实例加锁。MySQL 提供了一个加全局读锁的方法，命令是Flush tables with read lock (FTWRL)。 当你需要让整个库处于只读状态的时候，可以使用这个命令，之后其他线程的以下语句会被阻塞：

• 数据更新语句（数据的增删改）
• 数据定义语句（包括建表、修改表结构等）
• 更新类事务的提交语句

使用场景

• 典型使用场景是做全库逻辑备份（mysqldump）

数据库只读状态的危险性

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

全局锁两种方法：

• FLUSH TABLES WRITE READ LOCK（FTWRL）
• set global readonly=true

一般建议使用 FTWRL 方式，因为：

• 有些系统中 readonly 的值会被用来做其它逻辑。如判断一个库是主库或备库。因此修改 global 变量的方式影响面更大
• 异常处理机制上有差异：
  • 如果执行FTWRL后由于客户端发生异常断开，则MySQL会自动释放这个全局锁，整个库回到可正常更新状态
  • 将库设置为readonly后，若客户端发生异常，则数据库会一直保持readonly状态，导致整库长时间处于不可写状态
  • readonly 对super用户权限无效

全局读锁(FTWRL)

为什么需要全局读锁(FTWRL)？

当 mysqldump 使用参数 --single-transaction 的时候，导数据之前就会启动一个事务，来确保拿到一致性快照视图。而由于 MVCC 的支持，这个过程中数据是可以正常更新的。所以，single-transaction 方法只适用于所有的表使用事务引擎的库。如果有的表使用了不支持事务的引擎，那么备份就只能通过 FTWRL 方法。这往往是 DBA 要求业务开发人员使用 InnoDB 替代 MyISAM 的原因之一。

要使用全局锁，则要执行这条命：

flush tables with read lock

执行后，整个数据库就处于只读状态了，这时其他线程执行以下操作，都会被阻塞：

• 对数据的增删查改操作，比如 insert、delete、update等语句；
• 对表结构的更改操作，比如 alter table、drop table 等语句。

如果要释放全局锁，则要执行这条命令：

unlock tables

当然，当会话断开了，全局锁会被自动释放。

set global readonly=true

表级锁(Table-Level)

表级锁是MySQL中锁定粒度最大的一种锁，表示对当前操作的整张表加锁，它实现简单，资源消耗较少，被大部分MySQL引擎支持。最常使用的MyISAM与InnoDB都支持表级锁定。表级锁分为：

• 表共享读锁（共享锁）
• 表独占写锁（排他锁）

特点：开销小、加锁快、不会出现死锁、发生锁冲突的概率最高、并发度也最低。

表锁

表锁除了会限制别的线程的读写外，也会限制本线程接下来的读写操作。

如果我们想对学生表（t_student）加表锁，可以使用下面的命令：

-- 表级别的共享锁，也就是读锁
lock tables t_student read;
-- 表级别的独占锁，也就是写锁
lock tables t_stuent wirte;

不过尽量避免在使用 InnoDB 引擎的表使用表锁，因为表锁的颗粒度太大，会影响并发性能，InnoDB 的优势在于实现了颗粒度更细的行级锁。要释放表锁，可以使用下面这条命令，会释放当前会话的所有表锁：

unlock tables

元数据锁(MDL)

MDL作用是防止DDL和DML并发的冲突，保证读写的正确性。元数据锁是为了保证当用户对表执行 CRUD 操作时，防止其它线程对这个表结构做了变更。不需要显示的使用 MDL，因为当我们对数据库表进行操作时，会自动给这个表加上 MDL。

• 对一张表进行 CRUD 操作时，加的是 MDL 读锁

  当有线程在执行 select 语句（ 加 MDL 读锁）的期间，如果有其他线程要更改该表的结构（ 申请 MDL 写锁），那么将会被阻塞，直到执行完 select 语句（ 释放 MDL 读锁）。

• 对一张表做结构变更操作的时候，加的是 MDL 写锁

  当有线程对表结构进行变更（ 加 MDL 写锁）的期间，如果有其他线程执行了 CRUD 操作（ 申请 MDL 读锁），那么就会被阻塞，直到表结构变更完成（ 释放 MDL 写锁）。

MDL 是在事务提交后才会释放，这意味着事务执行期间，MDL是一直持有的。申请 MDL 锁的操作会形成一个队列，队列中写锁获取优先级高于读锁，一旦出现 MDL 写锁等待，会阻塞后续该表的所有 CRUD 操作。

意向锁(Intention Lock)

意向锁是表级锁，是InnoDB主动加的，不需要手动处理。其目的是为了快速判断表里是否有记录被加锁。

• 如果没有意向锁，那么加独占表锁时，就需要遍历表里所有记录，查看是否有记录存在独占锁，这样效率会很慢
• 如果有了意向锁，在对记录加独占锁前，先会加上表级的意向独占锁，那么在加独占表锁时，直接查该表是否有意向独占锁，如果有就意味着表里已经有记录被加了独占锁，这样就不用去遍历表里的记录

对于insert、update、delete，InnoDB会自动给涉及的数据加排他锁（X）；对于一般的Select语句，InnoDB不会加任何锁，事务可以通过以下语句给显示加共享锁或排他锁。

• 共享锁：SELECT ... LOCK IN SHARE MODE;
• 排他锁：SELECT ... FOR UPDATE;

意向共享锁（Intention Shared Lock）

意向共享锁（IS锁）是指当事务准备在某行记录上加共享锁（S锁）时，需要先在表级别加一个IS锁。

作用：通知数据库接下来需要施加什么锁并对表加锁。如果需要对记录A加共享锁，那么此时innodb会先找到这张表，对该表加意向共享锁之后，再对记录A添加共享锁。

意向排他锁（Intention Exclusive Lock）

意向排他锁（IX锁）是指当事务准备在某行记录上加排他锁（X锁）时，需要先在表级别加一个IX锁

作用：通知数据库接下来需要施加什么锁并对表加锁。如果需要对记录A加排他锁，那么此时innodb会先找到这张表，对该表加意向排他锁之后，再对记录A添加排他锁。

AUTO-INC锁(自增长锁)

在为某个字段声明 AUTO_INCREMENT 属性时，之后可以在插入数据时，可以不指定该字段的值，数据库会自动给该字段赋值递增的值，这主要是通过 AUTO-INC 锁实现的。

AUTO-INC 锁是特殊的表锁机制，锁不是在一个事务提交后才释放，而是在执行完插入语句后就会立即释放。在插入数据时，会加一个表级别的 AUTO-INC 锁，然后为被 AUTO_INCREMENT 修饰的字段赋值递增的值，等插入语句执行完成后，才会把 AUTO-INC 锁释放掉。

AUTO-INC 锁在对大量数据进行插入的时候，会影响插入性能，因为另一个事务中的插入会被阻塞。因此， 在 MySQL 5.1.22 版本开始，InnoDB 存储引擎提供了一种轻量级的锁来实现自增。一样也是在插入数据的时候，会为被 AUTO_INCREMENT 修饰的字段加上轻量级锁，然后给该字段赋值一个自增的值，就把这个轻量级锁释放了，而不需要等待整个插入语句执行完后才释放锁。

InnoDB 存储引擎提供了个innodb_autoinc_lock_mode的系统变量，是用来控制选择用 AUTO-INC 锁，还是轻量级的锁。

• innodb_autoinc_lock_mode=0：采用AUTO-INC锁
• innodb_autoinc_lock_mode=2：采用轻量级锁。性能最高，但自增长值可能不是连续的，在主从复制场景中是不安全的
• innodb_autoinc_lock_mode=1：默认值，两种锁混着用
  • 如果能够确定插入记录的数量就采用轻量级锁
  • 不确定时就采用 AUTO-INC 锁

页级锁(Page-Level)

页级锁是 MySQL 中锁定粒度介于行级锁和表级锁中间的一种锁。表级锁速度快，但冲突多，行级冲突少，但速度慢。因此，采取了折中的页级锁，一次锁定相邻的一组记录。BDB 支持页级锁。

特点：开销和加锁时间界于表锁和行锁之间、会出现死锁、锁定粒度界于表锁和行锁之间、并发度一般。