MySQL中的锁

发表于 更新于 分类于 阅读次数： Waline： 本文字数： 7.8k 阅读时长 ≈ 7 分钟

数据库锁设计的初衷是处理并发问题。作为多用户共享的资源，当出现并发访问的时候，数据库需要合理地控制资源的访问规则。而锁就是用来实现这些访问规则的重要数据结构。

(1) MySQL中的锁

按照加锁机制(是否锁住同步资源)分为乐观锁、悲观锁

按照锁粒度可以分为全局锁、表级锁、行锁。

按照多个线程是否共享分为共享锁、排他锁

(1.1) 两段锁协议

有大量的并发访问，为了预防死锁，一般应用中推荐使用一次封锁法，就是在方法的开始阶段，已经预先知道会用到哪些数据，然后全部锁住，在方法运行之后，再全部解锁。这种方式可以有效的避免循环死锁，但在数据库中却不适用，因为在事务开始阶段，数据库并不知道会用到哪些数据。

数据库遵循的是两段锁协议，将事务分成两个阶段，加锁阶段和解锁阶段（所以叫两段锁）

加锁阶段：在该阶段可以进行加锁操作。在对任何数据进行读操作之前要申请并获得S锁（共享锁，其它事务可以继续加共享锁，但不能加排它锁），在进行写操作之前要申请并获得X锁（排它锁，其它事务不能再获得任何锁）。加锁不成功，则事务进入等待状态，直到加锁成功才继续执行。
解锁阶段：当事务释放了一个封锁以后，事务进入解锁阶段，在该阶段只能进行解锁操作不能再进行加锁操作。


(2) 乐观锁悲观锁

MySQL中提供的锁都是悲观锁，需要先锁住才操作。


(3) MySQL中的全局锁表级锁和行锁

根据加锁的粒度/范围，MySQL 里面的锁大致可以分成全局锁、表级锁和行锁三类。

(3.1) 全局锁

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

全局锁的典型使用场景是，做全库逻辑备份。

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

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

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


(3.2) 表级锁

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

(3.2.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 命令来控制并发，毕竟锁住整个表的影响面还是太大。


(3.2.1) 元数据锁(metadata lock)

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

MDL 不需要显式使用，在访问一个表的时候会被自动加上。
MDL 的作用是并发情况下维护数据的一致性，保证读写的正确性。(避免加字段删字段导致查询结果异常)

因此，在 MySQL 5.5 版本中引入了 MDL，当对一个表做增删改查操作的时候，加 MDL 读锁；
当要对表做结构变更操作的时候，加 MDL 写锁。

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

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

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

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


(3.3) 行锁

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 这样的顺序，那么影院账户余额这一行的锁时间就最少。这就最大程度地减少了事务之间的锁等待，提升了并发度。


(4) 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锁的目的，是为了防止同一事务的两次当前读，出现幻读的情况。

 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隔离级别，读不加锁就不再成立，所有的读操作，都是当前读。

(4) 死锁

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

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

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

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

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

(5) 可能遇到的问题

(5.1) 备份一般都会在备库上执行，你在用–single-transaction 方法做逻辑备份的过程中，如果主库上的一个小表做了一个 DDL，比如给一个表上加了一列。这时候，从备库上会看到什么现象呢？

备份一般都会在备库上执行，你在用–single-transaction 方法做逻辑备份的过程中，如果主库上的一个小表做了一个 DDL，比如给一个表上加了一列。这时候，从备库上会看到什么现象呢？

假设这个 DDL 是针对表 t1 的， 这里我把备份过程中几个关键的语句列出来：

Q1:SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
Q2:START TRANSACTION  WITH CONSISTENT SNAPSHOT；
/* other tables */
Q3:SAVEPOINT sp;
/* 时刻 1 */
Q4:show create table `t1`;
/* 时刻 2 */
Q5:SELECT * FROM `t1`;
/* 时刻 3 */
Q6:ROLLBACK TO SAVEPOINT sp;
/* 时刻 4 */
/* other tables */

在备份开始的时候，为了确保 RR（可重复读）隔离级别，再设置一次 RR 隔离级别 (Q1);
启动事务，这里用 WITH CONSISTENT SNAPSHOT 确保这个语句执行完就可以得到一个一致性视图（Q2)；
设置一个保存点，这个很重要（Q3）；
show create 是为了拿到表结构 (Q4)，然后正式导数据 （Q5），回滚到 SAVEPOINT sp，在这里的作用是释放 t1 的 MDL 锁 （Q6）。当然这部分属于“超纲”，上文正文里面都没提到。
DDL 从主库传过来的时间按照效果不同，我打了四个时刻。题目设定为小表，我们假定到达后，如果开始执行，则很快能够执行完成。

