MySQL系列(五)---总结MySQL中的锁

时间:2023-12-14 08:51:02

MySQL中的锁

目录

概述

  • MyISAM支持表锁,InnoDB支持表锁和行锁,默认为行锁

  • 表级锁:开销小,加锁快,不会出现死锁。锁定粒度大,发生锁冲突的概率最高,并发量最低

  • 行级锁:开销大,加锁慢,会出现死锁。锁力度小,发生锁冲突的概率小,并发度最高

MyISAM表级锁

锁模式

  • show status like 'table%' 可以分析锁争夺情况
  • 表级锁分为两种模式:共享读锁,独占写锁
  • 读读共享,读写互斥,写写互斥,写读互斥(比如写读互斥,当一个用户对user表进行写的时候,会阻塞其他用户的写操作和读操作)

加表锁

  • 在select的时候自动加读锁,在update delete insert时,自动加写锁
  • 一般不需要用户用lock table 命令直接显式加锁

显示加表锁时

  • session1显示给user表加锁
lock table user read;
  • session1此时不能读取未锁定的class表,所以lock table时要一次性锁定要用到的表
  • session1此时不能进行更新和插入,只能进行读取
  • 其他session可以读取user表,但是更新会阻塞,而插入不会阻塞(可以通过设置concurrent_insert 系统变量来控制并发插入行为)

锁调度

  • 当一个进程请求user表的读锁,一个进程请求user表的写锁,即使读请求先到,也是请求写锁成先成功
  • 所以,MyISAM不适合大量的更新,因为大量更新会导致查操作很难获得读锁

InnoDB行级锁

行锁

  • 通过 show status like 'InnoDB_row_lock' 来分析行锁争夺情况
  • 共享锁S:允许一个事务去读一行,阻止其他事务获得相同数据集的排他锁
  • 排他锁X:允许获得排它锁的事务更新数据,阻止其他事务取得相同数据集的共享锁和排他锁

意向锁

  • 所谓意向锁就是意图拿到锁,所以要获得共享锁时,需要先获得意向共享锁
  • 意向共享所IS,一个事务给一个数据行加共享锁时,必须先获得表的IS锁
  • 意向排它锁IX,一个事务给一个数据行加排他锁时,必须先获得该表的IX锁

间隙锁

  • 范围查询时,比如select * from user where id>50 ,这个时候即使最大id为51,不仅会给符合条件的行加锁,也会给id大于51(不存在的记录)的加锁,这就是间隙锁
  • 间隙锁解决了事务并发的幻读问题

兼容情况

S X IS IX
S Yes No Yes No
X No No No No
IS Yes No Yes Yes
IX No No Yes Yes

加锁

  • 意向锁是InnoDB自动加的
  • 对于update insert delete 会自动加排它锁
  • 对于select不会自动加锁
  • Select * from user LOCK IN SHARE MODE 手动添加共享锁
  • select * from user For update 手动添加排它锁

行锁使用方式

  • 通过索引条件检索数据时,才使用行锁,否则用表锁
  • 也就是如果select的where列不是索引列,那么此时,实际上是给整个表加锁
  • 检索条件是索引时,又分索引是聚集索引还是非聚集索引,如果是聚集索引(索引上叶节点存放的整条记录),在聚集索引上的加上行锁,而如果是非聚集索引,要在非聚集索引记录上加行锁,又要在聚集索引上加行锁

什么时候使用表锁

  • 事务需要更新表的大部分数据。如果每一行都加上排他锁,锁冲突严重,那么此时执行效率低
  • 事务涉及多个表,比较复杂

死锁

MyISAM

  • 由于加的是表锁,一次性锁定要需要的表,所以不会出现死锁

InnoDB

  • 死锁一般是事务相互等待对方资源
  • 发生死锁后,会自动检测,并让一个事务释放锁并回退
session1 session2
update user set username='jiajun' where id=1 update user set username='jiajun' where id=2
update user set username='jiajun' where id=2 update user set username='jiajun' where id=1
  • 上面就是死锁的列子

乐观锁与悲观锁

  • 乐观锁和悲观锁是一种思想,不是上面提到的数据库提供的锁,是并发控制的一种手段
  • 有这样的场景,两个事务对商品(id=666)进行下单

乐观锁

  • 有一个statu字段,未下单为0,下单为1
  • 先查statu字段,下单进行更新, 再查询一次statu看是否与之前查的一样,一样的话commit,不一样的话rollback
  • 实现方式:加一个版本号或者时间戳,提交前进行一次比较

悲观锁

  • 直接对id为666的行加排他锁
  • 实现方式:通过数据库的锁机制进行加锁

对比

  • 悲观锁比较谨慎,采用加锁机制,一定程度下降低了并发性
  • 乐观锁认为一般情况下不会发生冲突,并发性较好
  • 从冲突量和重试代价来衡量,如果冲突很少,或者重试代价不会很严重,那么通常情况下应该选择乐观锁,因为它能得到更好的并发性,而且更容易实现。但是,如果冲突的代价严重,那么就需要使用悲观策略

两段锁原则

两段锁

  • 是一个加锁原则,一种协议
  • 锁操作分为两个阶段,加锁阶段只加锁不放锁,解锁阶段只放锁不加锁
  • 比如一个事务中两个update语句,此时执行完第一个update不会立刻放锁,等加锁阶段完成后再进行放锁,也就是加锁和解锁不相交
  • 事务开始后就处于加锁阶段,一直到执行ROLLBACK和COMMIT之前都是加锁阶段。ROLLBACK和COMMIT使事务进入解锁阶段,即在ROLLBACK和COMMIT模块中DBMS释放所有*。

我觉得分享是一种精神,分享是我的乐趣所在,不是说我觉得我讲得一定是对的,我讲得可能很多是不对的,但是我希望我讲的东西是我人生的体验和思考,是给很多人反思,也许给你一秒钟、半秒钟,哪怕说一句话有点道理,引发自己内心的感触,这就是我最大的价值。(这是我喜欢的一句话,也是我写博客的初衷)

作者:jiajun 出处: http://www.cnblogs.com/-new/

本文版权归作者和博客园共有,欢迎转载,但未经作者同意必须保留此段声明,且在文章页面明显位置给出原文连接,否则保留追究法律责任的权利。如果觉得还有帮助的话,可以点一下右下角的【推荐】,希望能够持续的为大家带来好的技术文章!想跟我一起进步么?那就【关注】我吧。