以下内容来自于腾讯工程师joeycheng。
| 导语有一道关于数据库锁的面试题,发现其实很多DBA包括工作好几年的DBA都答的不太好,说明MySQL锁的机制其实还是比较复杂,值得深入研究。本文对3条简单的查询语句加锁情况进行分析,彻底搞清楚加锁细节。
首先来看这个面试题: 已知表t是innodb引擘,有主键:id(int类型) ,下面3条语句是否加锁?加锁的话,是什么锁? \1. select * from t where id=X; \2. begin;select * from t where id=X; \3. begin;select * from t where id=X for update;
这里其实有很多依赖条件,并不能一开始就给出一个很确定的答复。我们一层层展开来分析。
首先要知道MySQL有哪些锁,如上图所示,至少有12类锁(其中自增锁是事务向包含了AUTO_INCREMENT列的表中新增数据时会持有,predicate locks for spatial index 为空间索引专用,本文不讨论这2类锁)。
锁按粒度可分为分为全局,表级,行级3类。
对整个数据库实例加锁。 加锁表现:数据库处于只读状态,阻塞对数据的所有DML/DDL 加锁方式:Flush tables with read lock 释放锁:unlock tables(发生异常时会自动释放) 作用场景:全局锁主要用于做数据库实例的逻辑备份,与设置数据库只读命令set global readonly=true相比,全局锁在发生异常时会自动释放
对操作的整张表加锁, 锁定颗粒度大,资源消耗少,不会出现死锁,但会导致写入并发度低。具体又分为3类: 1)显式表锁:分为共享锁(S)和排他锁(X) 显示加锁方式:lock tables ... read/write 释放锁:unlock tables(连接中断也会自动释放) 2)Metadata-Lock(元数据锁):MySQL5.5版本开始引入,主要功能是并发条件下,防止session1的查询事务未结束的情况下,session2对表结构进行修改,保护元数据的一致性。在session1持有 metadata-lock的情况下,session2处于等待状态:show processlist可见Waiting for table metadata lock。其具体加锁机制如下:
- DML->先加MDL 读锁(SHARED_READ,SHARED_WRITE)
- DDL->先加MDL 写锁(EXCLUSIVE)
- 读锁之间兼容
- 读写锁之间、写锁之间互斥
3)Intention Locks(意向锁):意向锁为表锁(表示为IS或者IX),由存储引擎自己维护,用户无法干预。下面举一个例子说明其功能: 假设有2个事务:T1和T2 T1: 锁住表中的一行,只能读不能写(行级读锁)。 T2: 申请整个表的写锁(表级写锁)。 如T2申请成功,则能任意修改表中的一行,但这与T1持有的行锁是冲突的。故数据库应识别这种冲突,让T2的锁申请被阻塞,直到T1释放行锁。 有2种方法可以实现冲突检测: \1. 判断表是否已被其它事务用表锁锁住。 \2. 判断表中的每一行是否已被行锁锁住。 其中2需要遍历整个表,效率太低。因此innodb使用意向锁来解决这个问题: T1需要先申请表的意向共享锁(IS),成功后再申请某一行的记录锁S。 在意向锁存在的情况下,上面的判断可以改为: T2发现表上有意向共享锁IS,因此申请表的写锁被阻塞。
1.3 行锁
InnoDB引擘支持行级别锁,行锁粒度小,并发度高,但加锁开销大,也可能会出现死锁。 加锁机制:innodb行锁锁住的是索引页,回表时,主键的聚簇索引也会加上锁。
行锁具体类别如上图所示,包括:Record lock/Gap Locks/Next-Key Locks,每类又可分为共享锁(S)或者排它锁(X),一共2*3=6类,最后还有1类插入意向锁: **Record lock(记录锁):**最简单的行锁,仅仅锁住一行。记录锁永远都是加在索引上的,即使一个表没有索引,InnoDB也会隐式的创建一个索引,并使用这个索引实施记录锁。 Gap Locks(间隙锁):加在两个索引值之间的锁,或者加在第一个索引值之前,或最后一个索引值之后的间隙。使用间隙锁锁住的是一个区间,而不仅仅是这个区间中的每一条数据。间隙锁只阻止其他事务插入到间隙中,不阻止其它事务在同一个间隙上获得间隙锁,所以 gap x lock 和 gap s lock 有相同的作用。它是一个左开右开区间:如(1,3) Next-Key Locks:记录锁和间隙锁的组合,它指的是加在某条记录以及这条记录前面间隙上的锁。它是一个左开右闭区间:如(1,3】 Insert Intention(插入意向锁):该锁只会出现在insert操作执行前(并不是所有insert操作都会出现),目的是为了提高并发插入能力。它在插入一行记录操作之前设置一种特殊的间隙锁,多个事务在相同的索引间隙插入时,如果不是插入间隙中相同的位置就不需要互相等待。
TIPS:
1.不存在unlock tables … read/write,只有unlock tables 2.If a session begins a transaction, an implicit UNLOCK TABLES is performed
引入意向锁后,表锁之间的兼容性情况如下表:
总结:
- 意向锁之间都兼容
- X,IX和其它都不兼容(除了1)
- S,IS和其它都兼容(除了1,2)
- MySQL 5.6.16版本之前,需要建立一张特殊表innodb_lock_monitor,然后使用show engine innodb status查看
CREATE TABLE innodb_lock_monitor (a INT) ENGINE=INNODB;
DROP TABLE innodb_lock_monitor;
- MySQL 5.6.16版本之后,修改系统参数innodb_status_output后,使用show engine innodb status查看
set GLOBAL innodb_status_output=ON;
set GLOBAL innodb_status_output_locks=ON;
每15秒输出一次INNODB运行状态信息到错误日志
- MySQL5.7版本之后
可以通过information_schema.innodb_locks查看事务的锁情况,但只能看到阻塞事务的锁;如果事务并未被阻塞,则在该表中看不到该事务的锁情况
- MySQL8.0
删除information_schema.innodb_locks,添加performance_schema.data_locks,即使事务并未被阻塞,依然可以看到事务所持有的锁,同时通过performance_schema.table_handles、performance_schema.metadata_locks可以非常方便的看到元数据锁等表锁。
该表包含一个主键,一个唯一键和一个非唯一键:
CREATE TABLE t
(
id
int(11) NOT NULL,
a
int(11) DEFAULT NULL,
b
int(11) DEFAULT NULL,
c
varchar(10),
PRIMARY KEY (id
),
unique KEY a
(a
),
key b(b))
ENGINE=InnoDB;
insert into t values(1,10,100,'a'),(3,30,300,'c'),(5,50,500,'e');
对于innodb引擘来说,加锁的2个决定因素:
1)当前的事务隔离级别 2)当前记录是否存在
假设id为3的记录存在,则在不同的4个隔离级别下3个语句的加锁情况汇总如下表(select 3表示 select * from t where id=3):
隔离级别 | select 3 | begin;select 3 | begin;select 3 for update |
---|---|---|---|
RU | 无 | SHARED_READ | SHARED_WRITEIXX,REC_NOT_GAP:3 |
RC | 无 | SHARED_READ | SHARED_WRITEIXX,REC_NOT_GAP:3 |
RR | 无 | SHARED_READ | SHARED_WRITEIXX,REC_NOT_GAP:3 |
Serial | 无 | SHARED_READISS,REC_NOT_GAP:1 | SHARED_WRITEIXX,REC_NOT_GAP:3 |
分析:
- 使用以下语句在4种隔离级别之间切换: set global transaction_isolation='READ-UNCOMMITTED'; set global transaction_isolation='READ-COMMITTED'; set global transaction_isolation='REPEATABLE-READ'; set global transaction_isolation='Serializable';
- 对于auto commit=true,select 没有显式开启事务(begin)的语句,元数据锁和行锁都不加,是真的“读不加锁”
- 对于begin; select ... where id=3这种只读事务,会加元数据锁SHARED_READ,防止事务执行期间表结构变化,查询performance_schema.metadata_locks表可见此锁:
4.对于begin; select ... where id=3这种只读事务,MySQL在RC和RR隔离级别下,使用MVCC快照读,不加行锁,但在Serial隔离级别下,读写互斥,会加意向共享锁(表锁)和共享记录锁(行锁)
5.对于begin; select ... where id=3 for update,会加元数据锁SHARED_WRITE
6.对于begin; select ... where id=3 or update,4种隔离级别都会加意向排它锁(表锁)和排它记录锁(行锁),查询performance_schema.data_locks可见此2类锁
6 记录不存在时的加锁
隔离级别 | select 2 | begin;select 2 | begin;select 2 for update |
---|---|---|---|
RU | 无 | SHARED_READ | SHARED_WRITEIX |
RC | 无 | SHARED_READ | SHARED_WRITEIX |
RR | 无 | SHARED_READ | SHARED_WRITEIXX,GAP:3 |
Serial | 无 | SHARED_READISS,GAP:3 | SHARED_WRITEIXX,GAP:3 |
分析:
- 当记录不存在的时候,RU和RC隔离级别只有意向锁,没有行锁了
- RR,Serial隔离级别下,记录锁变成了**Gap Locks(间隙锁),可以防止幻读,**lock_data为3的GAP lock锁住区间(1,3),此时ID=2的记录插入会被阻塞。
那么对于主键范围查询,唯一键查询,非唯一键查询,在不同隔离级别下又是如何加锁的呢? 《从一道数据库面试题彻谈MySQL加锁机制(下)》再做分享。
原文作者:鹅厂架构师