基于读者反馈 肝了一周,这下彻底把 MySQL的锁搞懂了 排版有些不太清晰,特整理了一个新排版:
申明:本文基于 MySQL 8.0.30 版本,InnoDB 引擎
MySQL 数据库锁设计的初衷是处理并发问题,保证数据安全。MySQL 数据库锁可以从下面 3 个维度进行划分:
按照锁的使用方式,MySQL 锁可以分成共享锁、排他锁两种;
根据加锁的范围,MySQL 锁大致可以分成全局锁、表级锁和行锁三类;
从思想层面上看,MySQL 锁可以分为悲观锁、乐观锁两种;
我们先讲解共享锁和排它锁,然后讲解全局锁、表级锁和行锁,因为这三种类别的锁中,有些是共享锁,有些是排他锁,最后再讲解悲观锁和乐观锁。
1.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; |
给 user 表 id=3 的行加共享锁
加锁线程 sessionA | 线程 B sessionB | 线程 C sessionC |
---|---|---|
#开启事务begin; | ||
#给 user 表 id=3 的行加共享锁select * from userwhere 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 操作执行 okdelete from user where id = 3; |
通过上述两个实例可以看出:
当共享锁加在 user 表上,则其它事务可以再次获取 user 表的共享锁,其它事务再次获取 user 表的排他锁失败,操作被堵塞;
当共享锁加在 user 表 id=3 的行上,则其它事务可以再次获取 user 表 id=3 行上的共享锁,其它事务再次获取 user 表 id=3 行上的排他锁失败,操作被堵塞,但是事务可以再次获取 user 表 id!=3 行上的排他锁;
1.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; |
给 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; |
2.1 全局锁
全局锁,顾名思义,就是对整个数据库实例加锁,它是粒度最大的锁。
在 MySQL 中,通过执行 flush tables with read lock 指令加全局锁:
# 加全局锁
flush tables with read lock
指令执行完,整个数据库就处于只读状态了,其他线程执行以下操作,都会被阻塞:
数据更新语句被阻塞,包括 insert, update, delete 语句;
数据定义语句被阻塞,包括建表 create table,alter table、drop table 语句;
更新操作事务 commit 语句被阻塞;
MySQl 释放锁有 2 种方式:
执行 unlock tables 指令:unlock tables
加锁的会话断开,全局锁也会被自动释放
为了更好地说明全局锁,可以参照下面的执行顺序流和实例图:
加锁线程 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 |
通过上述的实例可以看出,当加全局锁时,库下面所有的表都处于只读状态,不管是当前事务还是其他事务,对于库下面所有的表只能读,不能执行 insert,update,delete,alter,drop 等更新操作。
全局锁的典型使用场景是做全库逻辑备份,在备份过程中整个库完全处于只读状态。如下图:
假如在主库上备份,备份期间,业务服务器不能对数据库执行更新操作,因此涉及到更新操作的业务就瘫痪了;
假如在从库上备份,备份期间,从库不能执行主库同步过来的 binlog,会导致主从延迟越来越大,如果做了读写分离,那么从库上获取数据就会出现延时,影响业务;
从上述分析可以看出,使用全局锁进行数据备份,不管是在主库还是在从库上进行备份操作,对业务总是不太友好。那不加锁行不行?我们可以通过下面还钱转账的例子,看看不加锁会不会出现问题:
备份前:账户 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.2 表级锁
MySQL 表级锁有两种:
表锁
元数据锁(metadata lock,MDL)
2.2.1 表锁
表锁就是对整张表加锁,包含读锁和写锁,由 MySQL Server 实现,表锁需要显示加锁或释放锁,具体指令如下:#给表加写锁lock tables tablename write;
# 给表加读锁
lock tables tablename read;
# 释放锁
unlock tables;
读锁:代表当前表为只读状态,读锁是一种共享锁。需要注意的是,读锁除了会限制其它线程的操作外,也会限制加锁线程的行为,具体限制如下:
加锁线程只能对当前表进行读操作,不能对当前表进行更新操作,不能对其它表进行所有操作;
其它线程只能对当前表进行读操作,不能对当前表进行更新操作,可以对其它表进行所有操作;
为了更好地说明读锁,可以参照下面的执行顺序流和实例图:
加锁线程 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 |
写锁:写锁是一种独占锁,需要注意的是,写锁除了会限制其它线程的操作外,也会限制加锁线程的行为,具体限制如下:
加锁线程对当前表能进行所有操作,不能对其它表进行任何操作;
其它线程不能对当前表进行任何操作,可以对其它表进行任何操作;
为了更好的说明写锁,可以参照下面的执行顺序流和实例图:
加锁线程 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 |
2.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 |
为了更好的说明 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 |
2.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 |
加锁线程 sessionA | 线程 B sessionB |
---|---|
#开启事务 begin; |
|
#user 表 id=6 加排他锁,默认 user 表会 加上 IX 锁 select * from user where id = 6 for update; |
|
# 观察 IX 锁 select* from performance_schema.data_locksG |
2.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 锁,可以参照下面的顺序执行流和实例图:
表级锁的兼容性
下面的图表总结了表级锁类型的兼容性
X | IX | S | IS | |
---|---|---|---|---|
X | 冲突 | 冲突 | 冲突 | 冲突 |
IX | 冲突 | 兼容 | 冲突 | 兼容 |
S | 冲突 | 冲突 | 兼容 | 兼容 |
IS | 冲突 | 兼容 | 兼容 | 兼容 |
2.3 行锁
行锁是针对数据表中行记录的锁。MySQL 的行锁是在引擎层实现的,并不是所有的引擎都支持行锁,比如,InnoDB 引擎支持行锁而 MyISAM 引擎不支持。
InnoDB 引擎的行锁主要有四类:
Record Lock:记录锁,是在索引记录上加锁;
Gap Lock:间隙锁,锁定一个范围,但不包含记录;
Next-key Lock:Gap Lock + Record Lock,锁定一个范围(Gap Lock 实现),并且锁定记录本身(Record Lock 实现);
插入意向锁;
2.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 |
2.3.2 Gap Lock
Gap Lock:间隙锁,锁住两个索引记录之间的间隙上,由 InnoDB 隐式添加。比如(1,3) 表示锁住记录 1 和记录 3 之间的间隙,这样记录 2 就无法插入,间隙可能跨越单个索引值、多个索引值,甚至是空。
为了更好的说明 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 |
上图中,事务 A(sessionA)在加共享锁的时候产生了间隙锁(Gap Lock),事务 B(sessionB)对间隙中进行 insert/update 操作,需要先获取排他锁(X),导致阻塞。事务 C(sessionC)通过"show engine innodb statusG" 指令可以查看到间隙锁的存在。需要说明的,间隙锁只是锁住间隙内部的范围,在间隙外的 insert/update 操作不会受影响。
Gap Lock 锁,只存在于可重复读隔离级别,目的是为了解决可重复读隔离级别下幻读的现象。
2.3.3 Next-Key Lock
Next-Key 锁,称为临键锁,它是 Record Lock + Gap Lock 的组合,用来锁定一个范围,并且锁定记录本身锁,它是一种左开右闭的范围,可以用符号表示为:(a,b]。
为了更好的说明 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 |
上图中,事务 A(sessionA)在加共享锁的时候产生了间隙锁(Gap Lock),事务 B(sessionB)对间隙中进行 insert 操作,需要先获取排他锁(X),导致阻塞。事务 C(sessionC)对间隙中进行 update 操作,需要先获取排他锁(X),导致阻塞。事务 D(sessionD)通过"show engine innodb statusG" 指令可以查看到间隙锁的存在。需要说明的,间隙锁只是锁住间隙内部的范围,在间隙外的 insert/update 操作不会受影响。
2.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); |
三、乐观锁 & 悲观锁
在 MySQL 中,无论是悲观锁还是乐观锁,都是人们对概念的一种思想抽象,它们本身还是利用 MySQL 提供的锁机制来实现的。其实,除了在 MySQL 数据,像 JAVA 语言里面也有乐观锁和悲观锁的概念。
悲观锁,可以理解成:在对任意记录进行修改前,先尝试为该记录加上排他锁(exclusive locking),采用的是先获取锁再操作数据的策略,可能会产生死锁;
乐观锁,是相对悲观锁而言,一般不会利用数据库的锁机制,而是采用类似版本号比较之类的操作,因此乐观锁不会产生死锁的问题;
四、死锁 & 死锁检测
当并发系统中不同线程出现循环资源依赖,涉及的线程都在等待别的线程释放资源时,就会导致这几个线程都进入无限等待的状态,称为死锁。可以通过下面的指令查看死锁:
show engine innodb statusG
当出现死锁以后,有两种策略:
直接进入等待,直到超时。这个超时时间可以通过参数 innodb_lock_wAIt_timeout 来设置,InnoDB 中 innodb_lock_wait_timeout 的默认值是 50s。
发起死锁检测,发现死锁后,主动回滚死锁链条中的某一个事务,让其它事务得以继续执行。将参数 innodb_deadlock_detect 设置为 on,表示开启死锁检测。
五、总结
本文基于 MySQL 8.0.30 版本和 InnoDB 引擎,对 MySQL 中的锁进行了讲解,每种锁都有其特定的使用场景。作为经常和 MySQL 打交道的 Java 程序员来说,对 MySQL 锁了解的越深,越可以帮助我们更好的去写出高性能的 SQL 语句。