3 锁

来源：

《MySQL实战45讲》

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

本文介绍碰到锁时的现象和其背后的原理。

1 全局锁

对整个数据库实例加锁

加全局读锁的方法：FTWRL

Flush tables with read lock

使用这个命令之后，整个库处于只读的状态，其他线程的如下语句会被阻塞：
数据更新语句（数据的增删改）、数据定义语句（包括建表、修改表结构等）和更新类事务的提交语句

典型场景：做全库逻辑备份，即把整个库所有表都select出来存成文本

但是FTWRL这种只读的方式必须停掉业务；
可以使用官方自带的逻辑备份⼯具mysqldump，当mysqldump使用参数–single-transaction的时候，会启动⼀个事务，确保拿到⼀致性视图，而由于MVCC的⽀持，这个过程中数据是可以正常更新的；
（–single-transaction 是⼀致性读，在开始备份前开启事务并将隔离级别设置为可重复读，所以备份期间主库上后发起的DDL在备库上不可⻅。）
这也是业务中InnoDB代替MyISAM的原因，这样就能使用–single-transaction了

2 表锁

MySQL表级别的锁有两种：一种是表锁，一种是元数据锁

表锁的语法如下

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一般不使用

元数据锁MDL不需要显式使用，当对⼀个表做增删改查操作的时候，加MDL读锁；当要对表做结构变更操作的时
候，加MDL写锁。

作用：保证读写正确性，如果⼀个查询正在遍历⼀个表中的数据，而执⾏期间另⼀个线程对这个表结构做变更，删了⼀列，那么查询线程拿到的结果跟表结构对不上，那就不行了

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

• 问题：给小表加字段

如下图：

session A先启动，这时候会对表t加⼀个MDL读锁；
由于session B需要的也是MDL读锁，因此可以正常执行；
之后session C会被blocked，是因为session A的MDL读锁还没有释放，而session C需要MDL写锁，因此只能被阻塞；
session C之后所有要在表t上新申请MDL读锁的请求也会被session C阻塞，因为所有对表的增删改查操作都需要先申请MDL读锁，所以都被锁住，等于这个表现在完全不可读写了；
如果某个表上的查询语句频繁，而且客户端有重试机制，也就是说超时后会再起⼀个新session再请求的话，这个库的线程很快就会爆满。

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

• 解决：如何安全地加小字段

⾸先要解决长事务，事务不提交，就会⼀直占着MDL锁：
在MySQL的information_schema 库的 innodb_trx 表中可以查到当前执行中的事务；
如果你要做DDL变更的表刚好有长事务在执行，要考虑先暂停DDL，或者kill掉这个长事务。

考虑⼀下这个场景：如果要变更的表是⼀个热点表，数据量不大，请求却很频繁，但是不得不加个字段，该怎么做？
这时候kill可能未必管用，因为新的请求马上就来了；
比较理想的机制是，在alter table语句里面设定等待时间，如果在这个指定的等待时间里面能够拿到MDL写锁最好，拿不到也不要阻塞后面的业务语句，先放弃，之后开发⼈员或者DBA再通过重试命令重复这个过程；
MariaDB已经合并了AliSQL的这个功能，所以这两个开源分支目前都支持DDL NOWAIT/WAIT n这个语法。

ALTER TABLE tbl_name NOWAIT add column ...
ALTER TABLE tbl_name WAIT N add column ...

3 行锁

MySQL的行锁是在引擎层由各个引擎自己实现的。

在InnoDB事务中，行锁是在需要的时候才加上的，但并不是不需要了就立刻释放，而是要等到事务结束时才释
放，这个就是两阶段锁协议，如下图的执行序列，事务B的update会被阻塞，要等事务Acommit了之后，事务B的update才能继续执行：

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

要注意死锁问题：

事务A和事务B在互相等待对方的资源释放，进⼊了死锁状态。

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

1 直接进入等待，直到超时；
这个超时时间可以通过参数innodb_lock_wait_timeout来设置，默认50s，这个时间太长了，用户无法接受，而设置太短，又可能本次不是死锁而是正常的锁等待，但是超时而被误伤，所以一般采用第二种策略

2 发起死锁检测，发现死锁后，主动回滚死锁链条中的某⼀个事务，让其他事务得以继续执行；
将参数innodb_deadlock_detect设置为on，表示开启这个逻辑，默认就是on；

使用策略2时可能出现热点行更新导致的性能问题：所有事务都要更新同⼀行，每个新来的被堵住的线程，都要判断会不会由于自己的加⼊导致了死锁，这是⼀个时间复杂度是O(n)的操作；
假设有1000个并发线程要同时更新同⼀行，那么死锁检测操作就是100万这个量级的；
虽然最终检测的结果是没有死锁，但是这期间要消耗⼤量的CPU资源。因此会看到CPU利用率很⾼，但是每秒却执行不了几个事务。

解决：思路为控制访问相同资源的并发事务量

1 如果能确保这个业务⼀定不会出现死锁，可以临时把死锁检测关掉；
这种操作本身带有⼀定的风险，因为业务设计的时候⼀般不会把死锁当做⼀个严重错误，毕竟出现死锁了就回滚，然后通过业务重试⼀般就没问题了，这是业务无损的；
而关掉死锁检测意味着可能会出现大量的超时，这是业务有损的。

2 控制并发度；
在客户端进行限流，但是其实也不太可行，比如有600个客户端，即使每个客户端控制到只有5个并发线程，汇总到数据库服务端以后，峰值并发数也可能要达到3000，比上面说的1000还高；
因此可以在服务端进行并发控制，也就是限流，基本思路为：对于相同行的更新，在进入引擎之前排队，这样在InnoDB内部就不会有大量的死锁检测工作了；

3 在设计上进行优化：
通过将一行改为逻辑上的多行来减少冲突，汇总的时候记得合并就好，这类方案需要根据业务逻辑做详细设计

4 加锁规则

可重复读隔离级别下的规则：两个原则、两个优化、一个bug
1 原则 1：加锁的基本单位是 next-key lock。（前开后闭区间）
2 原则 2：查找过程中访问到的对象才会加锁。
3 优化 1：索引上的等值查询，给唯一索引加锁的时候，next-key lock 退化为行锁。
4 优化 2：索引上的等值查询，向右遍历时且最后一个值不满足等值条件的时候，next-key lock 退化为间隙锁。
5 一个 bug：唯一索引上的范围查询会访问到不满足条件的第一个值为止，而不是只访问到一个就完事。

读提交级别的优化：语句执行过程中加上的行锁，在语句执行完成后，就要把“不满足条件的行”上的行锁直接释放了，不需要等到事务提交。

show engine innodb status可以看到锁信息，如是否死锁、是否锁等待

5 Insert的锁

insert … select 是很常见的在两个表之间拷贝数据的方法；
注意，在可重复读隔离级别下，这个语句会给select的表里扫描到的记录和间隙加读锁。

如果insert和select的对象是同⼀个表，则有可能会造成循环写入；
这种情况下需要引入用户临时表来做优化。

insert 语句如果出现唯⼀键冲突，会在冲突的唯⼀值上加共享的next-key lock(S锁)；
因此，碰到由于唯⼀键约束导致报错后，要尽快提交或回滚事务，避免加锁时间过长。

6 如何避免死锁

可结合第3点的“解决

行锁、间隙锁可能会产生死锁，

1 注意程序的逻辑

2 保持事务的轻量

3 提高运行的速度

4 尽量快地提交事务，减少持有锁的时间