MySQL锁那些事儿

网友投稿 1346 2023-06-13

MySQL锁那些事儿

MySQL锁那些事儿

背景

数据库中有多个操作需要修改同一数据时,不可避免的会产生数据的脏读。这时就需要数据库具有良好的并发控制能力,这一切在 MySQL 中都是由服务器和存储引擎来实现的。解决并发问题最有效的方案是引入了锁的机制,锁在功能上分为共享锁 (shared lock) 和排它锁 (exclusive lock) 即通常说的读锁和写锁; 锁的粒度上分行锁和表锁,表级锁MySQL 里面表级别的锁有两种:一种是表锁,一种是元数据锁(meta data lock,MDL)

行锁种类

Next-Key Lock:锁定一个范围,并且锁定记录本上;Gap Lock:间隙锁,锁定一个范围,但不包含记录本上;Record Lock:单个行记录上的锁;

加锁规则

虽然 MySQL 的锁各式各样,但是有些基本的加锁原则是保持不变的,譬如:快照读是不加锁的,更新语句肯定是加排它锁的,RC 隔离级别是没有间隙锁的等等。这些规则整理如下:

常见语句的加锁

SELECT ... 语句正常情况下为快照读,不加锁;SELECT ... LOCK IN SHARE MODE 语句为当前读,加 S 锁;SELECT ... FOR UPDATE 语句为当前读,加 X 锁;常见的 DML 语句(如 INSERT、DELETE、UPDATE)为当前读,加 X 锁;常见的 DDL 语句(如 ALTER、CREATE 等)加表级锁,且这些语句为隐式提交,不能回滚;

表锁

表锁(分 S 锁和 X 锁)意向锁(分 IS 锁和 IX 锁)自增锁(一般见不到,只有在 innodb_autoinc_lock_mode = 0 或者 Bulk inserts 时才可能有)

行锁分析

行锁都是加在索引上的,最终都会落在聚簇索引上;加行锁的过程是一条一条记录加的;

锁冲突

S 锁和 S 锁兼容,X 锁和 X 锁冲突,X 锁和 S 锁冲突;

不同隔离级别下的锁

上面说 SELECT ... 语句正常情况下为快照读,不加锁;但是在 Serializable 隔离级别下为当前读,加 S 锁;RC 隔离级别下没有间隙锁和 Next-key 锁

SQL 的加锁分析

我们使用下面这张 students 表为例,其中 id 为主键,no(学号)为二级唯一索引,name(姓名)和 age(年龄)为二级非唯一索引,score(学分)无索引。

我们只分析最简单的一种 SQL,它只包含一个 WHERE 条件,等值查询或范围查询。虽然 SQL 非常简单,但是针对不同类型的列,我们还是会面对各种情况:

聚簇索引,查询命中:UPDATE students SET score = 100 WHERE id = 15;聚簇索引,查询未命中:UPDATE students SET score = 100 WHERE id = 16;二级非唯一索引,查询命中:UPDATE students SET score = 100 WHERE name = 'Tom';二级非唯一索引,查询未命中:UPDATE students SET score = 100 WHERE name = 'John';无索引:UPDATE students SET score = 100 WHERE score = 22;聚簇索引,范围查询:UPDATE students SET score = 100 WHERE id <= 20;二级索引,范围查询:UPDATE students SET score = 100 WHERE age <= 23;

聚簇索引,查询命中

语句 UPDATE students SET score = 100 WHERE id = 15 在 RC 和 RR 隔离级别下加锁情况一样,都是对 id 这个聚簇索引加 X 锁,如下:

聚簇索引,查询未命中

如果查询未命中记录,在 RC 和 RR 隔离级别下加锁是不一样的,因为 RR 有 GAP 锁。语句 UPDATE students SET score = 100 WHERE id = 16 在 RC 和 RR 隔离级别下的加锁情况如下(RC 不加锁):

二级非唯一索引,查询命中

如果查询命中的是二级非唯一索引,在 RR 隔离级别下,还会加 GAP 锁。语句 UPDATE students SET score = 100 WHERE name = 'Tom' 加锁如下:

数一数上方右图中的锁你可能会觉得一共加了 7 把锁,实际情况不是,要注意的是 (Tom, 37) 上的记录锁和它前面的 GAP 锁合起来是一个 Next-key 锁,这个锁加在 (Tom, 37) 这个索引上,另外 (Tom, 49) 上也有一把 Next-key 锁。那么最右边的 GAP 锁加在哪呢?右边已经没有任何记录了啊。其实,在 InnoDB 存储引擎里,每个数据页中都会有两个虚拟的行记录,用来限定记录的边界,分别是:Infimum Record 和 Supremum Record,Infimum 是比该页中任何记录都要小的值,而 Supremum 比该页中最大的记录值还要大,这两条记录在创建页的时候就有了,并且不会删除。上面右边的 GAP 锁就是加在 Supremum Record 上。所以说,上面右图中共有 2 把 Next-key 锁,1 把 GAP 锁,2 把记录锁,一共 5 把锁。

