mysql到底是怎么加锁的？

作者：benjamim，腾讯TEG后台开发工程师

背景

在上一篇《浅谈MySQL数据可靠性》的文章中，简单地聊了一下锁在实现数据可靠性中的具体作用，这篇文章我想继续来聊聊mysql的锁是怎么加上的，为啥想聊这个呢？主要是因为业务中我们或多或少都会使用到锁，毕竟锁是保障我们数据安全性的关键法宝。 但是由于不了解原理，往往可能导致我们在”刻意“或者”无意“的使用场景下，带来潜在的性能问题，轻则导致处理能力降低，重则可能会拖垮我们的DB，因此需要对锁的原理以及使用场景有比较全面的了解，才能更好地驾驭，避免给我们带来不必要的业务隐患。我们主要从三个方面来讨论这个问题：

• 啥时候加？
• 如何加？
• 什么时候该加什么时候不该加？

啥时候加

显式锁

• select ... for update
• select ... in share mode

两者的区别在于前者加的是排它锁，后者加的是共享锁。加了排他锁之后，后续对该范围数据的写和读操作都将被阻塞，另外一个共享锁不会阻塞读取，而是阻塞写入，但是这往往会带来一些问题，比如电商场景下更新库存时候，我们为了保障数据的一致性更新往往需要先将该商品数据锁住，如果此时两个线程并发更新库存，就可能会导致数据更新出现异常，如图所示：

所以我们在业务上往往会使用select ... for update对数据进行加锁。另外还有些咱们比较不常用的加锁方式，比如

• 全局锁：Flush tables with read lock，主要在进行逻辑备份的时候会用到
• 表锁：lock tables … read/write

隐式锁

隐式锁是我们需要特别关注的，因为很多的”坑“就是因为隐式锁的存在导致的，无形往往最为致命。

表级锁除了表锁以外，还有元数据锁，

• 在进行增删改查的时候会加MDL读锁；
• 在对表结构进行变更的时候，会加MDL写锁；

这个会带来的问题就是当我们想给表添加索引或者修改表结构的时候，由于加了MDL写锁，会阻塞我们线上正常的读写请求，这个时候可能会触发上游的失败重试机制，那很可能就会出现请求雪崩导致DB被打挂。

另外的就是与我们日常业务息息相关行锁以及间隙锁，当我们在进行增删改的时候，会根据当前的隔离级别加上行锁或者间隙锁，那么这时候需要注意的是是否会影响正常业务的读写性能，另外带来的风险就是可能出现加锁范围过大阻塞请求，并触发上游重试，导致服务雪崩，DB打挂。

会不会加锁呢？

谈到这里有的同学可能有疑问，你这增删改都加锁了，那我读的时候岂不是性能很差，特别是在读多写多的业务场景下，我的读请求一上来的话，DB不是分分钟被我查挂了？其实这里innodb引擎用到了一个mvcc的技术即多版本并发控制，其原理就是在数据更新的同时在undolog中记录更新的事务id以及相应的数据，并且维护一个readview的活跃事务id，这样当一个事务执行的时候，很容易能知道自己能看见什么数据，不能看见什么数据，这时候读取数据自然也就不会受到锁的影响能够正常的读取啦。

怎么加

这里讨论怎么加其实就是了解加锁的类型以及范围，即用了什么锁且加在哪里了？在讨论这个问题之前我们先来看看事务隔离级别：

• 读未提交
• 读已提交
• 可重复读
• 串行化

为啥要说这个呢？因为隔离级别也影响着咱们的加锁，读已提交解决了脏读的问题，但是未解决幻读问题；可重复读通过引入间隙锁解决了幻读问题，因此意味着不同的隔离级别用到的锁还不一样，但是有一点明确的是，越高隔离级别锁的使用更加严格。可重复读是默认的事务隔离级别，但是线上设置的隔离级别往往都是读已提交，主要是因为这个级别够用并且能够有更好的并发性能。接下来我们讨论的范围也主要是在读已提交（RC）和可重复读（RR）。

这里根据《mysql45讲》总结的规则来具体分析：

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

另外有两点需要注意的是：

• 锁是加在索引上的
• gap锁是共享的而非独占的

RC

接下来是分别进行讨论，可能有些冗长，需要你耐心看完。

首先是RC级别，这个级别下的加锁规则是比较简单的，因为只涉及到行锁，首先我们先设计一张表

