面试官:说一下MySQL中的锁机制吧

5. 1MySQL有哪些锁?

为保证数据的一致性,需要对并发操作进行控制,因此产生了。同时锁机制也为实现MySQL的各个隔离级别提供了保证。 锁冲突 也是影响数据库并发访问性能的一个重要因素。所以锁对数据库而言显得尤其重要,也更加复杂。

按照数据操作的类型,可以分为读锁、写锁。

  • 读锁 :也称为 共享锁 、英文用 S 表示。针对同一份数据,多个事务的读操作可以同时进行而不会互相影响,相互不阻塞的。

  • 写锁 :也称为 排他锁 、英文用 X 表示。当前写操作没有完成前,它会阻断其他写锁和读锁。这样就能确保在给定的时间里,只有一个事务能执行写入,并防止其他用户读取正在写入的同一资源。

在 MySQL 里,根据加锁的范围,可以分为全局锁、表级锁和行锁三类。

全局锁

使用全局锁,整个数据库就处于只读状态了。

  • 应用场景:主要应用于做全库逻辑备份,这样在备份数据库期间,不会因为数据或表结构的更新,而出现备份文件的数据与预期的不一样。

  • 缺点:业务只能读数据,而不能更新数据,这样会造成业务停滞。

  • 避免方法:使用可重复读的隔离级别,在备份数据库之前先开启事务,整个数据库的数据就都是可重复读的,而且由于 MVCC 的支持,备份期间业务依然可以对数据进行更新操作。

    # 加全局锁 FTWRL
    flush tables with read lock 
    

表级锁

