MySQL实战之锁

来自极客时间，林晓斌(丁奇)的MySQL实战45讲

全局锁

全局锁就是对整个数据库实例加锁。MySQL 提供了一个加全局读锁的方法，命令是 Flush tables with read lock (FTWRL)。

当你需要让整个库处于只读状态的时候，可以使用这个命令，之后其他线程的以下语句会被阻塞：

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

应用场景：整库逻辑备份

表级锁

表级锁分为两种，一种是表锁，一种是元数据锁（MDL, meta data lock）

表锁

表锁的语法是 lock tables … read/write，可以用 unlock tables 主动释放锁，也可以在客户端断开的时候自动释放。需要注意，lock tables 语法除了会限制别的线程的读写外，也限定了本线程接下来的操作对象。

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

MDL(元数据锁)

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

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

MDL 会直到事务提交才释放，在做表结构变更的时候，你一定要小心不要导致锁住线上查询和更新

行锁

行锁就是针对数据表中行记录的锁。这很好理解，比如事务 A 更新了一行，而这时候事务 B 也要更新同一行，则必须等事务 A 的操作完成后才能进行更新。

• 行锁是由存储引擎自行实现的，某些引擎就不支持行级锁，比如myisam。
• 如果不支持行级锁，则意味着同一张表在同一时刻只能有一个写操作在执行
• 如果你的事务中需要锁多个行，要把最可能造成锁冲突、最可能影响并发度的锁尽量往后放

两阶段锁协议

在 InnoDB 事务中，行锁是在需要的时候才加上的，但并不是不需要了就立刻释放，而是要等到事务结束时才释放。这个就是两阶段锁协议

innodb行级锁是通过锁索引记录实现的，如果更新的列没建索引是会锁住整个表的。innodb内部是全表根据主键索引逐行扫描，逐行加锁。在事务完成后，统一释放。

死锁

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

死锁的应对办法

1）等待，直到超时

• 可以通过 innodb_lock_wait_timeout 来设置超时时间
• 存在的问题，如果设置时间太长，比如50s，那么死锁的等待太久，业务上难以接受；如果设置时间很短，正常的锁可能也受影响。

2）主动死锁检测

• 发现死锁后，主动回滚某一个死锁链条上的事务，让其他事务继续执行。可以通过 innodb_deadlock_detect=on 来开启这个功能
• 存在的问题：耗费大量的CPU资源。每个新来的被堵住的线程，都要判断会不会由于自己的加入导致了死锁，这是一个时间复杂度是 O(n) 的操作。假设有 1000 个并发线程要同时更新同一行，那么死锁检测操作就是 100 万这个量级的

开启后并不是每个事务都要进行检测
1、如果他要加锁访问的行上有锁，他才要检测。比如一致性读不会加锁，就不需要做死锁检测；
2、并不是每次死锁检测都都要扫所有事务。只扫描在循环链上的事务。

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

如何解决热点行更新导致的性能问题？

1. 如果你能确保这个业务一定不会出现死锁，可以临时把死锁检测关闭掉。一般不建议采用
2. 控制并发度，对应相同行的更新，在进入引擎之前排队。这样在InnoDB内部就不会有大量的死锁检测工作了。
3. 将热更新的行数据拆分成逻辑上的多行来减少锁冲突，但是业务复杂度可能会大大提高

间隙锁

跟间隙锁存在冲突关系的，是“往这个间隙中插入一个记录”这个操作。间隙锁之间都不存在冲突关系。

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

间隙锁，是专门用于解决幻读这种问题的锁，它锁的了行与行之间的间隙，能够阻塞新插入的操作间隙锁的引入也带来了一些新的问题，比如：降低并发度，可能导致死锁。

next-key lock

间隙锁和行锁合称 next-key lock，每个 next-key lock 是前开后闭区间。也就是说，我们的表 t 初始化以后，如果用 select * from t for update 要把整个表所有记录锁起来，就形成了 7 个 next-key lock，分别是 (-∞,0]、(0,5]、(5,10]、(10,15]、(15,20]、(20, 25]、(25, +supremum]。

加锁的规则

原则1：加锁的基本单位是 next-key lock。next-key lock 是前开后闭区间。
原则2：查找过程中访问到的对象才会加锁。

优化1：索引上的等值查询，给唯一索引加锁的时候，next-key lock 退化为行锁。
优化2：索引上的等值查询，需向右遍历时且最后一个值不满足等值条件的时候，next-key lock 退化为间隙锁。
优化3：索引上的范围查询：无论是否是唯一索引，范围查询都需要访问到不满足条件的第一个值为止。

bug：唯一索引上的范围查询会访问到不满足条件的第一个值为止。