您当前的位置:首页 > 电脑百科 > 数据库 > MYSQL

mysql到底是怎么加锁的?

时间:2023-06-26 15:50:15  来源:  作者:腾讯技术工程

作者: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被长时间阻塞;


Tags:mysql   点击:()  评论:()
声明:本站部分内容及图片来自互联网,转载是出于传递更多信息之目的,内容观点仅代表作者本人,不构成投资建议。投资者据此操作,风险自担。如有任何标注错误或版权侵犯请与我们联系,我们将及时更正、删除。
▌相关推荐
MySQL 核心模块揭秘
server 层会创建一个 SAVEPOINT 对象,用于存放 savepoint 信息。binlog 会把 binlog offset 写入 server 层为它分配的一块 8 字节的内存里。 InnoDB 会维护自己的 savepoint...【详细内容】
2024-04-03  Search: mysql  点击:(6)  评论:(0)  加入收藏
MySQL 核心模块揭秘,你看明白了吗?
为了提升分配 undo 段的效率,事务提交过程中,InnoDB 会缓存一些 undo 段。只要同时满足两个条件,insert undo 段或 update undo 段就能被缓存。1. 关于缓存 undo 段为了提升分...【详细内容】
2024-03-27  Search: mysql  点击:(11)  评论:(0)  加入收藏
MySQL:BUG导致DDL语句无谓的索引重建
对于5.7.23之前的版本在评估类似DDL操作的时候需要谨慎,可能评估为瞬间操作,但是实际上线的时候跑了很久,这个就容易导致超过维护窗口,甚至更大的故障。一、问题模拟使用5.7.22...【详细内容】
2024-03-26  Search: mysql  点击:(10)  评论:(0)  加入收藏
从 MySQL 到 ByteHouse,抖音精准推荐存储架构重构解读
ByteHouse是一款OLAP引擎,具备查询效率高的特点,在硬件需求上相对较低,且具有良好的水平扩展性,如果数据量进一步增长,可以通过增加服务器数量来提升处理能力。本文将从兴趣圈层...【详细内容】
2024-03-22  Search: mysql  点击:(24)  评论:(0)  加入收藏
MySQL自增主键一定是连续的吗?
测试环境:MySQL版本:8.0数据库表:T (主键id,唯一索引c,普通字段d)如果你的业务设计依赖于自增主键的连续性,这个设计假设自增主键是连续的。但实际上,这样的假设是错的,因为自增主键不...【详细内容】
2024-03-10  Search: mysql  点击:(6)  评论:(0)  加入收藏
准线上事故之MySQL优化器索引选错
1 背景最近组里来了许多新的小伙伴,大家在一起聊聊技术,有小兄弟提到了MySQL的优化器的内部策略,想起了之前在公司出现的一个线上问题,今天借着这个机会,在这里分享下过程和结论...【详细内容】
2024-03-07  Search: mysql  点击:(28)  评论:(0)  加入收藏
MySQL数据恢复,你会吗?
今天分享一下binlog2sql,它是一款比较常用的数据恢复工具,可以通过它从MySQL binlog解析出你要的SQL,并根据不同选项,可以得到原始SQL、回滚SQL、去除主键的INSERT SQL等。主要...【详细内容】
2024-02-22  Search: mysql  点击:(45)  评论:(0)  加入收藏
如何在MySQL中实现数据的版本管理和回滚操作?
实现数据的版本管理和回滚操作在MySQL中可以通过以下几种方式实现,包括使用事务、备份恢复、日志和版本控制工具等。下面将详细介绍这些方法。1.使用事务:MySQL支持事务操作,可...【详细内容】
2024-02-20  Search: mysql  点击:(53)  评论:(0)  加入收藏
为什么高性能场景选用Postgres SQL 而不是 MySQL
一、 数据库简介 TLDR;1.1 MySQL MySQL声称自己是最流行的开源数据库,它属于最流行的RDBMS (Relational Database Management System,关系数据库管理系统)应用软件之一。LAMP...【详细内容】
2024-02-19  Search: mysql  点击:(38)  评论:(0)  加入收藏
MySQL数据库如何生成分组排序的序号
经常进行数据分析的小伙伴经常会需要生成序号或进行数据分组排序并生成序号。在MySQL8.0中可以使用窗口函数来实现,可以参考历史文章有了这些函数,统计分析事半功倍进行了解。...【详细内容】
2024-01-30  Search: mysql  点击:(54)  评论:(0)  加入收藏
▌简易百科推荐
MySQL 核心模块揭秘
server 层会创建一个 SAVEPOINT 对象,用于存放 savepoint 信息。binlog 会把 binlog offset 写入 server 层为它分配的一块 8 字节的内存里。 InnoDB 会维护自己的 savepoint...【详细内容】
2024-04-03  爱可生开源社区    Tags:MySQL   点击:(6)  评论:(0)  加入收藏
MySQL 核心模块揭秘,你看明白了吗?
为了提升分配 undo 段的效率,事务提交过程中,InnoDB 会缓存一些 undo 段。只要同时满足两个条件,insert undo 段或 update undo 段就能被缓存。1. 关于缓存 undo 段为了提升分...【详细内容】
2024-03-27  爱可生开源社区  微信公众号  Tags:MySQL   点击:(11)  评论:(0)  加入收藏
MySQL:BUG导致DDL语句无谓的索引重建
对于5.7.23之前的版本在评估类似DDL操作的时候需要谨慎,可能评估为瞬间操作,但是实际上线的时候跑了很久,这个就容易导致超过维护窗口,甚至更大的故障。一、问题模拟使用5.7.22...【详细内容】
2024-03-26  MySQL学习  微信公众号  Tags:MySQL   点击:(10)  评论:(0)  加入收藏
从 MySQL 到 ByteHouse,抖音精准推荐存储架构重构解读
ByteHouse是一款OLAP引擎,具备查询效率高的特点,在硬件需求上相对较低,且具有良好的水平扩展性,如果数据量进一步增长,可以通过增加服务器数量来提升处理能力。本文将从兴趣圈层...【详细内容】
2024-03-22  字节跳动技术团队    Tags:ByteHouse   点击:(24)  评论:(0)  加入收藏
MySQL自增主键一定是连续的吗?
测试环境:MySQL版本:8.0数据库表:T (主键id,唯一索引c,普通字段d)如果你的业务设计依赖于自增主键的连续性,这个设计假设自增主键是连续的。但实际上,这样的假设是错的,因为自增主键不...【详细内容】
2024-03-10    dbaplus社群  Tags:MySQL   点击:(6)  评论:(0)  加入收藏
准线上事故之MySQL优化器索引选错
1 背景最近组里来了许多新的小伙伴,大家在一起聊聊技术,有小兄弟提到了MySQL的优化器的内部策略,想起了之前在公司出现的一个线上问题,今天借着这个机会,在这里分享下过程和结论...【详细内容】
2024-03-07  转转技术  微信公众号  Tags:MySQL   点击:(28)  评论:(0)  加入收藏
MySQL数据恢复,你会吗?
今天分享一下binlog2sql,它是一款比较常用的数据恢复工具,可以通过它从MySQL binlog解析出你要的SQL,并根据不同选项,可以得到原始SQL、回滚SQL、去除主键的INSERT SQL等。主要...【详细内容】
2024-02-22  数据库干货铺  微信公众号  Tags:MySQL   点击:(45)  评论:(0)  加入收藏
如何在MySQL中实现数据的版本管理和回滚操作?
实现数据的版本管理和回滚操作在MySQL中可以通过以下几种方式实现,包括使用事务、备份恢复、日志和版本控制工具等。下面将详细介绍这些方法。1.使用事务:MySQL支持事务操作,可...【详细内容】
2024-02-20  编程技术汇    Tags:MySQL   点击:(53)  评论:(0)  加入收藏
MySQL数据库如何生成分组排序的序号
经常进行数据分析的小伙伴经常会需要生成序号或进行数据分组排序并生成序号。在MySQL8.0中可以使用窗口函数来实现,可以参考历史文章有了这些函数,统计分析事半功倍进行了解。...【详细内容】
2024-01-30  数据库干货铺  微信公众号  Tags:MySQL   点击:(54)  评论:(0)  加入收藏
mysql索引失效的场景
MySQL中索引失效是指数据库查询时无法有效利用索引,这可能导致查询性能显著下降。以下是一些常见的MySQL索引失效的场景:1.使用非前导列进行查询: 假设有一个复合索引 (A, B)。...【详细内容】
2024-01-15  小王爱编程  今日头条  Tags:mysql索引   点击:(85)  评论:(0)  加入收藏
站内最新
站内热门
站内头条