二级非唯一索引,查询未命中

如果查询未命中记录,RR 隔离级别会加 GAP 锁,RC 无锁。语句 UPDATE students SET score = 100 WHERE name = 'John' 加锁情况如下:

无索引

如果 WHERE 条件不能走索引,MySQL 会如何加锁呢?有的人说会在表上加 X 锁,也有人说会根据 WHERE 条件将筛选出来的记录在聚簇索引上加上 X 锁,那么究竟如何,我们看下图:

在没有索引的时候,只能走聚簇索引,对表中的记录进行全表扫描。在 RC 隔离级别下会给所有记录加行锁,在 RR 隔离级别下,不仅会给所有记录加行锁,所有聚簇索引和聚簇索引之间还会加上 GAP 锁。

语句 UPDATE students SET score = 100 WHERE score = 22 满足条件的虽然只有 1 条记录,但是聚簇索引上所有的记录,都被加上了 X 锁。那么,为什么不是只在满足条件的记录上加锁呢?这是由于 MySQL 的实现决定的。如果一个条件无法通过索引快速过滤,那么存储引擎层面就会将所有记录加锁后返回,然后由 My*** 层进行过滤,因此也就把所有的记录都锁上了。

聚簇索引,范围查询

上面所介绍的各种情况其实都是非常常见的 SQL,它们有一个特点:全部都只有一个 WHERE 条件,并且都是等值查询。那么问题来了,如果不是等值查询而是范围查询,加锁情况会怎么样呢?

SQL 语句为 UPDATE students SET score = 100 WHERE id <= 20,按理说我们只需要将 id = 20、18、15 三条记录锁住即可,但是看右边的图,在 RR 隔离级别下,我们还把 id = 30 这条记录以及 (20, 30] 之间的间隙也锁起来了,很显然这是一个 Next-key 锁。

二级索引,范围查询

然后我们把范围查询应用到二级非唯一索引上来,SQL 语句为:UPDATE students SET score = 100 WHERE age <= 23,加锁情况如下图所示:

可以看出和聚簇索引的范围查询一样,除了 WHERE 条件范围内的记录加锁之外,后面一条记录也会加上 Next-key 锁,这里有意思的一点是,尽管满足 age = 24 的记录有两条,但只有第一条被加锁,第二条没有加锁,并且第一条和第二条之间也没有加锁。

metadata lock

元数据锁(meta data lock,MDL)不需要显式使用,在访问一个表的时候会被自动加上。MDL 的作用是,保证读写的正确性。可以想象一下,如果一个查询正在遍历一个表中的数据,而执行期间另一个线程对这个表结构做变更,删了一列,那么查询线程拿到的结果跟表结构对不上,肯定是不行的。因此,在 MySQL 5.5 版本中引入了 MDL,当对一个表做增删改查操作的时候,加 MDL 读锁;当要对表做结构变更操作的时候,加 MDL 写锁。读锁之间不互斥,因此可以有多个线程同时对一张表增删改查。读写锁之间、写锁之间是互斥的,用来保证变更表结构操作的安全性,因此,如果有两个线程要同时给一个表加字段,其中一个要等另一个执行完才能开始执行。

虽然 MDL 锁是系统默认会加的,但却是你不能忽略的一个机制,比如下面这个例子,经常看到有人掉到这个坑里:给一个小表加个字段,导致整个库挂了。你肯定知道,给一个表加字段,或者修改字段,或者加索引,需要扫描全表的数据。在对大表操作的时候,需要特别小心,以免对线上服务造成影响。而实际上,即使是小表操作不慎也会出问题。

我们来看一下下面的操作序列,假设表 t2 是一个小表。备注:这里的实验环境是 MySQL 5.7

Session a

Session b

Session c

Session d

begin;

select * from t2 limit 1;

select * from t2 limit 1;

alter table t2 add f int;(blocked)

select * from t2 limit 1;(blocked)

我们可以看到 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 锁;

Online DDL的过程是这样的:

1. 拿MDL写锁

2. 降级成MDL读锁

3. 真正做DDL

4. 升级成MDL写锁

5. 释放MDL锁

1、2、4、5如果没有锁冲突,执行时间非常短。第3步占用了DDL绝大部分时间,这期间这个表可以正常读写数据,是因此称为“online ”

我们上面的例子,是在第一步就堵住了,拿不到MDL写锁

