Mysql学习笔记【锁】
原创
本文为极客时间《Mysql实战45讲》的学习笔记，并结合《高性能Mysql》，梳理了索引相关的知识点，总结了一些常见问题，并记录了一些比较实用的方法。
锁的类型
全局锁
- 全局锁就是对整个数据库实例加锁
- 命令是 Flush tables with read lock (FTWRL)
- 使用这个命令，其他线程的以下语句会被阻塞：数据更新语句（数据的增删改）、数据定义语句（包括建表、修改表结构等）和更新类事务的提交语句
- 全局锁的典型使用场景是，做全库逻辑备份。
既然要全库只读，为什么不使用 set global readonly=true 的方式呢？
- 有些情况下，readonly 的值会被用来做其他逻辑，比如用来判断一个库是主库还是备库。
- 在异常处理机制上有差异。FTERL在服务出现异常后，会自动释放全局锁。而readonly不会，将导致服务长期处在不能写入的状态。
- readonly 这个设置对于super 权限是无效的。
表锁
表锁大致可以分成两种：表锁，元数据锁（MDL锁）
表锁
- 表锁的语法是 lock tables … read/write，解锁unlock tables。客户端断开的时候自动释放。
- lock tables 语法除了会限制别的线程的读写外，也限定了本线程接下来的操作。
MDL 锁
- MDL 不需要显式使用，在访问一个表的时候会被自动加上,已保证读写的正确性
- 当对一个表做增删改查操作的时候，加 MDL 读锁；当要对表做结构变更操作的时候，加 MDL 写锁
- MDL 锁，在语句执行开始时申请，但是语句结束后并不会马上释放，而会等到整个事务提交后再释
如何安全地给小表加字段？首先，干掉长事务，避免不必要的锁等待。其次，设置等待时间，反复重试。undefined查询MDL锁可以使用：
show processlist // 需要设置performance_schema on 大约会有10%性能损失行锁
（各个引擎对于行锁的实现方式不一样）
与表锁的一些比较：
- 表锁：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低
- 行锁：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高 查询行锁可以使用：
select * from t sys.innodb_lock_waits where locked_table = 'db'.'table'两阶段锁
在 InnoDB 事务中，行锁是在需要的时候才加上的，但并不是不需要了就立刻释放，而是要等到事务结束时才释放。
因此，对于一个事务中语句执行顺序有一个大致的原则：如果你的事务中需要锁多个行，要把最可能造成锁冲突、最可能影响并发度的锁尽量往后放
Record Locks 行记录锁
UPDATE users SET name = "saurfang" WHERE id = 3;最基本的锁，锁住ID为1的这一行数据。锁会加在索引上，如果没有主键索引，那么会加在row_id上。如果查询的是二级索引，会回到主键索引上，并加锁。
当查询没有索引时，会走全表，把查到的每一行都加锁，在RC（读提交）下，加锁的语句执行完成后，就会直接释放掉不符合要求的行锁。因此，如果一条更新语句没有走索引，会花费极大的开销。
Gap Locks 间隙锁
我们之前提到过一个幻读的问题，在RR（可重复读）的隔离级别下，解决方法就是间隙锁。
间隙锁，锁住的是两个行之间的数据，不允许其他人向中间写入一个数据。比如在2-4 之间加上间隙锁，那么其他人在写入 3的时候就不会成功。
以上面的sql 为例子，MySQL会给id=3 这行的前后索引之间的间隙都加上锁。当多个事务同时持有这一行间隙锁的时候是不会出现冲突的，因为跟间隙锁存在冲突关系的，是“往这个间隙中插入一个记录”这个操作。 （这里的写入指的是insert，更新操作是不会被锁住的）。
实际上，目前遇到的多数业务中，对于数据一致性的要求不是非常非常的高，出现幻读也不是非常严重的问题，可以把隔离级别降到RC（读提交）这样可以提高并发性。间隙锁虽然彼此不冲突，本身也是花费一些开销，而且会和写入操作发生冲突，影响并发。另外，所谓“间隙”，其实根本就是由“这个间隙右边的那个记录”定义的。
Next-Key Locks
可以认为是记录锁和间隙锁的组合。是一个前开后闭区间。比如上边的sql，Next-Key Locks加的锁就是(2,3]。意思就是，加了（2，3）的间隙锁，又加了3 的行锁
无论主键索引还是二级索引，都会加上间隙锁。Next-Key Locks 因为包含行锁，会出现冲突。
Insert Intention Locks 插入意向锁
只有在insert的时候会使用，和间隙锁冲突，但是彼此不冲突。比如两个写入的事务都有（1，5）的意向锁，一个写入2，一个写入4，不会发生冲突。如果（1，5）之间有间隙锁，那么他们都会个间隙锁发生冲突。
读写锁与意向锁
锁的标志
- LOCK_IS：读意向锁；
- LOCK_IX：写意向锁；
- LOCK_S：读锁；
- LOCK_X：写锁；
- LOCK_AUTO_INC：自增锁；
读写锁
- 读锁，又称共享锁（Share locks，简称 S 锁），加了读锁的记录，所有的事务都可以读取，但是不能修改，并且可同时有多个事务对记录加读锁
- 写锁，又称排他锁（Exclusive locks，简称 X 锁），或独占锁，对记录加了排他锁之后，只有拥有该锁的事务可以读取和修改，其他事务都不可以读取和修改，并且同一时间只能有一个事务加写锁。
- 以上都基于当前读。在快照读下，无论加不加锁，都可以直接读。
读写意向锁
表锁和行锁是互相冲突的。如果一个行锁只锁住了一行数据，这时要申请一下表锁，那么会遍历表，看看是否存在行锁，开销很大。为了解决这个问题，会先在表上加上意向锁，然后再执行行锁操作。这样就可以避免上述问题。
意向锁之间是不会产生冲突的，也不和 AUTO_INC 表锁冲突，它只会阻塞表级读锁或表级写锁，另外，意向锁也不会和行锁冲突，行锁只会和行锁冲突。
- 意向锁之间互不冲突；
- S 锁只和 S/IS 锁兼容，和其他锁都冲突；
- X 锁和其他所有锁都冲突；
- AI 锁只和意向锁兼容；
加锁的规则
根据极客时间的《Mysql实战45讲》中的说明，对于加锁的基本规则大致为5个，包含了两个“原则”、两个“优化”和一个“bug”：
- 原则 1：加锁的基本单位是 next-key lock。希望你还记得，next-key lock 是前开后闭区间
- 原则 2：查找过程中访问到的对象才会加锁
- 优化 1：索引上的等值查询，给唯一索引加锁的时候，next-key lock 退化为行锁
- 优化 2：索引上的等值查询，向右遍历时且最后一个值不满足等值条件的时候，next-key lock 退化为间隙锁
- 一个 bug：唯一索引上的范围查询会访问到不满足条件的第一个值为止
几个经典的例子
CREATE TABLE `user` (
`id` int NOT NULL,
`name` int DEFAULT NULL,
`age` int DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `age` (`age`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
BEGIN;
INSERT INTO `user` VALUES (1, 0, 0);
INSERT INTO `user` VALUES (2, 1, 5);
INSERT INTO `user` VALUES (3, 2, 10);
INSERT INTO `user` VALUES (5, 3, 15);
INSERT INTO `user` VALUES (10, 4, 20);
INSERT INTO `user` VALUES (15, 5, 25);
COMMIT;假设我们数据库里的数据是这样的。我们对sql依次分析：
第一类：主键等值查询与普通索引等值查询
update user set age = 15 where id = 5;首先加上（3，5]的next-key lock（原则1）；然后找到了id=5 的这一行，next-key lock退化为行锁（优化1）。此时其他事务可以写入一个ID=4的数据。
update user set age = 15 where id = 4; #主键索引首先，ID=4 不存在，向后查到ID=5加上（3，5]的next-key lock（原则1）；然后next-key lock退化为间隙锁（优化2）锁住了（3，5）。此时其他事务无法写入一个ID=4的数据。
select ID from user where age = 10 lock in share mode #普通索引 覆盖索引这个语句走了普通索引，只查询id，所以会走覆盖索引，不用回表。此时会查到age (5,10],然后退化为age=10 的行锁(优化1)，继续往后查到(10,15]，然后退化为间隙锁(10,15)(优化2)。
注意！ 根据原则2，此时主键索引不会加锁。因此，通过主键更新name 不会被锁住，原因是当前索引上没有name这个字段，但写入一行数据会被锁住。
lock in share mode 只锁覆盖索引。for update ，系统会认为你接下来要更新数据，因此会顺便给主键索引上满足条件的行加上行锁。
第二类：范围查询
select * from user where id>=10 and id<11 for update; #主键范围查询首先，给ID=10 加行锁（同上边）；然后，继续向后查到ID=15，加(10,15]的next-key lock。此时实际加锁范围是10，15
select * from user where age >=10 and age<11 for update; #普通索引范围查询首先，给普通索引（5，10]加锁，此时age 索引不是唯一索引，不能走优化1；然后，继续向后查到ID=15，加(10,15]的next-key lock。此时实际加锁范围是（5，15]
select * from user where id >5 and id <= 10 for update; #主键范围查询按理说，给（5,10]加上锁就行了，实际上，会继续向后查，给(10,15]加上锁。这就是上面说的BUG
加锁的顺序
我们前面说加锁指的是 next-key lock。实际的加锁顺序分成两步，第一步加间隙锁，第二步加行锁。我们之前说，间隙锁彼此不冲突，一个间隙可以很多个事务持有间隙锁，但是行锁只有一个事务持有，其他就处在等待状态了。
在执行过程中，通过树搜索的方式定位记录的时候，用的是“等值查询”的方法。
当查询很多行数据时，锁是一个一个加上去的，并不是一起加的。 在实际工作中，可以遵循以下规则：
- 由于锁是一个个加的，要避免死锁，对同一组资源，要按照尽量相同的顺序访问；
- for update 这条语句占有的资源更多，回滚成本更大，所以 InnoDB 选择了回滚成本更小的 lock in share mode 语句回滚。
死锁与死锁检测
死锁
当并发系统中不同线程出现循环资源依赖，涉及的线程都在等待别的线程释放资源时，就会导致这几个线程都进入无限等待的状态，称为死锁。
举一个简单的死锁例子:
#TRANSACTION 1
begin;
update user set age = age + 1 where id = 1;
update user set age = age + 1 where id = 2;
#TRANSACTION 2
begin;
update user set age = age + 1 where id = 2;
update user set age = age + 1 where id = 1;此时，事务1 在等事务2 放开 ID= 2 的行锁，事务2在等事务1 放开ID=1 的行锁，出现了死锁。
死锁检测
处理死锁的两个策略:
- 直接进入等待，直到超时。这个超时时间可以通过参数 innodb_lock_wait_timeout 来设置
- 发起死锁检测，发现死锁后，主动回滚死锁链条中的某一个事务，让其他事务得以继续执行。将参数 innodb_deadlock_detect 设置为 on，表示开启这个逻辑
在 InnoDB 中，innodb_lock_wait_timeout 的默认值是 50s，意味着出现死锁后，第一个被锁住的线程要过 50s 才会超时退出，其他线程才有可能继续执行。这个开销有点大。死锁检测，就是出现事务被锁，就检查下他所依赖的线程有没有被其他锁住。
死锁检测有一定的性能损耗，如果并发很大的话，会导致CPU负载很高，但是并发量却上不去。
show engine innodb status 可以用来排查死锁信息
引申：怎么解决由这种热点行更新导致的性能问题
- 如果业务上不会出现死锁的情况发生，那就把死锁检测关闭了。
- 控制并发度，把同时需要进行死锁检测的并发降到合理的范围。难度较大
- 分而治之，将一行数据拆分为多行数据。将要加锁的行数进行分散，降低加锁的冲突。
insert时的情况
Insert 语句如果出现唯一键冲突，会在冲突的唯一值上加共享的 next-key lock(S 锁)。因此，碰到由于唯一键约束导致报错后，要尽快提交或回滚事务，避免加锁时间过长
乐观锁与悲观锁
乐观锁与悲观锁，可以认为是一种基于业务需要的特殊的锁。其中，乐观锁需要依赖业务逻辑来实现，悲观锁则直接使用select……for update 来实现。
其他
- MDL作用是防止DDL和DML并发的冲突。
- 快照读不需要加锁，自然也就没有死锁检测。
- 并不是每次死锁检测都都要扫所有事务。A=B C=D，E=D 此时只会判断CDE 不会关注AB。
- 如果查询直接走了覆盖索引，没有走到主键索引上，那么就直接在覆盖索引上加锁(原则2)。
- 在删除数据的时候尽量加 limit，可以有效降低加锁的范围。既安全，又高效。
- 空表有间隙锁码？有，空表锁 (-supernum，supernum]
原创声明：本文系作者授权腾讯云开发者社区发表，未经许可，不得转载。
如有侵权，请联系 cloudcommunity@tencent.com 删除。
原创声明：本文系作者授权腾讯云开发者社区发表，未经许可，不得转载。
如有侵权，请联系 cloudcommunity@tencent.com 删除。