MySQL实战 | 06/07 简单说说MySQL中的锁

原文链接：MySQL实战 | 06/07 简单说说MySQL中的锁

本文思维导图：https://mubu.com/doc/AOa-5t-IsG

锁是计算机协调多个进程或纯线程并发访问某一资源的机制。

在数据库中，除传统的计算资源（CPU、RAM、I/O）的争用以外，数据也是一种供许多用户共享的资源。

如何保证数据并发访问的一致性、有效性是所在有数据库必须解决的一个问题，锁冲突也是影响数据库并发访问性能的一个重要因素。从这个角度来说，锁对数据库而言显得尤其重要，也更加复杂。

为什么要有锁？

使用数据库，避免不了并发问题，当并发事务同时访问一个资源时，有可能导致数据不一致，因此需要一种机制来将数据访问顺序化，以保证数据库数据的一致性。

锁就是其中的一种机制。

我们可以用公厕做个比喻。

公厕是可供多个消费者使用的，因此可能出现多个人同时需要使用厕所的情况。

但是，厕所只有一个，总不能大家一起吧？

为了避免冲突，于是厕所里装了锁，某一个人在上测试时，可以在里面用锁锁住，其他人就不能再从外面打开了，只能等待。

等里面的人出来了，从里面把锁打开，外面的人才能进去。

下面，带你一起梳理下 MySQL 的锁管理机制和锁的执行流程，先有一个大致的脉络。

MySQL 的锁管理机制

1、全局读锁 — FLUSH TABLES WITH READ LOCK（SQL层）

2、表级 table-level 数据锁（SQL层）

3、Meta-data 元数据锁：在 table cache 缓存里实现的，为 DDL（Data Definition Language）提供隔离操作。

4、存储引擎特有机制 — row locks行锁，page locks页锁，table locks表级，版本控制（在引擎中实现）

相对其他数据库而言，MySQL 的锁机制比较简单，其最显著的特点是不同的存储引擎支持不同的锁机制。

MySQL 的锁执行流程

1、计算语句使用到的所有表；
2、在每个表：打开表，从 table cache 缓存里得到 TABLE 对象，并在此表加上 meta-data 元数据锁；
3、等待全局读锁后改变数据；
4、在每个表：锁表，在表加上 table-level 数据锁；
5、执行语句：调用：handler::write_row()/read_rnd()/read_index() 等；隐式地调用引擎级 engine-level 锁机制；
6、在每个表：释放表的数据锁；
7、在每个表：释放表的 DDL 锁并把表放回 table cache 缓存里；

下面，我们开始简单针对每一种锁，看下都有什么特点。

全局锁

加了全局锁后，整个库变为只读状态，所有的写操作都会被阻塞，包括：

• 数据的增删改
• 表结构的创建、修改
• 更新事务

加全局锁的命令：Flush tables with read lock，即 FTWRL。

全局锁的主要使用场景是全库的逻辑备份，加了全局锁进行备份时有一定的使用风险：

1、若在主库备份，备份期间只读，会影响业务；
2、若在从库备份，从库只读，无法及时同步主可以的更新，造成主从不一致；

mysqldump --single-transaction

也许你还记得，我们在之前讲事务的时候，有一个隔离级别叫做可重复读，也就是设置了隔离级别进入事务后，别的事务更改数据不会影响当前的读取。

使用 mysqldump 命令，结合 --single-transaction 参数，可以将隔离级别设置为：REPEATABLE READ。

并且随后再执行一条 START TRANSACTION 语句，让整个数据在 dump 过程中保证数据的一致性，这个选项对 InnoDB 的数据表很有用，且不会锁表。

为了确保使用 --single-transaction 命令时，最终 dump 文件的有效性。需没有下列语句 ALTER TABLE, CREATE TABLE, DROP TABLE, RENAME TABLE, TRUNCATE TABLE，因为一致性读不能隔离上述语句。所以如果在 dump 过程中，使用上述语句，可能会导致 dump 出来的文件数据不一致或者不可用。

为啥不直接使用 mysqldump --single-transaction 来备份？

因为，有些引擎不支持事务啊，比如 MyISAM 引擎，所以，现在大家都在力推用 InnoDB 替代 MyISAM。

set global readonly=true？

set global readonly=true 也可以将全局表设为只读状态，有啥区别呢？

首先，修改 global 变量的方式影响面更大，不建议使用。

另外，异常处理机制上和 FTWRL 有差异：

• FTWRL 命令：客户端异常断开，MySQL 会自动释放全局锁，整个库回到正常更新的状态
• readonly 状态下，客户端发生异常，数据库会一直保持 readonly 状态，导致整个库长时间处于不可写状态

注意点

• FTWRL 前有读写的话 ，FTWRL 都会等待读写执行完毕后才执行
• FTWRL 执行的时候要刷脏页的数据到磁盘，要保持数据的一致性
• 执行 FTWRL 时候会等待所有事务都提交完毕

表级锁

表锁

语法

LOCK TABLES tbl_name ; # 不影响其他表的写操作

解锁也是：

UNLOCK TABLES;

注意点：