MySQL 里面表级别的锁有:表锁、元数据锁(MDL)、意向锁、AUTO-INC 锁。

  • 表锁

    表锁也有表级别的共享锁,表级别的独占锁。

    表锁除了会限制别的线程的读写外,也会限制本线程接下来的读写操作。表锁的颗粒度太大,会影响并发性能,应该尽量避免使用表锁。

  • 元数据锁(Meta Datebase Lock,MDL

    对一张表进行 CRUD 操作时,加的是 MDL 读锁

    • 只允许读,不能做结构的修改,比如修改表字段等。

    对一张表做结构变更操作的时候,加的是 MDL 写锁

    • 只允许写,修改表结构时不能通过CRUD读取数据。

    MDL 不需要显示调用,在事务提交后才会释放,这意味着事务执行期间,MDL 是一直持有的

    申请 MDL 锁的操作会形成一个队列,队列中写锁获取优先级高于读锁,一旦出现 MDL 写锁等待(线程申请不到 MDL 写锁),会阻塞后续该表的所有 CRUD 操作(MDL读锁)。

    • 所以为了能安全的对表结构进行变更,在对表结构变更前,先要看看数据库中的长事务,是否有事务已经对表加上了 MDL 读锁,如果可以考虑 kill 掉这个长事务,然后再做表结构的变更。
  • 意向锁

    意向锁的目的是快速判断表里是否有记录被加锁

    意向共享锁(intention shared lock, IS):事务有意向对表中的某些行加共享锁(S锁)

    意向排他锁(intention exclusive lock, IX):事务有意向对表中的某些行加排他锁(X锁)

    普通的SELECT语句利用MVCC实现一致性读,是无锁的,但是可以使用以下方式加锁:

    //先在表上加上意向共享锁,然后对读取的记录加共享锁
    select ... lock in share mode;
    
    //先表上加上意向独占锁,然后对读取的记录加独占锁
    select ... for update;
    

    意向锁之间不冲突,也不会和行级的共享锁和独占锁发生冲突,只会和共享表锁(lock tables … read)或独占表锁(lock tables … write)发生冲突。

  • AUTO-INC 锁(自动增长)

    表里的主键通常都会设置成自增的,这是通过对主键字段声明 AUTO_INCREMENT 属性实现的。

    在插入数据时,会加一个表级别的 AUTO-INC 锁,然后为被 AUTO_INCREMENT 修饰的字段赋值递增的值,等插入语句执行完成后,才会把 AUTO-INC 锁释放掉。

    • InnoDB 存储引擎提供了一种轻量级的锁来实现自增。只是在赋值完成后,就把该锁释放。

行级锁

对于表锁和行锁,满足读读共享、读写互斥、写写互斥的。

不同隔离级别下,行级锁的种类不同。

  • 在读已提交隔离级别下,行级锁的种类只有记录锁,也就是仅仅把一条记录锁上。

  • 在可重复读隔离级别下,行级锁的种类除了有记录锁,还有间隙锁(目的是为了避免幻读)。

行级锁的类型主要有三类:

  • 记录锁(Record Lock),也就是仅仅把一条记录锁上;记录锁是有 S 锁和 X 锁之分的。

    • 共享锁(S锁)指的就是对于多个不同的事务,对同一个资源共享同一个锁。 相当于对于同一把门,它拥有多个钥匙一样。
    • 独占锁(X锁)也叫排他锁,是指该锁一次只能被一个线程所持有。

    共享锁(S锁)满足读读共享,读写互斥;独占锁(X锁)满足写写互斥、读写互斥。

  • 间隙锁(Gap Lock),锁定一个范围,但是不包含记录本身;只存在于可重复读隔离级别,目的是为了解决可重复读隔离级别下幻读的现象。

    • 间隙锁之间是兼容的,即两个事务可以同时持有包含共同间隙范围的间隙锁,并不存在互斥关系,因为间隙锁仅仅是为了防止插入幻影记录而提出的。
  • 临键锁(Next-Key Lock),Record Lock + Gap Lock 的组合,锁定一个范围,并且锁定记录本身。next-key lock 即能保护记录,又能阻止其他事务将新纪录插入到被保护记录前面的间隙中。

    • next-key lock 是包含间隙锁+记录锁的,如果一个事务获取了 X 型的 next-key lock,那么另外一个事务在获取相同范围的 X 型的 next-key lock 时,是会被阻塞的
  • 插入意向锁:插入意向锁名字虽然有意向锁,但是它并不是意向锁,它是一种特殊的间隙锁,属于行级别锁。存在间隙锁时,执行Insert语句时会用到。

    • 插入意向锁是一种特殊的间隙锁,但不同于间隙锁的是,该锁只用于并发插入操作。如果说间隙锁锁住的是一个区间,那么「插入意向锁」锁住的就是一个点。因而从这个角度来说,插入意向锁确实是一种特殊的间隙锁。

5.2 MySQL是怎么加锁的?

  1. 从语句角度来看

普通的SELECT默认是不加锁的,属于快照读,是使用MVCC的方式实现的。但是可以在查询时对记录加行级锁,查询会加锁的语句称为锁定读。锁定读的语句必须在事务中,因为当事务提交了,锁就会被释放。而update 和 delete 操作都会加行级锁,且锁的类型都是独占锁(X型锁)。

//对读取的记录加共享锁(S型锁)
select ... lock in share mode;

//对读取的记录加独占锁(X型锁)
select ... for update;

Insert 语句在正常执行时是不会生成锁结构的,它是靠聚簇索引记录自带的 trx_id 隐藏列来作为隐式锁来保护记录的。但此时记录之间加有间隙锁,隐式锁会转换为显示锁。

如果已加间隙锁,此时会生成一个插入意向锁,然后锁的状态设置为等待状态,现象就是Insert语句被阻塞。

  • 因为插入意向锁与间隙锁是冲突的,所以当其它事务持有该间隙的间隙锁时,需要等待其它事务释放间隙锁之后,才能获取到插入意向锁。

如果记录之间加有间隙锁,为了避免幻读,此时是不能插入记录的,因为插入意向锁会被设置为等待状态

如果 Insert 的记录和已有记录存在唯一键冲突,此时也不能插入记录,会对这条记录加上S型的锁

  • 至于是记录锁,还是 next-key 锁,跟是「主键冲突」还是「唯一二级索引冲突」有关系。
    • 如果主键冲突:给已存在的主键索引记录添加S型记录锁
    • 如果唯一二级索引冲突:给已存在的二级索引记录添加S型next-key锁

  1. 从MySQL角度来看

MySQL加锁的对象是索引,加锁的基本单位是 next-key lock

  • next-key lock 是前开后闭区间,而间隙锁是前开后开区间。在能使用记录锁或者间隙锁就能避免幻读现象的场景下, next-key lock 就会退化成退化成记录锁或间隙锁。

唯一索引等值查询,加锁情况分析:(主键索引为例)

  • 当查询的记录是存在的,在索引树上定位到这一条记录后,该记录的索引中的 next-key lock 会退化成「记录锁」
    • 等值查询唯一索引,只需要加锁一条记录,并且加记录锁就可以避免幻读。
  • 当查询的记录是不存在的,在索引树找到第一条大于该查询记录的记录后,将该记录的索引中的 next-key lock 会退化成「间隙锁」,因为仅靠间隙锁就可以避免幻读。

唯一索引范围查询,加锁情况分析:

  • 首先会对每一个扫描到的索引加 next-key 锁,如果遇到下面这些情况,会退化成记录锁或者间隙锁:

    • 大于的范围查询,next-key锁不会退化。

      select * from user where id > 15 for update;
      
      img
    • 大于等于的范围查询,如果“等于”的等值查询的记录是存在于表中,那么该记录的索引中的 next-key 锁会退化成记录锁

      select * from user where id >= 15 for update;
      
      img
    • 小于/小于等于的范围查询,扫描到终止范围查询的记录时,next-key锁就会退化为间隙锁。小于等于时,等值查询的记录在表中,next-key不会退化,因为next-key本就是左开右闭,避免幻读。

      select * from user where id < 6 for update;
      
      img
  • 非唯一索引等值查询

    • 当查询的记录存在时,扫描到的二级索引记录加的是next-key lock,扫描到的第一个不符合条件的二级索引记录,next-key 锁会退化成间隙锁。同时,在符合查询条件的记录的主键索引上加记录锁。

      select * from user where age = 22 for update;
      
      img
    • 当查询的记录不存在时,扫描到第一条不符合条件的二级索引记录, next-key 锁会退化成间隙锁。因为不存在满足查询条件的记录,所以不会对主键索引加锁。

      select * from user where age = 25 for update;
      
      img
  • 非唯一索引范围查询

    • 对扫描到的二级索引记录加锁都是加 next-key 锁,主键索引加记录锁。
  • 没有加索引的查询

    • 在线上在执行 update、delete、select … for update 等具有加锁性质的语句,一定要检查语句是否走了索引,如果是全表扫描的话,会对每一个索引加 next-key 锁,相当于把整个表锁住了

5.3 update没加索引会锁全表?

当我们执行 update 语句时,实际上是会对记录加独占锁(X 锁)的,此时其他事务对持有独占锁的记录进行修改时是会被阻塞的。另外,这个锁并不是执行完 update 语句就会释放的,而是会等事务结束时才会释放。

  • 在 update 语句的 where 条件没有使用索引,就会全表扫描,于是就会对所有记录加上 next-key 锁(记录锁 + 间隙锁),相当于把整个表锁住了
  • 即使where条件使用了索引,还得看这条语句在执行过程中,优化器最终选择的是索引扫描,还是全表扫描,如果走了全表扫描,就会对全表的记录加锁了。

5.4 MySQL死锁了,怎么办?

RR隔离级别下,会存在幻读的问题,InnoDB为了解决可重复读隔离级别下的幻读问题,就引出了next-key 锁,是记录锁和间隙锁的组合。

我们可以执行 select * from performance_schema.data_locks\G; 语句 ,确定事务加了什么类型的锁。

  • 为什么会出现死锁?

    1. 建了一张订单表,其中 id 字段为主键索引,order_no 字段普通索引,也就是非唯一索引(二级索引)

      # 插入六条记录,id 1-6 、order_on 1001-1006
      
    2. 事务A要插入1007订单记录:在插入之前,给订单做幂等性校验,目的是为了保证不会出现重复的订单。

      SELECT id FROM t_order WHERE `order_no` = 1007 for UPDATE;
      # 需要对订单做幂等性校验,所以两个事务先要查询该订单是否存在,不存在才插入记录
      

      执行该语句,事务A在二级索引加了X型next-key锁,范围是(1006 , +∞)。

    3. 事务B也做幂等性校验:

      SELECT id FROM t_order WHERE `order_no` = 1008 for UPDATE;
      

      事务B在二级索引加了X型next-key锁,范围也是(1006 , +∞)。

    4. 事务A、B执行insert语句,插入1007、1008。

      Insert into t_order (order_no, create_date) values (1007, now());
      Insert into t_order (order_no, create_date) values (1008, now());
      

      此时两个事务都陷入了等待状态,也就是发生了死锁,因为都在相互等待对方释放锁。

      • 因为当我们执行insert语句时,会在插入间隙上获取插入意向锁,而插入意向锁与间隙锁是冲突的,所以当其它事务持有间隙锁时,需要等待其它事务释放间隙锁之后,才能获取到插入意向锁。

        而间隙锁与间隙锁之间是兼容的,并且两个事务中 select ... for update 语句并不会相互影响。

        • 因为间隙锁的意义只在于阻止区间被插入一个事务获取的间隙锁不会阻止另一个事务获取同一个间隙范围的间隙锁,共享和排他的间隙锁是没有区别的,他们相互不冲突,且功能相同,即两个事务可以同时持有包含共同间隙的间隙锁。
        • next-key lock 是包含间隙锁+记录锁的,如果一个事务获取了 X 型的 next-key lock,那么另外一个事务再获取相同范围的 X 型的 next-key lock 时,是会被阻塞的。但是,对于这种范围为 (1006, +∞] 的 next-key lock,两个事务是可以同时持有的,不会冲突。因为 +∞ 并不是一个真实的记录,自然就不需要考虑 X 型与 S 型关系。
  • 如何避免死锁?

    死锁的四个必要条件:互斥、占有且等待、不可强占用、循环等待。只要系统发生死锁,这些条件必然成立,但是只要破坏任意一个条件就死锁就不会成立。

    在数据库层面,有两种策略通过「打破循环等待条件」来解除死锁状态:

    • 设置事务等待锁的超时时间。当一个事务的等待时间超过该值后,就对这个事务进行回滚,于是锁就释放了,另一个事务就可以继续执行了。在 InnoDB 中,参数 innodb_lock_wait_timeout 是用来设置超时时间的,默认值时 50 秒。

    • 开启主动死锁检测。主动死锁检测在发现死锁后,主动回滚死锁链条中的某一个事务,让其他事务得以继续执行。将参数 innodb_deadlock_detect 设置为 on,表示开启这个逻辑,默认就开启。

5.5 字节面试: 加了什么锁,导致死锁的?

img

创建一张学生表:其中id为主键索引,其他都是普通字段。

事务 A 和 事务 B 都在执行 insert 语句后,都陷入了等待状态,也就是发生了死锁,因为都在相互等待对方释放锁。

img
  • Time1阶段:此时事务 A 在主键索引(INDEX_NAME : PRIMARY)上加的是间隙锁,锁范围是(20, 30)。(唯一索引等值查询,查询id不在索引中,退化成间隙锁)
  • Time2阶段:此时事务B在主键索引上加的是也是间隙锁,和事务A相同。
  • Time3阶段:事务 A 的状态为等待状态(LOCK_STATUS: WAITING),因为向事务 B 生成的间隙锁(范围 (20, 30))中插入了一条记录,所以事务 A 的插入操作生成了一个插入意向锁(LOCK_MODE:INSERT_INTENTION)。
  • Time4阶段:与Time3阶段相同。

本次案例中,事务 A 和事务 B 在执行完后 update 语句后都持有范围为(20, 30)的间隙锁,而接下来的插入操作为了获取到插入意向锁,都在等待对方事务的间隙锁释放,于是就造成了循环等待,满足了死锁的四个条件:互斥、占有且等待、不可强占用、循环等待,因此发生了死锁。

整理自:小林Coding

本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如若转载,请注明出处:/a/2036.html

如若内容造成侵权/违法违规/事实不符,请联系我们进行投诉反馈qq邮箱809451989@qq.com,一经查实,立即删除!

相关文章

seata服务搭建

它支持两种存储模式&#xff0c;一个是文件&#xff0c;一个是数据库&#xff0c;下面我们分别介绍一下这两种配置nacos存储配置&#xff0c;注意如果registry.conf中注册和配置使用的是file&#xff0c;就会去读取file.config的配置&#xff0c;如果是nacos则通过nacos动态读取…

Kafka和RabbitMQ有哪些区别,各自适合什么场景?

目录标题1. 消息的顺序2. 消息的匹配3. 消息的超时4. 消息的保持5. 消息的错误处理6. 消息的吞吐量总结1. 消息的顺序 有这样一个需求&#xff1a;当订单状态变化的时候&#xff0c;把订单状态变化的消息发送给所有关心订单变化的系统。 订单会有创建成功、待付款、已支付、已…

Cookie和Session详解

目录 前言&#xff1a; Session详解 Cookie和Session区别和关联 服务器组织会话的方式 使用Tomcat实现登录成功跳转到欢迎页面 登录前端页面 登录成功后端服务器 重定向到欢迎页面 抓包分析交互过程 小结&#xff1a; 前言&#xff1a; Cookie之前博客有介绍过&#x…

音视频技术开发周刊 | 285

每周一期&#xff0c;纵览音视频技术领域的干货。新闻投稿&#xff1a;contributelivevideostack.com。GPT-4 Office全家桶发布谷歌前脚刚宣布AI工具整合进Workspace&#xff0c;微软后脚就急匆匆召开了发布会&#xff0c;人狠话不多地祭出了办公软件王炸——Microsoft 365 Cop…

使用GPT-4生成QT代码

一、概述最近ChatGPT火爆起来了&#xff0c;ChatGPT是一种基于GPT的自然语言处理模型&#xff0c;可以用于生成自然语言文本&#xff0c;例如对话、文章等。最近又发现了一个优秀且免费的代码生成工具Cursor.so &#xff0c;Cursor.so集成了 GPT-4 &#xff0c;可以帮助你快速编…

Python3,5行代码,生成自动排序动图,这操作不比Excel香?

5行代码生成自动排序动图1、引言2、代码实战2.1 pynimate介绍2.2 pynimate安装2.3 代码示例3、总结1、引言 小屌丝&#xff1a;鱼哥&#xff0c;听说你的excel段位又提升了&#xff1f; 小鱼&#xff1a;你这是疑问的语气&#xff1f; 小屌丝&#xff1a;没有~ 吧… 小鱼&…

计算机网络复习重点

文章目录计算机网络复习重点第一章 计算机网络和因特网概念与应用1、什么是因特网2、协议protocol3、入网方式4、物理媒介5、数据交换模式6、延时与丢包什么时候发生延时&#xff1f;延时的类型丢包何时发生7、协议层次与模型因特网协议栈TCP / IP模型ISO/OSI参考模型协议数据单…

MySQL-存储过程

什么是存储过程我们前面所学习的MySQL语句都是针对一个表或几个表的单条 SQL 语句&#xff0c;但是在数据库的实际操作中&#xff0c;并非所有操作都那么简单&#xff0c;经常会有一个完整的操作需要多条SQL语句处理多个表才能完成。例如&#xff0c;为了确认学生能否毕业&…

归并排序和快速排序

目录 归并排序 思路&#xff1a; 代码执行&#xff1a; 快速排序 运行流程图&#xff1a; 代码思路&#xff1a; 代码执行&#xff1a; 归并排序 定义&#xff1a;归并排序是建立在归并操作上的一种有效&#xff0c;稳定的排序算法&#xff0c;该算法是采用分治法&#x…

【JavaSE】类和对象(中)

类和对象&#xff08;中&#xff09;4. this引用4.1 为什么要有this引用4.2 什么是this引用4.3 this引用的特性5. 对象的构造及初始化5.1 如何初始化对象5.2 构造方法&#xff08;构造器&#xff09;5.2.1 概念5.2.2 特性5.3 默认初始化5.4 就地初始化6. 封装6.1 封装的概念6.2…

常用hook

Hook 是 React 16.8 的新增特性。它可以让你在不编写 class 的情况下使用 state 以及其他的 React 特性。理解&#xff1a;hook是react提供的函数API官方提供的hook基础hookuseState APIconst [state, setState] useState(initialState); //返回state值 以及更新state的方法 …

壹沓科技完成近2亿元B轮融资:构建数字机器人,实现业务超自动化

RPA中国获悉&#xff0c;全球领先的数字机器人公司壹沓科技近期宣布完成近2亿元B轮融资&#xff0c;本轮融资由鼎晖VGC(创新与成长基金)领投&#xff0c;创享欢聚投资基金、IDG资本、钟鼎资本跟投&#xff0c;指数资本继续担任独家财务顾问。 壹沓科技CEO卞晓瑜表示&#xff1…

栈、队列、优先级队列的模拟实现

优先级队列的模拟实现栈stack的模拟实现push()pop()top()size()empty()swap()stack总代码队列queue的模拟实现push()pop()front()back()empty()size()swap()queue总代码优先级队列(堆)push()pop()top()empty()size()swap()priority_queue总代码deque的了解栈 在CSTL中栈并不属…

吃透Java面试题,建议收藏

本文已经收录到Github仓库&#xff0c;该仓库包含计算机基础、Java基础、多线程、JVM、数据库、Redis、Spring、Mybatis、SpringMVC、SpringBoot、分布式、微服务、设计模式、架构、校招社招分享等核心知识点&#xff0c;欢迎star~ Github地址&#xff1a;https://github.com/…

LeetCode:27. 移除元素

&#x1f34e;道阻且长&#xff0c;行则将至。&#x1f353; &#x1f33b;算法&#xff0c;不如说它是一种思考方式&#x1f340;算法专栏&#xff1a; &#x1f449;&#x1f3fb;123 一、&#x1f331;27. 移除元素 题目描述&#xff1a;给你一个数组 nums 和一个值 val&am…

Chapter6.2:其他根轨迹及综合实例分析

该系列博客主要讲述Matlab软件在自动控制方面的应用&#xff0c;如无自动控制理论基础&#xff0c;请先学习自动控制系列博文&#xff0c;该系列博客不再详细讲解自动控制理论知识。 自动控制理论基础相关链接&#xff1a;https://blog.csdn.net/qq_39032096/category_10287468…

使用vite创建vue3工程

定义 什么是vite&#xff1f;-----新一代前端构建工具 优势 开发环境中&#xff0c;无需打包操作&#xff0c;可快速的冷启动---最牛的地方轻量快速的热重载&#xff08;HMR&#xff09;---一修改代码就局部刷新&#xff0c;webpack也具备&#xff0c;但vite更快真正的按需编…

【数据结构与算法】用队列实现栈

文章目录&#x1f60e;前言如何用队列实现栈&#xff1f;用队列实现栈整体的实现代码&#x1f60e;写在最后&#x1f60e;前言 &#x1f63c;前面我们相继实现了 栈 和 队列 &#xff0c;是不是愁没有练手的地方呢&#xff1f;别担心&#xff0c;本章带大家用队列来实现一个栈&…

synchronized 加锁 this 和 class 的区别

synchronized 是 Java 语言中处理并发问题的一种常用手段&#xff0c;它也被我们亲切的称之为“Java 内置锁”&#xff0c;由此可见其地位之高。然而 synchronized 却有着多种用法&#xff0c;当它修饰不同对象时&#xff0c;其意义也是不同的&#xff0c;下面我们一起来看。 ​…

云原生时代顶流消息中间件Apache Pulsar部署实操之Pulsar IO与Pulsar SQL

文章目录Pulsar IO (Connector连接器)基础定义安装Pulsar和内置连接器连接Pulsar到Cassandra安装cassandra集群配置Cassandra接收器创建Cassandra Sink验证Cassandra Sink结果删除Cassandra Sink连接Pulsar到PostgreSQL安装PostgreSQL集群配置JDBC接收器创建JDBC Sink验证JDBC …