CREATE TABLE `t_db_lock` ( `id` int(11) NOT NULL, `a` int(11) DEFAULT NULL, `b` int(11) DEFAULT NULL, PRIMARY KEY (`id`), KEY `a` (`a`)) ENGINE=InnoDB;insert into t_db_lock values(0,0,0),(5,5,5),(10,10,10); 主键等值存在

sessionA	sessionB	sessionC
begin;update t_db_lock set id=id+1 where id = 0;
	insert into t_db_lock values(1,1,1) [block]
		update t_db_lock set id=id+1 where id = 5;[success]

• 可以看到此时sessionA在做主键上的数据更新，将当前的记录的主键值更新为1，此时db会在id=1和0上加上行锁，即此时针对该id的更新会被阻塞；
• 因此当sessionB想插入id=1的记录时会被阻塞住；
• 但是由于sessionC更新的是id=5的记录，因此可以执行成功

非唯一等值

sessionA	sessionB	sessionC
begin;update t_db_lock set b=b+1 where a = 0;
	update t_db_lock set b=b+1 where id = 0; [block]
		update t_db_lock set b=b+1 where b = 0;[block]

• sessionA根据普通索引的判断条件更新数据，由于行锁是加在索引上，因此这时候a列相关索引数据上了锁；
• 但是为啥这时候我更新id=0的数据也被阻塞了呢？因为这时除了加a上的索引，还有回表更新的操作，此时访问到的主键上的索引也会被加锁，因为是同一行，所以此时更新同样被阻塞住；
• 同样的道理，当我们去更新的b=0的数据对应的主键索引上也是同一条数据，所以此时更新也被阻塞，但是如果我们此时是更新b=5的这条数据的话就能更新成功;

主键等值不存在

sessionA	sessionB	sessionC
begin;select * from t_db_lock where id = 2 for update;
	update t_db_lock set b=b+1 where a = 0; [success]
		update t_db_lock set b=b+1 where b = 0;[success]

• sessionA加了一个id为2的锁，此时这行记录不存在，此时行锁没有加成功，因此不会阻塞其他session的请求
• sessionB执行成功
• sessionC执行成功

无索引等值不存在

sessionA	sessionB	sessionC
begin;select * from t_db_lock where b=3 for update;
	update t_db_lock set b=b+1 where a = 0; [success]
		update t_db_lock set b=b+1 where b = 5;[success]

• 这种情况和主键等值不存在一致，由于未找到对应的加锁记录，则后续的更新操作都能够执行成功。

主键范围

sessionA	sessionB	sessionC
begin;select * from t_db_lock where id >= 0 and id <= 5 for update;
	update t_db_lock set b=b+1 where a = 0; [block]
		insert into t_db_lock values(1,1,1) [success]

• sessionA根据范围加锁，锁了id=0和5这两行数据；
• sessionB由于更新id=0这行已经上锁的数据，所以被阻塞住；
• sessionC由于之前id=1这行记录并不存在，所以可以正常插入，这个场景是不是有点熟悉，就是咱们所说的幻读，如果这时候在sessionA中再执行select * from t_db_lock where id >= 0 and id <= 5就会发现多了一条数据；

RR

这里可重复读级别下主要是讨论间隙锁的加锁场景，这种加锁情况会比读已提交的隔离级别复杂的多； set session transaction isolation level repeatable read;

主键等值存在

sessionA	sessionB	sessionC
begin;select * from t_db_lock where id = 5 for update;
	insert into t_db_lock values(2,2,2); [success]
		insert into t_db_lock values(6,6,6); [success]

• sessionA在已经存在的id=5这行加锁，根据加锁规则，唯一索引会退化为行锁，因此仅在id=5这行加锁；其实这也好理解，既然已经是唯一索引了，那么就不会会出现幻读的情况，因此幻读仅仅取决于这行是否存在，因此我只要给该行加锁保证不再写入即可；
• sessionB和sessionC均不在锁范围内则插入成功；

非唯一等值

sessionA	sessionB	sessionC
begin;select * from t_db_lock where a = 5 for update;
	insert into t_db_lock values(6,11,6); [block]
		insert into t_db_lock values(11,10,6); [success]

• sessionA在已经存在的a=5这行记录上加锁，由于是非唯一索引，根据加锁规则，首先扫描a索引加上next-key lock (0,5] ,接着向右遍历到第一个不满足条件的(根据规则五，唯一索引上的范围查询会访问到不满足条件的第一个值为止)，并退化为间隙锁，因此加锁范围为（5,10），总体加锁范围为（0,10）；并且for update，因此也会对应在主键的索引范围内加上锁，即（0，10）；
• sessionB在主键索引的锁范围内，因此被阻塞；
• sessionC此时不在普通索引和主键索引的范围上，因此执行成功；

  这里可以看到，对于非唯一等值查询的情况下，加锁的范围要比主键等值存在更大，因此我们在对非唯一索引加锁的时候需要注意这个范围。