在MySQL中使用 create table2 as select * from table1时,语句执行完成之前 table1的mdl锁不会释放,且因持续持有S锁造成阻塞table1写操作;

insert into table1 select * from table2

在MySQL中使用insert into table1 select * from table2时,会对table2进行加锁,这个加锁分以下几种情况:

1.后面不带查询条件,不带排序方式

insert into table1 select * from table2: 此时MySQL是逐行加锁,每一行都锁

2.查询使用主键排序

insert into table1 select * from table2 order by id: 使用主键排序时,MySQL逐行加锁,每一行都锁

3.使用非主键排序

insert into table1 select * from table2 order by modified_date:使用非主键排序时,MySQL锁整个表

4.在查询条件中使用非主键筛选条件,

insert into table1 select * from table2 where modified_date>='2017-10-01 00:00:00'

使用非主键筛选条件时,MySQL逐行加锁,每一行都锁

结论:该句式导致写阻塞

建议:select ...outfile是不阻塞dml操作的,可以用select...into outfile 和 load data infile 的组合来代替insert...select完成插入操作。

实际场景中如何避免锁的资源竞争

让 SELECT 速度尽量快,尽量减少大的复杂的Query,将复杂的Query分拆成几个小的Query分步进行;

尽可能地建立足够高效的索引,让数据检索更迅速;

使用EXPLAIN SELECT来确定对于你的查询中使用的索引跟预期一致

死锁案例

案例1

Session a

Session b

update students set score=100 where id=20;

pdate students set score=100 where id=30;

update students set score=100 where id=30;

update students set score=100 where id=20;

死锁的根本原因是有两个或多个事务之间加锁顺序的不一致导致的,这个死锁案例其实是最经典的死锁场景。

首先,事务 A 获取 id = 20 的锁(lock_mode X locks rec but not gap),事务 B 获取 id = 30 的锁;然后,事务 A 试图获取 id = 30 的锁,而该锁已经被事务 B 持有,所以事务 A 等待事务 B 释放该锁,然后事务 B 又试图获取 id = 20 的锁,这个锁被事务 A 占有,于是两个事务之间相互等待,导致死锁。

案例2

Session aSession b

update students set score=100 where id<30; update students set score=100 where age>23;

这个案例里每个事务都只有一条 SQL 语句,但可能会导致死锁问题,其实说起来,这个死锁和案例一并没有什么区别,只不过理解起来要更深入一点。要知道在范围查询时,加锁是一条记录一条记录挨个加锁的,所以虽然只有一条 SQL 语句,如果两条 SQL 语句的加锁顺序不一样,也会导致死锁。

在案例一中,事务 A 的加锁顺序为:id = 20 -> 30,事务 B 的加锁顺序为:id = 30 -> 20,正好相反,所以会导致死锁。这里的情景也是一样,事务 A 的范围条件为 id < 30,加锁顺序为:id = 15 -> 18 -> 20,事务 B 走的是二级索引 age,加锁顺序为:(age, id) = (24, 18) -> (24, 20) -> (25, 15) -> (25, 49),其中,对 id 的加锁顺序为 id = 18 -> 20 -> 15 -> 49。可以看到事务 A 先锁 15,再锁 18,而事务 B 先锁 18,再锁 15,从而形成死锁。

如何避免死锁

如上面的案例一和案例二所示,对索引加锁顺序的不一致很可能会导致死锁,所以如果可以,尽量以相同的顺序来访问索引记录和表。在程序以批量方式处理数据的时候,如果事先对数据排序,保证每个线程按固定的顺序来处理记录,也可以大大降低出现死锁的可能;

为表添加合理的索引,如果不走索引将会为表的每一行记录加锁,死锁的概率就会大大增大;

避免大事务,尽量将大事务拆成多个小事务来处理;因为大事务占用资源多,耗时长,与其他事务冲突的概率也会变高;

避免在同一时间点运行多个对同一表进行读写的脚本,特别注意加锁且操作数据量比较大的语句;我们经常会有一些定时脚本,避免它们在同一时间点运行;

设置锁等待超时参数:innodb_lock_wait_timeout(默认50s),这个参数并不是只用来解决死锁问题,在并发访问比较高的情况下,如果大量事务因无法立即获得所需的锁而挂起,会占用大量计算机资源,造成严重性能问题,甚至拖跨数据库。我们通过设置合适的锁等待超时阈值,可以避免这种情况发生。

版权声明:本文内容由网络用户投稿,版权归原作者所有,本站不拥有其著作权,亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容,请联系我们jiasou666@gmail.com 处理,核实后本网站将在24小时内删除侵权内容。

上一篇:MySQL主从复制问题总结及排查过程分享
下一篇:线上MySQL不可用,报错数据库无法连接
相关文章