参考答案如下：

  1. 如果在 Q4 语句执行之前到达，现象：没有影响，备份拿到的是 DDL 后的表结构。

  2. 如果在“时刻 2”到达，则表结构被改过，Q5 执行的时候，报 Table definition has changed, please retry transaction，现象：mysqldump 终止；

  3. 如果在“时刻 2”和“时刻 3”之间到达，mysqldump 占着 t1 的 MDL 读锁，binlog 被阻塞，现象：主从延迟，直到 Q6 执行完成。

  4. 从“时刻 4”开始，mysqldump 释放了 MDL 读锁，现象：没有影响，备份拿到的是 DDL 前的表结构。

(5.2) 删数据问题

如果你要删除一个表里面的前 10000 行数据，有以下三种方法可以做到：
第一种，直接执行 delete from T limit 10000;
第二种，在一个连接中循环执行 20 次 delete from T limit 500;
第三种，在 20 个连接中同时执行 delete from T limit 500。

你会选择哪一种方法呢？为什么呢？

方案一，事务相对较长，则占用锁的时间较长，会导致其他客户端等待资源时间较长。
方案二，串行化执行，将相对长的事务分成多次相对短的事务，则每次事务占用锁的时间相对较短，其他客户端在等待相应资源的时间也较短。这样的操作，同时也意味着将资源分片使用（每次执行使用不同片段的资源），可以提高并发性。
方案三，人为自己制造锁竞争，加剧并发量。

(5.3) 问题3

1.如何在死锁发生时,就把发生的sql语句抓出来？
2.在使用连接池的情况下,连接会复用.比如一个业务使用连接set sql_select_limit=1,释放掉以后.其他业务复用该连接时,这个参数也生效.请问怎么避免这种情况,或者怎么禁止业务set session？
3.很好奇双11的成交额,是通过redis累加的嘛？
4.不会改源码能成为专家嘛？

  1. show engine innodb status 里面有信息，不过不是很全…
  2. 5.7的reset_connection接口可以考虑一下
  3. 用redis的话，为了避免超卖需要增加了很多机制来保证。修改都在数据库里执行就方便点。前提是要解决热点问题
  4. 我认识几位处理问题和分析问题经验非常丰富的专家，不用懂源码，但是原理还是要很清楚的

(5.4) 转义导致死锁问题

前天在开发中，还遇到过一次死锁，是在一个批处理中，要删除1000条数据，5个线程，200条数据commit一次，
sol：delete from 表A where id =15426169754750004759008 STORAGEDB
(id是主键)
我同事解决了，说原因是id 是char 类型，但是没有加单引号，所以没有进入id索引中，然后锁表了，所以导致死锁。

这个问题的出现，应该是人为只要并发导致锁冲突吧？但是为什么不加单引号会死锁，加了单引号就能正常跑呢？


(6) 思考题

从并发角度看插入操作和更新操作哪个先执行

假设你负责实现一个电影票在线交易业务，顾客 A 要在影院 B 购买电影票。我们简化一点，这个业务需要涉及到以下操作：
1、从顾客 A 账户余额中扣除电影票价；
2、给影院 B 的账户余额增加这张电影票价；
3、记录一条交易日志。

也就是说，要完成这个交易，我们需要 update 两条记录，并 insert 一条记录。当然，为了保证交易的原子性，我们要把这三个操作放在一个事务中。
那么，你会怎样安排这三个语句在事务中的顺序呢？

根据两阶段锁协议，不论你怎样安排语句顺序，所有的操作需要的行锁都是在事务提交的时候才释放的。
操作2行锁冲突比较大，如果你把语句2安排在最后，比如按照 3、1、2 这样的顺序，那么操作2影院账户余额这一行的锁时间就最少。
这就最大程度地减少了事务之间的锁等待，提升了并发度。

这么设计，影院余额这一行的行锁在一个事务中不会停留很长时间。

从并发系统性能的角度考虑，你觉得在这个事务序列里，应该先插入操作记录，还是应该先更新计数表呢？

知识点在《行锁功过：怎么减少行锁对性能的影响？》
因为更新计数表涉及到行锁的竞争，先插入再更新能最大程度地减少了事务之间的锁等待，提升了并发度。

参考资料

[1] 19 | 为什么我只查一行的语句，也执行这么慢？MySQL实战45讲
[2] 06 | 全局锁和表锁 ：给表加个字段怎么有这么多阻碍？MySQL实战45讲
[3] 07 | 行锁功过：怎么减少行锁对性能的影响？MySQL实战45讲
[4] mysql 5.7 lock-tables
[5] MySQL 5.7 Reference Manual / The InnoDB Storage Engine / Locks Set by Different SQL Statements in InnoDB
[6] MySQL 5.7 Reference Manual / The InnoDB Storage Engine / InnoDB Startup Options and System Variables
[7] 《高性能MySQL》 O’REILLY
[8] mysql-show-open-tables
[9] mysql-show-processlist
[10] innodb-locking
[11] innodb-index-types
[12] 面试官：同学，分析一下MySQL/InnoDB的加锁过程吧
[13] 解决死锁之路 - 常见 SQL 语句的加锁分析
[14] mysql insert锁机制
[15] MySQL六十六问，两万字+五十图详解！
[16] Innodb中的事务隔离级别和锁的关系