MySQL 锁

Louis XIV原创2020/12/6...大约 9 分钟

按照加锁范围来分，MySQL锁可以分为全局锁、表级锁、行级锁、间隙锁、next-key lock。

全局锁

命令：flush tables with read lock(FTWRL)

加锁后所有更新类DML语句和DDL语句都会阻塞。主要用于全库备份。

另，进行全库备份并不一定要加全局锁，在可重复读隔离级别下，使用mysqldump加上 --single-transaction 参数开启一个事务也可以保证整个备份过程的数据一致性。

表级锁

显式表锁，命令：lock tables ... read/write

元数据锁 metadata lock MDL，另一种表级锁，访问一个表时会自动加上。对表做增删改查时加读锁，修改表结构时加写锁。

行级锁

行级锁锁住的是行，主要在执行DML语句的时候会加锁。包括select在使用lock in share mode和for update时也会加锁。行级锁加锁规则比较复杂，下面将单独介绍。

两阶段锁协议

锁是在执行sql时加上的，但并不立即释放，而是等待事务结束后释放，这称为两阶段锁协议。
因此在事务中如果需要锁定多个行，尽可能将会产生锁冲突的锁往后放，这样可以减少对锁的占用时间，提升并发量。

提示

从锁的行为分可以分为读锁和写锁。
读锁是共享锁，线程给表（记录）加上读锁，则所有线程都只能读不能写；
写锁是排他锁，给表（记录）加上写锁，则只有加锁的线程能读写，其他线程都无法读写。

间隙锁比较特殊，同一个间隙锁可以由多个线程同时获取，一旦加了间隙锁，相应区间内的insert操作会被阻塞。

间隙锁

幻读 phantom read：在事务A中对数据进行了insert或delete导致事务B中两次查询的结果不一致。幻读与可重复读的区别是：可重复读是针对数据update说的，幻读是针对记录增减来说的。

提示

在可重复读隔离级别下，普通select是一致性读，读取的是快照，不存在幻读问题。幻读只在当前读情况下会出现。

InnoDB引入间隙锁来解决幻读的问题，间隙锁只在可重复读隔离级别下才生效。读提交级别不存在间隙锁。

间隙锁 Gap Lock：锁定一个范围，但不包含记录本身，也就是一个开区间，如下例的(5,10)、(10,15)等

注意

一旦持有间隙锁，其他线程就不能在这个间隙内执行insert。

Next-key Lock：锁定左开右闭的区间，也就是间隙锁+行锁，如下例的(-∞,0]、(0,5]等

举例：
表结构如下，id是主键，c是普通索引。初始化6条记录，把表分成7个区间。

CREATE TABLE `t` ( 
	`id` int(11) NOT NULL, 
	`c` int(11) DEFAULT NULL, 
	`d` int(11) DEFAULT NULL, 
	PRIMARY KEY (`id`), 
	KEY `c` (`c`)
) ENGINE=InnoDB;

insert into t values(0,0,0),(5,5,5),(10,10,10),(15,15,15),(20,20,20),(25,25,25);

加锁规则

在可重复读级别下：

原则 1：加锁的基本单位是 next-key lock。next-key lock 是前开后闭区间。
原则 2：查找过程中访问到的对象才会加锁。
优化 1：索引上的等值查询，给唯一索引加锁的时候，next-key lock 退化为行锁。
优化 2：索引上的等值查询，向右遍历时且最后一个值不满足等值条件的时候，next-key lock 退化为间隙锁。
一个“bug”：唯一索引上的范围查询会访问到不满足条件的第一个值为止。

例1 无索引 for update

select * from t where d=5 for update

由于字段d没有索引，需要全表扫描，根据原则2，会对全表加锁，也就是加7个next-key lock

例2 等值查询间隙锁

update t set d=d+1 where id=7;

加锁单位是next-key lock，也就是(5,10]
由于是等值索引查询，右边界10不满足查询条件，最终加锁范围是(5,10)

例3 非唯一索引等值锁

// 事务A 加共享锁
begin;
select id from t where c = 5 lock in share mode;
// 事务B ok
update t set d=d+1 where id = 5;
// 事务C blocked
insert into t values(7,7,7);

这个例子比较奇怪，事务A打算给索引c=5加锁，更新id=5能正常更新，insert (7,7,7)却被锁住。因为：

加锁单位是next-key lock，也就是(0,5]
由于c是普通索引，需要继续遍历查到c=10不满足条件，但根据原则2，(5,10]也要加锁
由于是等值索引查询，且右边界10不满足条件，退化为间隙锁(5,10)
由于本例用到了覆盖索引，不需要访问主键索引即可完成查询。根据原则2，不需要在主键上加锁，因此与事务B不冲突
事务C被间隙锁(5,10)阻塞