• 这两个语句在执行的时候都需要注意个特点，就是隐式提交的语句，在退出 mysql 终端的时候都会隐式的自动执行 unlock tables，也就是如果要让表锁定生效就必须一直保持对话。
• lock tables 除了会限制别的线程的读写外，也会限制本线程接下来的操作对象
• 锁住整个表的影响面较大

P.S. MYSQL 的 read lock 和 wirte lock

read-lock：允许其他并发的读请求，但阻塞写请求，即可以同时读，但不允许任何写，也叫共享锁
write-lock：不允许其他并发的读和写请求，是排他的（exclusive），也叫独占锁

元数据锁（MDL：metadata lock）

元数据锁不需要显式使用，在访问一个表的时候会自动加上。

它的作用主要是保证读写的正确性。

• 表的增删改查操作，需要先加 MDL 读锁；
• 表结构变更操作，需要先加 MDL 写锁
• MDL 读锁之间不互斥，多个线程可以同时对一张表增删改查。
• MDL 读写锁之间、写锁之间是互斥的，用来保证变更表结构操作的安全性。

如果有两个线程要同时给一个表加字段，其中一个要等另一个执行完才能开始执行。

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

因此，需要避免长事务，因为长事务会造成锁一直不能释放，后续的操作会堆积，这个库的线程很快就会爆满。

行锁

行锁是引擎层实现的，像 MyISAM 引擎就直接不支持行锁，这些引擎在并发控制只能用表锁！

InnoDB 的行锁

两阶段协议：

• 需要的时候加上
• 事务结束时释放

当需要锁多个行时，尽量把影响并发的锁往后放，这样可以最大程度的减少事务之间的锁等待，提升并发度。

另外，InnoDB 的 行锁建立在索引的基础上，锁的是索引。因此，如果更新的列没建索引会锁住整个表。

死锁

不同线程出现循环资源依赖，涉及的线程都在等待别的线程释放资源。

死锁对策

1、主动等待超时，由参数 innodb_lock_wait_timeout 设置，但是业务无法等待；

2、主动死锁检测（innodb_deadlock_detect=on）

发生死锁后，InnoDB 一般都可以检测到，并使一个事务释放锁回退，另一个则可以获取锁完成事务。

另外，我们可以采取以下方式避免死锁：

• 通过表级锁来减少死锁产生的概率；
• 多个程序尽量约定以相同的顺序访问表（这也是解决并发理论中哲学家就餐问题的一种思路）；
• 同一个事务尽可能做到一次锁定所需要的所有资源。

另外，死锁检测也非常耗费资源，判断会不会由于自己的加入导致了死锁，这是一个时间复杂度是 O(n) 的操作。

比如有 1000 个并发线程要同时更新同一行，那么死锁检测操作就是 100 万这个量级的，这将消耗大量的 CPU 资源。

如何解决死锁检测耗费资源的情况？

1、关掉死锁检测，需要保证不会发生死锁；
2、控制并发，对应相同行的更新，在进入引擎之前排队；

• 数据库服务端实现，中间件实现
• 不要在客户端实现，因为客户端的数量未知
• 改 MySQL 源码
• 将热更新的行数据拆分成逻辑上的多行来减少锁冲突，但是业务复杂度可能会大大提高

更新一条记录时具体什么时候用行锁什么时候是表锁

引擎支持行锁就行锁，比如 innodb；

引擎不支持行锁就表锁，比如 myisam；

Online DDL 的过程

在 MySQL5.6 中，开始支持更多的 alter table 类型操作来避免 copy data，同时支持了在线上 DDL 的过程中不阻塞 DML 操作，真正意义上的实现了 Online DDL。

1、拿 MDL 写锁
2、降级成 MDL 读锁
3、真正做 DDL
4、升级成 MDL 写锁
5、释放 MDL 锁

1、2、4、5 如果没有锁冲突，执行时间非常短。
第 3 步占用了 DDL 绝大部分时间，这期间这个表可以正常读写数据，是因此称为「online」

总结

• 表级锁：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低。
  • 这些存储引擎通过总是一次性同时获取所有需要的锁以及总是按相同的顺序获取表锁来避免死锁。
  • 表级锁更适合于以查询为主，并发用户少，只有少量按索引条件更新数据的应用，如 Web 应用
• 行级锁：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高。
  • 最大程度的支持并发，同时也带来了最大的锁开销。
  • 在 InnoDB 中，除单个 SQL 组成的事务外，锁是逐步获得的，这就决定了在 InnoDB 中发生死锁是可能的。
  • 行级锁只在存储引擎层实现，而 Mysql 服务器层没有实现。
  • 行级锁更适合于有大量按索引条件并发更新少量不同数据，同时又有并发查询的应用，如一些在线事务处理（OLTP）系统

上述特点来看，很难说哪种锁更好，只能相对于所处的业务场景来选择更加适合的锁机制。

如果仅从锁的角度来看，表级锁更适合以查询为主的应用场景，而行级锁则更适合于大量按索引条件并发更新少量数据的应用场景。

对于平时常用的存储引擎，MyISAM 采用的是表级锁，InnoDB 采用的是行级锁加表级锁。