主键等值不存在

sessionA	sessionB	sessionC
begin;select * from t_db_lock where id = 3 for update;
	insert into t_db_lock values(2,2,2); [block]
		insert into t_db_lock values(6,6,6); [success]

• sessionA此时对id=3的记录加上了行锁，但是由于此时3这行的记录不存在，会对此范围加锁，按照加锁原则，向右遍历且最后一个值不满足等值条件，next-key lock退化为间隙锁，此时加锁范围为（0,5）；
• sessionB属于加锁范围内，因此被阻塞；
• sessionC不在此加锁范围内，加锁成功；

为啥这里要加的是范围锁呢，其实主要解决的是幻读问题，假设这里如果没有在此范围内加锁，那么T1时刻sessionB执行成功，T2时刻再次执行select * from t_db_lock where id = 3的话，就会发现原先查询不到的结果现在竟然可以查询到了，就像出现幻觉一样；因为为了避免出现这种幻读的情况，需要在此范围内加锁。

非唯一等值不存在

sessionA	sessionB	sessionC
begin;select * from t_db_lock where a = 3 for update;
	insert into t_db_lock values(3,5,5); [block]
		insert into t_db_lock values(6,5,5); [success]

• sessionA在a=3这行上加锁的，由于db中不存在该行，所以同样会加next-key lock,并且因为锁都是加在索引上的，因此会在a索引上加上（0,5]的范围锁。但是这里有个奇怪的现象，当a=5时，如果id<5会阻塞，如果id>5则会成功，从结果看来，此时a上的锁似乎是有偏向性的，并不是严格意义上的a=5时就会锁住相应的插入记录

主键范围

sessionA	sessionB	sessionC
select * from t_db_lock where id >= 5 and id < 6 for update;
	insert into t_db_lock values(3,3,3); [success]
		insert into t_db_lock values(10,10,10); [block]

• sessionA进行范围查询加锁，在语义上等价于select * from t_db_lock where id = 5 for update，但是实际加锁情况还是有很大的区别，首先id >= 5根据等值查询查询到id=5这行加锁为（0,5],由于是唯一索引，退化为行锁，因此在id=5这行上加了锁，接着向右查询，找到第一个不满足条件的值，即id=10这行，所以加next-key lock(5,10],这里因为并不是等值查询，不会有退化为间隙锁的过程，所以整体加锁范围[5,10]
• sessionB不在锁范围内，插入成功
• sessionC在所谓中，插入失败，注意这里是被阻塞住，而不是报主键冲突

非唯一范围

sessionA	sessionB	sessionC
begin;select * from t_db_lock where a >= 5 and a < 6 for update;
	insert into t_db_lock values(3,3,3); [block]
		insert into t_db_lock values(10,10,10); [block]

• sessionA加锁范围区别于主键索引主要是在(0, 5]这个范围下并未退化为行锁，因此总体加锁范围为(0, 10]

无索引等值不存在

sessionA	sessionB	sessionC
begin;select * from t_db_lock where b = 6 for update;
	insert into t_db_lock values(3,3,3); [block]
		insert into t_db_lock values(10,10,10); [block]

• sessionA中加锁记录为b=6这行，由于b未创建索引，因此会将所有b索引上的记录都加锁，由于是for update加锁，认为还回去主表上更新，因此主表的相关记录也都被上了锁，这就会导致加锁期间处于锁表的状态，任何的更新操作都没办法成功，这在线上会是非常危险的操作，可能会导致db被打垮。

什么时候该加什么时候不该加

通过上述的分析我们应该对锁的类型以及语句中加锁的范围有一个大致的了解，可以知道悲观锁是需要我们谨慎使用的，因为很可能简单的sql就会拖垮db的性能，影响线上服务的质量，那么什么时候该加什么时候不该加呢？

我认为对于db的并发场景，我们可以这么去考虑：

1. 尽可能优先考虑使用乐观锁的方式解决；
2. 如果需要用到悲观锁，则务必在加锁的键上加索引；
3. 确认db的隔离级别，分析sql中可能存在导致冲突或者死锁的原因，避免sql被长时间阻塞；