注意在本例中使用lock in share mode只锁住了索引c，但如果改为for update，引擎判断可能要执行更新操作，会顺便给主键加上相应的行锁。

启示

尽量用唯一索引，可以减少锁住的范围
利用覆盖索引，可以减少回表，提升查询效率
for update一定会锁住主键，而lock in share mode在特定条件下可以不用锁主键

覆盖索引确认

使用explain sql查看执行计划，在Extra字段中如果是Using index表示使用了覆盖索引

例4 主键索引范围锁

mysql> select * from t where id=10 for update;
mysql> select * from t where id>=10 and id<11 for update;

二者是否有区别？

首先第一条语句是主键等值查询，毫无疑问加的是行锁。第二条语句在查到id=10后，由于是范围查询还要继续往下查找到id=15且不满足条件，根据访问即加锁原则，还需要加上next-key lock (10,15]，因此加的是行锁id=10和next-key lock (10,15]。

例5 非唯一索引范围锁

// 事务A
select * from t where c >= 10 and c < 11 for update;
// 事务B blocked
insert into t values(8,8,8);
// 事务C blocked
update t set d=d+1 where c=15;

与上一个例子不同的是，这里用的是非唯一索引c，范围查询，不适用优化规则，因此需要加next-key lock (5,10]和(10,15]。

所以上面的insert c=8也会锁住。

例6 唯一索引范围锁bug

// 事务A
begin;
select * from t where id > 10 and id <= 15 for update;
// 事务B blocked
update t set d=d+1 where id=20;
// 事务C blocked
insert into t values(16,16,16);

这里触发了唯一索引范围查询的“bug”，也许是设计如此。按常理事务A只需锁住next-key lock(10,15]，由于是唯一索引，没有必要继续检索数据，但具体实现还是继续向后检索到第一个不满足条件的值id=20，根据原则2，又加上了(15,20]这个锁。

因此事务B和事务C看似无关，但也被锁住。

例7 limit加锁

为便于说明，在表里新增一条数据

insert into t values(30,10,30);

这时在索引c=10上就有两条数据，分别是(10,10,10)和(30,10,30)

// 事务A
begin;
select * from t where c=10 for update;
// 事务B blocked
insert into t values(8,8,8);
// 事务C blocked
insert into t values(12,12,12);

通过以上几个例子，很容易解释这个实验，实际锁住的是索引c上的next-key lock(5,10]和间隙锁(10,15)，因此事务B和C全部锁住。

加上limit试一试：

// 事务A
begin;
select * from t where c=10 limit 1 for update;
// 事务B blocked
insert into t values(8,8,8);
// 事务C ok
insert into t values(12,12,12);
update t set d=d+1 where id=30;

加上limit的区别是只需要检索到(10,10,10)这条数据后就不需要再继续向后检索了，根据原则2，访问到的对象才加锁，实际锁住的是间隙锁(5,10)和行锁(10,10,10)，而(30,10,30)未加锁因此可以正常更新，(12,12,12)也可以插入成功

提示

使用limit可以减少锁的范围，至少可以省去一个右侧的间隙锁

间隙锁的问题

影响并发，显而易见，间隙越大，越会导致该间隙内的数据插入困难
可能造成死锁。例如下面的例子，两个事务在select之后同时持有间隙锁(5,10)，两个事务同时持有一个间隙锁是允许的。此时事务A insert id=9就必须等待事务B释放间隙锁，事务B不释放锁，也尝试insert就导致了死锁。

事务A	事务B
select * from t where id=9 for update;
	select * from t where id=9 for update;
INSERT INTO t VALUES(9,9,9); //blocked
	INSERT INTO t VALUES(9,9,9); //deadlock

由于MySQL有死锁检测机制，此例中的事务B会报错并回滚：ERROR: 1213 Deadlock found when trying to get lock; try restarting transaction

一个可选的解决办法是：调整隔离级别为读已提交，并设置binlog_format=row（如果业务上非必须要用到可重复读）

死锁

有几个参数可用于解决死锁

innodb_lock_wait_timeout: 锁等待时间，超时后线程会自动释放锁并退出，默认50s
innodb_deadlock_detect: 死锁检测开关，当检测到死锁时，主动回滚导致死锁链条中的某个事务。但需注意死锁检测需要遍历所有可能导致死锁的线程，在并发较高时检测效率很低。

以上参数只能调整在发现死锁时的应对，并不能防止死锁产生，避免死锁还需要从程序上解决。

产生死锁的四个条件：

资源互斥
不可抢占
请求保持。进程（线程）部分占有资源不释放，同时继续请求新的资源
循环等待