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/

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

相关文章