MySQL锁可以分为全局锁、表级锁、行锁
目录
一、全局锁
全局锁：对整个数据库实例加锁
①flush tables with read lock（FTWRL）全局读锁，让整个数据库处于只读状态，会阻塞以下语句：数据的增删改、数据的表结构修改（DDL）、更新类事务的提交语句
使用场景：做数据库的备份
官方自带的逻辑备份工具是mysqldump，当mysqldump 使用参数 -single-transaction的时候，导数据之前会启动一个事务，来确保拿到一致性视图，而由于MVCC的支持，这个过程中是可以正常更新的
注：
备份数据库：mysqldump -uroot -pPassword [database name] > [dump file]
恢复数据库：mysql [database name] < [backup file name]
mysqldump适用于存储引擎为支持事务的备份
flush table with read lock适用于MySQL server层面（不要求是否支持事务），而且，如果备份的过程中有更新，又要求只能取最新的数据，就只能使用FTWRL命令
②set global readonly = true 让全库进入只读状态
注：
flush table with read lock 与 set global readonly=true的区别
①readonly的值会被用来做其他逻辑，比如用来判断是主库还是备库，因此，修改global变量的影响面更大
②在异常处理机制上有差异，如果执行FTWRL命令后由于客户端发生异常断开，那么MySQL会自动释放这个全局锁，
而设置readonly之后，若客户端发生异常，则数据库就会一直保持readonly状态，会导致数据库长时间处于不可写状态（super权限可以正常操作）
二、表级锁
①表锁lock tables … read/write，unlock tables释放锁，对于innodb这种支持行锁的引擎，一般不使用这个锁
②意向锁（意向共享锁、意向排它锁），为了兼容表锁与行锁
事务A在申请行锁（写锁）之前，数据库会自动先给事务A申请表的意向排他锁。
当事务B去申请表的写锁时就会失败，因为表上有意向排他锁之后事务B申请表的写锁时会被阻塞
③元数据锁meta data lock，MDL（Server层），主要用于隔离DML和DDL之间的干扰
当对表做增删改查操作（DML）的时候，加MDL读锁
当对表结构做变更操作（DDL）的时候，加MDL写锁
MDL读锁之间不互斥，可以允许多个线程同时对一张表增删改查
MDL读写锁之间、写锁之间是互斥的，用来保证变更表结构操作的安全性
引发的问题：给一个表增加字段，导致整个库挂了
给一个表加字段、修改字段类型、加索引，需要扫描全表的数据
①session A先启动，此时会对表t施加一个MDL读锁，由于session B也是施加MDL读锁，所以可以正常执行
②session C启动，由于MDL读锁还没有释放，而且C需要的是MDL写锁，所以C会被阻塞
③session D启动，申请MDL读锁，由于C已经阻塞，导致D也会被阻塞，也就是说，此时所有对表的增删改查操作都会被锁住，导致这个表完全不可读写（这个地方以后可以深入了解一下，低优先级）
解决：
①解决长事务，事务不提交，会一直占用MDL锁
在MySQL的information_schema库的innodb_trx表中，可以查询当前执行中的事务，若在执行DDL语句时刚好有长事务在执行，可以先考虑暂停DDL，或者停掉这个长事务
②在DDL操作上增加等待时间，等待时间内获取不到MDL写锁，则不阻塞后面的业务语句
ALTER TABLE tbl_name NOWAIT add column ...
ALTER TABLE tbl_name WAIT N add column ...
注：
Online DDL的过程是这样的：
1. 拿MDL写锁
2. 降级成MDL读锁
3. 真正做DDL
4. 升级成MDL写锁
5. 释放MDL锁
1、2、4、5如果没有锁冲突，执行时间非常短。第3步占用了DDL绝大部分时间，这期间这个表可以正常读写数据，是因此称为“online ”
三、行锁
1.在innodb事务中，行锁在需要的时候才加上的，等到事务结束才释放
2.innodb行锁是通过锁索引记录来实现，如果update的列没有索引，即使只update一条记录也会锁定整张表（逐行加锁）
3.myisam不支持行锁，表锁同一张表在同一个时刻只能有一个更新，一张表可以有多个线程获取MDL读锁，但是只有一个能获得表锁
4.死锁：不同线程出现循环资源依赖，都在等待其他线程释放锁
解决：
①设置innodb_lock_wait_timeout锁超时时间，默认50s
②设置innodb_deadlock_detect死锁监测，默认on开启
③按照顺序加锁，对同一组资源，按照相同的顺序访问
④控制并发数
5.innodb通过使用一致性视图实现RC和RR隔离级别
对于可重复读，查询只承认在事务启动前就已经提交完成的数据；
对于读提交，查询只承认在语句启动前就已经提交完成的数据；
四、非索引字段加锁过程
例子：字段c上无索引
begin;
select * from t where c=5 for update;
commit;
问：这个语句是如何加锁，加的锁是什么时候释放的？
①Read Committed（读已提交）
先对该表所有行施加行锁，再逐个进行过滤，解锁c不等于5的行，最终只锁c=5的行（仅对update有效，delete仍然锁全部）
②Repeatable Read（可重复读）
对该表所有行施加行锁，并所有行之间施加gap锁（间隙锁）
五、只查询一行数据，为什么却执行非常慢
1.等MDL写锁
例子：select * from t where id = 1;
①使用show processlist命令查看当前语句处于什么状态
Waiting for table metadata lock 表示现在有一个线程正在表t上请求或者持有MDL写锁，把select语句阻塞住
②kill 阻塞的线程号
2.等flush
例子：select * from t where id = 1;
关闭表t：flush tables t with read lock;
关闭所有打开的表：flush tables with read lock;
出现Waiting for table flush的情况可能是，有一个flush table命令被其他语句阻塞了，然后它又阻塞了select语句
使用show processlist + kill 可以解决
3.等行锁
例子：select * from t where id = 1 lock in share mode;
①另一个事务占用了该记录的写锁，迟迟没有提交
4.索引
例子：select * from t where c=50000 limit 1;
字段c上无索引，只能走全表扫描
5.undo log
例子：
select * from t where id=1;很慢
select * from t where id=1 lock in share mode;很快
sessionB执行了100万次，生成了100万个undo log
带lock in share mode的SQL语句，是当前读，直接读取主键索引树上的叶子节点数据，所以速度很快；
而不带锁的SQL语句，是快照读，需要依次反向执行100万次undo log，得到结果。快照读是需要靠undo log来获取老版本数据，而不是把历史版本的数据保存下来
六、间隙锁
1.间隙锁
①间隙锁在可重复读隔离级别下才有效
②间隙锁之间不冲突，因为它们拥有共同的目标：保护这个间隙，不允许插入新值
③间隙锁+行锁=next-key lock，每个next-key lock 都是前开后闭区间
2.加锁规则
a.加锁的基本单位是next-key lock
b.查找过程中访问到的对象才会加锁
c.索引上的等值查询，给唯一索引加锁的时候，next-key lock退化为行锁
d.索引上的等值查询，向右遍历时且最后一个值不满足等值条件时，next-key lock退化成间隙锁
e.唯一索引上的范围查询会访问到不满足条件的第一个值为止
desc会将向右扫描变成向左扫描，其他规则不变
例子：
|
|
①唯一索引等值查询间隙锁
根据规则a，加锁单位是next-key lock，session A加锁的范围就是 (5,10]
由于这是一个等值查询，而id=10不满足查询条件，next-key lock会退化成间隙锁，因此最终范围是 (5,10)
所以，session B 的insert语句会被锁住，但是session C的update语句不会被锁住
②非唯一索引等值锁
1.首先，根据规则a，加锁单位是next-key lock，因此会给索引c的 (0,5]施加临键锁
2.由于索引c是普通索引，因此根据规则d需要继续向右继续遍历，找到c=10才放弃
3.根据规则b，要给 (5,10]施加临键锁，而又根据规则d，最后的10不等于5，因此退化成 (5,10)
4.根据规则b，只有访问到的对象（索引）才会加锁，这个查询使用了覆盖索引，并不需要访问主键索引，因此主键索引上没有任何锁
所以，session B的update语句能够执行成功，session C的insert会被锁住
注：在这个例子中，由于只查询id，因此lock in share mode只会锁覆盖索引，如果查询的字段需要去主键索引查、或者说使用for update，都会锁住主键索引
③主键索引范围锁
1.根据规则a，加锁单位为next-key lock，因此会给索引id施加 (5,10]临键锁
2.根据规则c，先定位id=10的行，因此临键锁退化成行锁id=10
3.根据规则d，向右开始范围查找，找到id=15停下来，因此施加next-key lock (10,15]（需要扫描id=15这行，才知道需不需要往下找，所以加锁，又因为不是等值查找，因此不会退化行锁）
④非唯一索引范围锁
1.根据规则a，加锁单位是next-key lock，因此会给索引c施加 (5,10]临键锁，由于非唯一索引，所以不会退化成行锁
2.根据规则d，向右开始范围扫描，找到id=15停下来，因此施加next-key lock (10,15]
⑤唯一索引范围锁bug（8.0.30已修复）
1.根据规则a，加锁单位是next-key lock，因此会给索引id施加 (10,15]临键锁，由于是唯一索引，退化成行锁
2.根据规则e，还会继续扫描，直到找到20，施加临键锁 (15,20]
大佬认为是bug，扫描到id=15就可以了，没必要扫描到20
⑥非唯一索引上等值的间隙
虽然有两个c=10，但是由于主键id（10，30）是不同的，因此这两个记录之间也会有间隙
session A 先找到第一个c=10的记录，先施加（5，5）到（10，10）的临键锁
向右范围查找第一个不满足条件的行，施加（10，10）到（10，15）的间隙锁（等值查询，退化成间隙锁）
因此索引c的加锁范围如下：
⑦limit语句加锁
表中c=10的条数有两条，已经满足了limit 2，因此当session B执行insert操作时，直接通过了
加锁范围为（5，5）到（10，30）的临键锁
如果没有（10，30）这条数据，session B执行insert语句，将被阻塞
⑧desc语句影响加锁
1.由于是order by c desc，第一个要定位的元素是是右边的c=20这个行，所以会加上临键锁 (15,20] 和 间隙锁（20，25），只能从20再向右扫描到25，才能知道它的左边是最右的20
2.在索引c上向左扫描，扫描到c=10才停下来，所以临键锁会加到 (5，10]
因此，索引c上锁的范围是（5，25），主键索引上id=15、20两个行锁，
通过树搜索的方式定位记录时，用的是“等值查询”的方法，所以（20，25）没有25
⑨in语句的加锁
例子：select id from t where c in(5,20,10) lock in share mode;（c普通索引、非唯一索引）
a）先查找c=5，锁住了(0,5]。但是因为c不是唯一索引，所以为了确认还有没有别的记录c=5，就会向右遍历，找到c=10才确认没有了，所以加了间隙锁（5，10）
b）查找c=10，锁住了(5,10]和（10，15），查找c=20时，加锁(15,20]和（20，25）
最终加锁范围为（5，25）并去掉c=15的行锁，这些锁是在执行过程一个一个假的，而不是一次性加上去的
注：如果同时又有一个语句在执行，select id from t where c in(5,20,10) desc for update;
和上面语句是相同的加锁范围，加锁的顺序相反，所以当这两条语句并发执行的时候，就可能会出现死锁
⑩一个死锁例子
session A 施加 (5，10]的临键锁和（10，15）的间隙锁
session B 先施加（5，10）间隙锁，再施加10行锁，结果被锁住
从而造成死锁
3.间隙锁引发的死锁
例子：表t不存在id=9的数据，存在id=5和id=10的数据
①session A 执行select语句，由于id=9数据不存在，因此会施加间隙锁（5，10）
②session B 执行select语句，由于id=9数据不存在，同样也会施加间隙锁（5，10）
③session B 执行insert语句，由于session A已经施加了间隙锁，所以执行会阻塞
④session A 执行insert语句，同样会被session B施加的间隙锁阻塞
⑤至此，形成死锁
所以，间隙锁的引入，虽然避免了当前读的幻读问题，但是也会导致锁的范围变大，也在一定程度上影响了并发度
311
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
