MySQL锁
全局锁
对数据库加锁，典型使用场景是全库备份。
加全局读锁，整库只读：Flush tables with read lock (FTWRL)。
针对全库只读，不使用set global readonly=true，使用FTWRL的原因：
1. readonly的值可能用来做逻辑判断，例如判断是主库还是备库。
2. FTWRL在客户端异常断开连接后自动释放全局锁，readonly不会。
备份错误做法：通过FTWRL确保不会有其他线程对数据库做更新，然后对整个库做备份。在备份过程中整个库完全处于只读状态。
整库只读风险
1. 如果在主库上备份，那么在备份期间都不能执行更新；
2. 如果在从库上备份，那么备份期间从库不能执行主库同步过来的binlog，导致主从延迟。
备份正确做法：在可重复读隔离级别下开启一个事务。
官方自带的逻辑备份工具是mysqldump。当mysqldump使用参数–single-transaction的时候，导数据之前就会启动一个事务，来确保拿到一致性视图。而由于MVCC的支持，这个过程中数据是可以正常更新的。
表级锁
MySQL里面表级别的锁有2种：
表锁
支持主动释放锁，客户端断开时会自动释放。
lock tables语法除了会限制别的线程的读写外，也限定了本线程接下来的操作对象。
举个例子, 线程A执行lock tables t1 read, t2 write; 这个语句，其他线程写t1、读写t2的语句都会被阻塞；线程A在执行unlock tables之前，也只能执行读t1、读写t2的操作。
元数据锁（meta data lock，MDL)
访问表时自动加上。
事务中的MDL锁，在语句执行开始时申请，等到整个事务提交后再释放。
MDL的作用是，保证读写的正确性。如果一个查询正在遍历一个表中的数据，另一个线程对这个表结构做变更，删了一列，那么查询线程拿到的结果跟表结构对不上，不允许。
当对一个表做增删改查操作的时候，加MDL读锁；当要对表做结构变更操作的时候，加MDL写锁。
session A持有表t的MDL写锁。session B想获取MDL读锁，进入等待状态。
问题现象：给一个表加个字段，导致整个库挂了。
错误做法：
实验环境是MySQL 5.6。
session A对表t加MDL读锁。
session B获得读锁后正常执行。
session C因session A的MDL读锁没有释放而加MDL写锁阻塞。
session D想在表t上加MDL读锁的请求会被session C阻塞。
这个表暂时不可读写了。
如果表t上的查询语句频繁，而且客户端有重试机制，那么这个库的线程会爆满。
正确做法：
事务不提交，就会一直占着MDL锁。在MySQL的information_schema 库的 innodb_trx 表中查询当前执行中的事务。如果DDL变更时刚好有事务在执行，那么先暂停DDL后寻找时机，或者kill掉这个事务，或者在alter table语句里面设定等待时间（如果在这个指定的等待时间加MDL写锁失败，那么之后重试，不阻塞业务）。
死锁和死锁检测
不同线程都在等待别的线程释放资源时，导致线程相互等待，称为死锁。
案例1：update加行锁导致死锁
事务A在等待事务B释放id=2的行锁，事务B在等待事务A释放id=1的行锁。
案例2：select for update加gap lock导致死锁
创建表
CREATE TABLE `order_record` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`order_no` int(11) DEFAULT NULL,
`status` int(4) DEFAULT NULL,
`create_date` datetime(0) DEFAULT NULL,
PRIMARY KEY USING BTREE (`id`),
INDEX `idx_order_status` USING BTREE(`order_no`, `status`)
) ENGINE = InnoDB;可重复读隔离级别
|
事务A |
事务B |
|
begin; |
begin; |
|
select id from order_record where order_no = 4 for update; gap lock，gap范围是(-∞, +∞) |
|
|
|
select id from order_record where order_no = 5 for update; gap lock，gap范围是(-∞, +∞) |
|
insert into order_record(order_no, status, create_date) values (4, 1, '2019-07-13 10:57:03'); 因事务B gap lock而等待 |
|
|
|
insert into order_record(order_no, status, create_date) values (5, 1, '2019-07-13 10:57:03'); 检测到死锁，直接回滚。 |
死锁解除策略
1. 等待超时，超时时间由参数innodb_lock_wait_timeout指定。innodb_lock_wait_timeout默认值50s导致业务无法接受，设置成很短的1s会跳过锁等待流程而误认为死锁。
2. 死锁检测发现死锁后，回滚某一个事务，让其他事务继续执行。该行为由参数innodb_deadlock_detect指定。innodb_deadlock_detect的默认值是on。
推荐第2种策略，主动检测死锁。
可重复读隔离级别加锁案例
查询当前事务隔离级别
select @@tx_isolation;
在可重复读隔离级别下，普通查询是快照读，不会看到别的事务插入的数据。幻读在当前读下才会出现，仅专指新插入的行。
update的加锁语义和select ... for update是一样的。产生幻读的原因是，新插入的记录是之前不存在的行，无法加行锁。为了解决幻读问题，InnoDB引入间隙锁。
行锁分为读锁和写锁。
|
读锁 |
写锁 |
|
|
读锁 |
兼容 |
冲突 |
|
写锁 |
冲突 |
冲突 |
跟行锁有冲突关系的是另一个行锁。
跟间隙锁存在冲突关系的是，往这个间隙中插入记录这个操作。间隙锁和另一个间隙锁不存在冲突关系。间隙锁是开区间。
设置隔离级别是读提交后，为了解决可能出现的数据和日志不一致问题，需要把binlog格式设置为row。
间隙锁的引入，可能导致同样的语句锁住更大的范围，影响并发度。
为了实现当前事务修改语义上的正确性，扫描过的行加上行锁，而不是只有符合条件的行才加行锁。
A事务对某列=xxx的行加锁后，B事务想把某行的该列改成xxx而阻塞，保证A事务才有对某列=xxx的行的修改权限。
即使把所有的记录都加上锁，还是阻止不了新插入的记录，这就是幻读问题的特殊性。
可重复读和读提交的加锁不同
间隙锁在可重复读隔离级别下才有效，读提交没有间隙锁。
在读提交隔离级别下，行锁在语句执行完成后把“不满足条件的行”上的行锁会直接释放，不需要等到事务提交。相比于可重复读，锁的范围更小，锁的时间更短，这是业务默认使用读提交隔离级别的原因。
可重复读隔离级别遵守两阶段锁协议，加锁在事务提交或者回滚的时候才释放。
加锁总结
加锁总结
锁加在索引上，如果没有索引则是使用表锁。间隙锁（索引项之间的间隙）可以重复加，间隙锁范围内无法insert。
原则1：加锁的基本单位是next-key lock，由间隙锁加行锁实现，前开后闭区间。
原则2：查找过程中访问到的对象才会加锁。
退化
优化1：索引上的等值查询，给唯一索引加锁时，next-key lock退化为行锁。
优化2：索引上的等值查询，向右遍历时且最后一个值不满足等值条件的时候，next-key lock退化为间隙锁（开区间）。
bug1：唯一索引上的范围查询会访问到不满足条件的第一个值为止。
初始化表
CREATE TABLE `t` (
`id` int(11) NOT NULL,
`c` int(11) DEFAULT NULL,
`d` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `c` (`c`)
) ENGINE=InnoDB;
insert into t values(0,0,0),(5,5,5),(10,10,10),(15,15,15),(20,20,20),(25,25,25);案例1：等值查询间隙锁
根据原则1，加锁单位是next-key lock，session A加锁范围就是(5,10]。
根据优化2，这是一个等值查询id=7，id=10不满足查询条件，next-key lock退化成间隙锁，最终加锁的范围是(5,10)。
session B插入id=8的记录会被锁住，session C修改id=10这行是可以的。
案例2：非唯一索引等值锁
session A为c=5这行加读锁。
根据原则1，加锁单位是next-key lock，加锁范围是(0,5]。
c是普通索引，向右遍历，查到c=10才放弃。根据原则2，访问到的都要加锁，给(5,10]加next-key lock。根据优化2，退化成间隙锁(5,10)。
根据原则2，这个查询使用覆盖索引（查询字段来自该索引b+树），不需要访问主键索引，所以主键索引上没有加任何锁，session B可以执行完成。
session C要插入一个(7,7,7)的记录，就会被session A的间隙锁(5,10)锁住。
lock in share mode只锁覆盖索引，for update会给主键索引上满足条件的行加上行锁。
锁是加在索引上的。如果你要用lock in share mode来给行加读锁避免数据被更新的话，在查询字段中加入索引中不存在的字段。比如，将session A的查询语句改成select d from t where c=5 lock in share mode。
案例3：主键索引范围锁
对于我们这个表t，下面这两条查询语句，加锁范围相同吗？
mysql> select * from t where id=10 for update;
mysql> select * from t where id>=10 and id<11 for update;
开始执行的时候，要找到第一个id=10的行，因此本该是next-key lock(5,10]。
根据优化1，主键id上的等值条件，退化成行锁，只加了id=10这一行的行锁。
范围查找就往后继续找，找到id=15这一行停下来，因此需要加next-key lock(10,15]。
所以，session A在主键索引上加锁，行锁id=10和next-key lock(10,15]。
id>=10用等值查询，向右扫描到id=15用范围查询。
案例4：非唯一索引范围锁
索引c上加了(5,10]和(10,15] next-key lock。
案例5：唯一索引范围锁bug
根据原则1，索引id上只加(10,15] next-key lock，因为id是唯一键，所以循环判断到id=15这一行就应该停止了。
根据bug1，InnoDB会往前扫描到第一个不满足条件的行为止即id=20。这是范围扫描，索引id上加(15,20] next-key lock。
照理说，没有必要锁住id=20这一行。因为扫描到id=15，就可以确定不用往后再找了。但实现上还是这么做了，因此认为这是个bug。
案例6：非唯一索引上存在"等值"
insert into t values(30,10,30);
表里有两个c=10的行。由于非唯一索引上包含主键的值，所以是不可能存在“相同”的两行的。
虽然有两个c=10，但是它们的主键值id是不同的（分别是10和30），因此这两个c=10的记录之间，也是有间隙的。
为了跟间隙锁的开区间形式进行区别，用(c=10,id=30)这样的形式，来表示索引上的一行。
delete语句加锁的逻辑跟select ... for update类似。
根据原则1，加(c=5,id=5)到(c=10,id=10) next-key lock。
session A向右查找，直到碰到(c=15,id=15)这一行，循环才结束。
根据优化2，这是等值查询，退化成(c=10,id=10)到(c=15,id=15)间隙锁。
这个delete语句在索引c上的加锁范围，就是下图中蓝色区域覆盖的部分。
这个蓝色区域左右两边都是虚线，表示开区间，即(c=5,id=5)和(c=15,id=15)这两行上都没有锁。
案例7：limit 语句加锁
delete语句明确加了limit 2的限制，遍历到(c=10, id=30)这一行之后，满足条件的语句已经有两条，循环就结束了。
索引c上的加锁范围就变成了从（c=5,id=5)到（c=10,id=30)这个前开后闭区间
在删除数据的时候尽量加limit。不仅控制删除数据的条数，操作更安全，还可以减小加锁的范围。
案例8：一个死锁的例子
session A的select语句在索引c上加next-key lock(5,10]和间隙锁(10,15)；
session B的update语句要在索引c上加next-key lock(5,10] ，进入锁等待；
session A要再插入(8,8,8)这一行，被session B的间隙锁锁住。
由于出现了死锁，InnoDB让session B回滚。
session B的next-key lock不是还没申请成功吗？
session B的“加next-key lock(5,10] ”操作，实际上分成了两步，先是加(5,10)的间隙锁，加锁成功；然后加c=10的行锁，这时候才被锁住的。
参考资料
