本文首发于公众号“猿java”
最近,某同事在生产上遇到一个 MySQL 死锁的问题,于是在帮忙解决问题后,特意花了一周的时间,把 MySQL 所有的锁都整理了一遍,今天就来一起聊聊 MySQL锁。
申明:本文基于 MySQL 8.0.30 版本,InnoDB 引擎
MySQL 数据库锁设计的初衷是处理并发问题,保证数据安全。MySQL 数据库锁可以从下面 3个维度进行划分:
- 按照锁的使用方式,MySQL 锁可以分成共享锁、排它锁两种;
- 根据加锁的范围,MySQL 锁大致可以分成全局锁、表级锁和行锁三类;
- 从思想层面上看,MySQL 锁可以分为悲观锁、乐观锁两种;
我们会先讲解共享锁和排它锁,然后讲解全局锁、表级锁和行锁,因为这三种类别的锁中,有些是共享锁,有些是排他锁,最后,我们再讲解 悲观锁和乐观锁。
为什么要掌握 MySQL 锁
作为经常和 MySQL 数据库打交道的程序员来说,编写业务 SQL 语句是在所难免,编写高性能的 SQL 语句更是一种技术能力的体现。锁作为 MySQL 数据库一个非常重要的知识点,不但是面试中的高频问题,更是通往技术高P岗位的必备技能。
共享锁&排他锁
1.共享锁
共享锁,Share lock,也叫读锁。它是指当对象被锁定时,允许其它事务读取该对象,也允许其它事务从该对象上再次获取共享锁,但不能对该对象进行写入。 加锁方式是:
# 方式1
select ... lock in share mode;
# 方式2
select ... for share;
如果事务T1 在某对象持有共享(S)锁,则事务T2 需要再次获取该对象的锁时,会出现下面两种情况: - 如果T2 获取该对象的共享(S)锁,则可以立即获取锁; - 如果T2 获取该对象的排他(X)锁,则无法获取锁;
为了更好的理解上述两种情况,可以参照下面的执行顺序流和实例图:
给user表加共享锁
加锁线程 sessionA | 线程B sessionB |
---|---|
#开启事务 begin; | |
#对user整张表加共享锁 select * from user lock in share mode; | |
#获取user表上的共享锁ok,select操作成功执行 select * from user; | |
#获取user表上的排他锁失败,操作被堵塞 delete from user where id = 1; | |
#提交事务 #user表上的共享锁被释放 commit; | |
#获取user表上的排他锁成功,delete操作执行ok delete from user where id = 1; |
img.png
给user表id=3的行加共享锁
加锁线程 sessionA | 线程B sessionB | 线程C sessionC |
---|---|---|
#开启事务 begin; | ||
#给user表id=3的行加共享锁 select * from user where id = 3 lock in share mode; | ||
#获取user表id=3行上的共享锁ok #select操作执行成功 select * from user where id=3; | #获取user表id=3行上的共享锁ok #select操作执行成功 select * from user where id=3; | |
#获取user表id=3行上的排它锁失败 #delete操作被堵塞 delete from user where id = 3; | #获取user表id=4行上的排它锁成功 #delete操作执行成功 delete from user where id = 4; | |
#提交事务 #user表id=3的行上共享锁被释放 commit; | ||
#获取user表id=3行上的排它锁成功 #被堵塞的delete操作执行ok delete from user where id = 3; |
img.png
通过上述两个实例可以看出: - 当共享锁加在user表上,则其它事务可以再次获取user表的共享锁,其它事务再次获取user表的排他锁失败,操作被堵塞; - 当共享锁加在user表id=3的行上,则其它事务可以再次获取user表id=3行上的共享锁,其它事务再次获取user表id=3行上的排他锁失败,操作被堵塞,但是事务可以再次获取user表id!=3行上的排他锁;
2. 排它锁
排它锁,Exclusive Lock,也叫写锁或者独占锁,主要是防止其它事务和当前加锁事务锁定同一对象。同一对象主要有两层含义: - 当排他锁加在表上,则其它事务无法对该表进行insert,update,delete,alter,drop等更新操作; - 当排他锁加在表的行上,则其它事务无法对该行进行insert,update,delete,alter,drop等更新操作;
排它锁加锁方式为:
select ... for update;
为了更好的说明排他锁,可以参照下面的执行顺序流和实例图:
给user表对象加排他锁
加锁线程 sessionA | 线程B sessionB |
---|---|
#开启事务 begin; | |
#对user整张表加排他锁 select * from user for update; | |
#获取user表上的共享锁ok,select执行成功 select * from user; | |
#获取user表上的排他锁失败,操作被堵塞 delete from user where id=3; | |
#提交事务 #user表上的排他被释放 commit; | |
#获取user表上的排他锁成功,操作执行ok delete from user where id = 1; |
img.png
给user表id=3的行对象加排他锁
加锁线程 sessionA | 线程B sessionB | 线程C sessionC |
---|---|---|
#开启事务 begin; | ||
#给user表id=3的行加排他锁 select * from user where id = 3 for update; | ||
#获取user表id=3行上的共享锁ok select * from user where id=3; | #获取user表id=3行上的共享锁ok select * from user where id=3; | |
#获取user表id=3行上的排它锁失败 delete from user where id = 3; | #获取user表id=4行上的排它锁成功 delete from user where id = 4; | |
#提交事务 #user表id=3的行上排他锁被释放 commit; | ||
#获取user表id=3行上的排它锁成功 #被堵塞的delete操作执行ok delete from user where id = 3; |
img.png
全局锁&表级锁&行锁
1. 全局锁
1.1 定义
全局锁,顾名思义,就是对整个数据库实例加锁。它是粒度最大的锁。
1.2 加锁
在MySQL中,通过执行 flush tables with read lock 指令加全局锁:
flush tables with read lock
指令执行完,整个数据库就处于只读状态了,其他线程执行以下操作,都会被阻塞: - 数据更新语句被阻塞,包括 insert, update, delete语句; - 数据定义语句被阻塞,包括建表 create table,alter table、drop table 语句; - 更新操作事务commit语句被阻塞;
1.3 释放锁
MySQl释放锁有2种方式:
为了更好的说明全局锁,可以参照下面的执行顺序流和实例图:
加锁线程 sessionA | 线程B sessionB |
---|---|
flush tables with read lock; 加全局锁 | |
select user表ok | select user表ok |
insert user表堵塞 | insert user表堵塞 |
delete user表堵塞 | delete user表堵塞 |
drop user 表堵塞 | drop user 表堵塞 |
alter user表 堵塞 | alter user表 堵塞 |
unlock tables; 解锁 | |
被堵塞的修改操作执行ok | 被堵塞的修改操作执行ok |
img.png
通过上述的实例可以看出,当加全局锁时,库下面所有的表都处于只能状态,不管是当前事务还是其他事务,对于库下面所有的表只能读,不能执行insert,update,delete,alter,drop等更新操作。
1.4 使用场景
全局锁的典型使用场景是做全库逻辑备份,在备份过程中整个库完全处于只读状态。如下图:
img.png
- 假如在主库上备份,备份期间,业务服务器不能对数据库执行更新操作,因此涉及到更新操作的业务就瘫痪了;
- 假如在从库上备份,备份期间,从库不能执行主库同步过来的 binlog,会导致主从延迟越来越大,如果做了读写分离,那么从库上获取数据就会出现延时,影响业务;
从上述分析可以看出,使用全局锁进行数据备份,不管是在主库还是在从库上进行备份操作,对业务总是不太友好。那不加锁行不行?我们可以通过下面还钱转账的例子,看看不加锁会不会出现问题:
img.png
- 备份前:账户A 有1000,账户B 有500
- 此时,发起逻辑备份
- 假如数据备份时不加锁,此时,客户端A 发起一个还钱转账的操作:账户A 往账户B 转200
- 当账户A 转出200完成,账户B 转入200 还未完成时,整个数据备份完成
- 如果用该备份数据做恢复,会发现账户A 转出了200,账户B 却没有对应的转入记录,这样就会产生纠纷:A 说我账户少了 200, B 说我没有收到,最后,A,B谁都不干。
既然不加锁会产生错误,加全局锁又会影响业务,那么有没有两全其美的方式呢?
有,MySQL官方自带的逻辑备份工具 mysqldump,具体指令如下:
mysqldump –single-transaction
执行该指令,在备份数据之前会先启动一个事务,来确保拿到一致性视图, 加上 MVCC 的支持,保证备份过程中数据是可以正常更新。但是,single-transaction方法只适用于库中所有表都使用了事务引擎,如果有表使用了不支持事务的引擎,备份就只能用 FTWRL 方法。
2. 表级锁
MySQL 表级锁有两种: 表锁 和 元数据锁(metadata lock,MDL)
2.1 表锁
表锁就是对整张表加锁,包含读锁和写锁,由MySQL Server实现,表锁需要显示加锁或释放锁,具体指令如下:
# 给表加写锁
lock tables tablename write;
# 给表加读锁
lock tables tablename read;
# 释放锁
unlock tables;
读锁:代表当前表为只读状态,读锁是一种共享锁。需要注意的是,读锁除了会限制其它线程的操作外,也会限制加锁线程的行为,具体限制如下: 1. 加锁线程只能对当前表进行读操作,不能对当前表进行更新操作,不能对其它表进行所有操作; 2. 其它线程只能对当前表进行读操作,不能对当前表进行更新操作,可以对其它表进行所有操作;
为了更好的说明读锁,可以参照下面的执行顺序流和实例图:
加锁线程 sessionA | 线程B sessionB |
---|---|
#给user表加读锁 lock tables user read; | |
select user表 ok | select user表 ok |
insert user表被拒绝 | insert user表堵塞 |
insert address表被拒绝 | insert address表ok |
select address表被拒绝 | alter user表堵塞 |
unlock tables; 释放锁 | |
被堵塞的修改操作执行ok |
img.png
写锁:写锁是一种独占锁,需要注意的是,写锁除了会限制其它线程的操作外,也会限制加锁线程的行为,具体限制如下: 1. 加锁线程对当前表能进行所有操作,不能对其它表进行任何操作; 2. 其它线程不能对当前表进行任何操作,可以对其它表进行任何操作;
为了更好的说明写锁,可以参照下面的执行顺序流和实例图:
加锁线程 sessionA | 线程B sessionB |
---|---|
#给user表加写锁 lock tables user write; | |
select user表 ok | select user表 ok |
insert user表被拒绝 | insert user表堵塞 |
insert address表被拒绝 | insert address表ok |
select address表被拒绝 | alter user表堵塞 |
unlock tables; 释放锁 | |
堵塞在user表的上更新操作执行ok |
img.png
2.2 MDL元数据锁
元数据锁:metadata lock,简称MDL,它是在MySQL 5.5版本引进的。元数据锁不用像表锁那样显式的加锁和释放锁,而是在访问表时被自动加上,以保证读写的正确性。加锁和释放锁规则如下:
- MDL读锁之间不互斥,也就是说,允许多个线程同时对加了 MDL读锁的表进行CRUD(增删改查)操作;
- MDL写锁,它和读锁、写锁都是互斥的,目的是用来保证变更表结构操作的安全性。也就是说,当对表结构进行变更时,会被默认加 MDL写锁,因此,如果有两个线程要同时给一个表加字段,其中一个要等另一个执行完才能开始执行。
- MDL读写锁是在事务commit之后才会被释放;
为了更好的说明 MDL读锁规则,可以参照下面的顺序执行流和实例图:
加锁线程 sessionA | 其它线程 sessionB |
---|---|
开启事务 begin; | |
select user表,user表会默认加上MDL读锁 | |
select user表ok | select user表ok |
insert user表ok | insert user表ok |
update user表ok | update user表ok |
delete user表ok | delete user表ok |
alter user表,获取MDL写锁失败,操作被堵塞 | |
commit;提交事务,MDL读锁被释放 | |
被堵塞的修改操作执行ok |
img.png
为了更好的说明 MDL写锁规则,可以参照下面的顺序执行流和实例图:
加锁线程 sessionA | 线程B sessionB | 线程C sessionC |
---|---|---|
#开启事务 begin; | ||
#user表会默认加上MDL读锁 select user表, | ||
select user表ok | select user表ok | select user表ok |
#获取MDL写锁失败 alter user表操作被堵塞 | #获取MDL读锁失败 select * from user; | |
提交事务,MDL读锁被释放 | ||
#MDL写锁被释放 被堵塞的alter user操作执行ok | ||
#被堵塞的select 操作执行ok |
img.png
2.3 意向锁
由于InnoDB引擎支持多粒度锁定,允许行锁和表锁共存,为了快速的判断表中是否存在行锁,InnoDB推出了意向锁。
意向锁,Intention lock,它是一种表锁,用来标识事务打算在表中的行上获取什么类型的锁。 不同的事务可以在同一张表上获取不同种类的意向锁,但是第一个获取表上意向排他(IX) 锁的事务会阻止其它事务获取该表上的任何 S锁 或 X 锁。反之,第一个获得表上意向共享锁(IS) 的事务可防止其它事务获取该表上的任何 X 锁。
意向锁通常有两种类型: - 意向共享锁(IS),表示事务打算在表中的各个行上设置共享锁。 - 意向排他锁(IX),表示事务打算对表中的各个行设置排他锁。
意向锁是InnoDB自动加上的,加锁时遵从下面两个协议: - 事务在获取表中行的共享锁之前,必须先获取表上的IS锁或更强的锁。 - 事务在获取表中行的排他锁之前,必须先获取表上的IX锁。
为了更好的说明意向共享锁,可以参照下面的顺序执行流和实例图:
加锁线程 sessionA | 线程B sessionB |
---|---|
#开启事务 begin; | |
#user表id=6加共享行锁 ,默认user表会 加上IS锁 select * from user where id = 6 for share; | |
# 观察IS锁 select * from performance_schema.data_locksG |
img.png
加锁线程 sessionA | 线程B sessionB |
---|---|
#开启事务 begin; | |
#user表id=6加排他锁,默认user表会 加上IX锁 select * from user where id = 6 for update; | |
# 观察IX锁 select * from performance_schema.data_locksG |
img.png
2.4 AUTO-INC锁
AUTO-INC锁是一种特殊的表级锁,当表中有AUTO_INCREMENT的列时,如果向这张表插入数据时,InnoDB会先获取这张表的AUTO-INC锁,等插入语句执行完成后,AUTO-INC锁会被释放。
AUTO-INC锁可以使用innodb_autoinc_lock_mode变量来配置自增锁的算法,innodb_autoinc_lock_mode变量可以选择三种值如下表:
innodb_autoinc_lock_mode | 含义 |
---|---|
0 | 传统锁模式,采用 AUTO-INC 锁 |
1 | 连续锁模式,采用轻量级锁 |
2 | 交错锁模式(MySQL8默认),AUTO-INC和轻量级锁之间灵活切换 |
为了更好的说明意AUTO-INC锁,可以参照下面的顺序执行流和实例图:
2.5 锁的兼容性
下面的图表总结了表级锁类型的兼容性
X | IX | S | IS | |
---|---|---|---|---|
X | 冲突 | 冲突 | 冲突 | 冲突 |
IX | 冲突 | 兼容 | 冲突 | 兼容 |
S | 冲突 | 冲突 | 兼容 | 兼容 |
IS | 冲突 | 兼容 | 兼容 | 兼容 |
3. 行锁
行锁是针对数据表中行记录的锁。MySQL 的行锁是在引擎层实现的,并不是所有的引擎都支持行锁,比如,InnoDB引擎支持行锁而 MyISAM引擎不支持。
InnoDB 引擎的行锁主要有四类:
3.1 Record Lock
Record Lock:记录锁,是针对索引记录的锁,锁定的总是索引记录。
例如,select id from user where id = 1 for update; for update 就显式在索引id上加行锁(排他锁),防止其它任何事务 update或delete id=1 的行,但是对user表的insert、alter、drop操作还是可以正常执行。
为了更好的说明 Record Lock锁,可以参照下面的执行顺序流和实例图:
加锁线程 sessionA | 线程B sessionB | 线程B sessionC |
---|---|---|
#开启事务 begin; | ||
给user表id=1加写锁 select id from user where id = 1 for update; | ||
update user set name = 'name121' where id = 1; | ||
查看 InnoDB监视器中记录锁数据 show engine innodb statusG | ||
commit提交事务 record lock 被释放 | ||
被堵塞的update操作执行ok |
img.png
3.2 Gap Lock
Gap Lock:间隙锁,锁住两个索引记录之间的间隙上,由InnoDB隐式添加。比如(1,3) 表示锁住记录1和记录3之间的间隙,这样记录2就无法插入,间隙可能跨越单个索引值、多个索引值,甚至是空。
img.png
为了更好的说明 Gap Lock间隙锁,可以参照下面的顺序执行流和实例图:
加锁线程 sessionA | 线程B sessionB | 线程C sessionC |
---|---|---|
#开启事务 begin; | ||
加锁 select * from user where age = 10 for share; | ||
insert into user(id,age) values(2,20); | ||
#查看 InnoDB监视器中记录锁数据 show engine innodb statusG | ||
commit提交事务 Gap Lock被释放 | ||
被堵塞的insert操作执行ok |
img.png
上图中,事务A(sessionA)在加共享锁的时候产生了间隙锁(Gap Lock),事务B(sessionB)对间隙中进行insert/update操作,需要先获取排他锁(X),导致阻塞。事务C(sessionC)通过"show engine innodb statusG" 指令可以查看到间隙锁的存在。需要说明的,间隙锁只是锁住间隙内部的范围,在间隙外的insert/update操作不会受影响。
Gap Lock锁,只存在于可重复读隔离级别,目的是为了解决可重复读隔离级别下幻读的现象。
3.3 Next-Key Lock
Next-Key锁,称为临键锁,它是Record Lock + Gap Lock的组合,用来锁定一个范围,并且锁定记录本身锁,它是一种左开右闭的范围,可以用符号表示为:(a,b]。
img.png
为了更好的说明 Next-Key Lock间隙锁,可以参照下面的顺序执行流和实例图:
加锁线程 sessionA | 线程B sessionB | 线程C sessionC | 线程D sessionD |
---|---|---|---|
#开启事务 begin; | |||
加锁 select * from user where age = 10 for share; | |||
#获取锁失败,insert操作被堵塞 insert into user(id,age) values(2,20); | |||
update user set name='name1' where age = 10; | #查看 InnoDB监视器中记录锁数据 show engine innodb statusG | ||
提交事务Gap Lock被释放 commit | |||
被堵塞的insert操作执行ok | 被堵塞的update操作执行ok |
img.png
上图中,事务A(sessionA)在加共享锁的时候产生了间隙锁(Gap Lock),事务B(sessionB)对间隙中进行insert操作,需要先获取排他锁(X),导致阻塞。 事务C(sessionC)对间隙中进行update操作,需要先获取排他锁(X),导致阻塞。 事务D(sessionD)通过"show engine innodb statusG" 指令可以查看到间隙锁的存在。需要说明的,间隙锁只是锁住间隙内部的范围,在间隙外的insert/update操作不会受影响。
3.4 Insert Intention Lock
插入意向锁,它是一种特殊的间隙锁,特指插入操作产生的间隙锁。
为了更好的说明 Insert Intention Lock锁,可以参照下面的顺序执行流和实例图:
加锁线程 sessionA | 线程B sessionB | 线程C sessionC |
---|---|---|
#开启事务 begin; | ||
加锁 select * from user where age = 10 for share; | ||
#获取锁失败,insert操作被堵塞 insert into user(id,age) values(2,20); | ||
#查看 InnoDB监视器中记录锁数据 show engine innodb statusG | ||
commit提交事务 Gap Lock被释放 | ||
#被堵塞的insert操作执行ok insert into user(id,age) values(2,20); |
img.png
乐观锁&悲观锁
在MySQL中,无论是悲观锁还是乐观锁,都是人们对概念的一种思想抽象,它们本身还是利用 MySQL提供的锁机制来实现的。其实,除了在MySQL数据,像 Java语言里面也有乐观锁和悲观锁的概念。
- 悲观锁,可以理解成:在对任意记录进行修改前,先尝试为该记录加上排他锁(exclusive locking),采用的是先获取锁再操作数据的策略,可能会产生死锁;
- 乐观锁,是相对悲观锁而言,一般不会利用数据库的锁机制,而是采用类似版本号比较之类的操作,因此乐观锁不会产生死锁的问题;
死锁和死锁检测
当并发系统中不同线程出现循环资源依赖,涉及的线程都在等待别的线程释放资源时,就会导致这几个线程都进入无限等待的状态,称为死锁。可以通过下面的指令查看死锁
show engine innodb statusG
当出现死锁以后,有两种策略:
- 一种策略是,直接进入等待,直到超时。这个超时时间可以通过参数 innodb_lock_wait_timeout 来设置,InnoDB 中 innodb_lock_wait_timeout 的默认值是 50s。
- 另一种策略是,发起死锁检测,发现死锁后,主动回滚死锁链条中的某一个事务,让其它事务得以继续执行。将参数 innodb_deadlock_detect 设置为 on,表示开启死锁检测。