文章/答案/技术大牛

发布

社区首页 >专栏 >Mysql学习笔记【锁】

Mysql学习笔记【锁】

原创

Porco1Rosso

修改于 2020-07-02 03:25:53

6390

文章被收录于专栏：程序猿的学习笔记程序猿的学习笔记

本文为极客时间《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 的行锁，出现了死锁。