MySQL的意向共享锁、意向排它锁和死锁

目录
  • 一、InnoDB的表级锁
  • 二、意向共享锁和意向排它锁
  • 三、死锁
    • 1. 数据库中的死锁
    • 2. 死锁场景以及解决方法
    • 3. 操作
  • 三、锁的优化建议

一、InnoDB的表级锁

在绝大多数情况下应该使用行锁，因为事务和行锁往往是选择InnoDB的理由，但个别情况下也使用表级锁。

事务需要更新大部分或全部数据，表又比较大，如果使用默认的行锁，不仅这个事务执行效率低，而且可能造成其他事务长时间等待和锁冲突事务涉及多个表，比较复杂，很可能引起死锁，造成大量事务回滚

我们希望获取表锁时，执行以下命令：

在使用表锁的时候，涉及的效率问题：
要获取一张表的共享锁S或者排它锁X，首先要确定这张表没有被其它事务获取过X锁，这张表里面的数据没有被其它事务获取过行锁X锁。

假如我们这张表有一千万个数据，怎么判断这一千万个数据哪些行有X锁？
如果想获得表的S锁，就需要判断表里面哪些行有过X锁，如果有一些行有过X锁，那么就不能获取这张表的S锁或者X锁。除了挨个检查，没有更好的办法，这就导致效率低下的问题

由于需要加表锁而去挨个遍历数据，确定是否有某些数据被加了行锁，而导致的效率低下问题。我们这里学习的意向共享锁和意向排他锁就可以解决，当要获取表的X锁是，不需要再检查表中的哪些行锁被（X或者S）占用，只需要快速检查IX和IS锁即可。

二、意向共享锁和意向排它锁

  • 意向共享锁（IS锁）：事务计划给记录加行共享锁，事务在给一行记录加共享锁前，必须先取得该表的IS锁
  • 意向排他锁（IX锁）：事务计划给记录加行排他锁，事务在给一行记录加排他锁前，必须先取得该表的IX锁

  • 在加行锁之前，由InnoDB存储引擎加上表的IS或IX锁
  • 意向锁之间都是兼容的，不会产生冲突，主要是为了辅助其他的在获取表锁的时候加快效率
  • 意向锁存在的意义是为了更高效的获取表锁（表格中的X和S指的是表锁，不是行锁！）
  • 意向锁是表级锁，协调表锁和行锁的共存关系。主要目的是显示事务正在锁定某行或者试图锁定某行。

分析事务1获取行X锁和事务2获取表S锁：

首先事务1需要给表的第10行数据加X锁，于是InnoDB存储引擎自动给整张表加上了IX锁。当事务2再想获取整张表的S锁时，看到这张表已经有别的事务获取了IX锁了，就说明这张表肯定有某些数据被加上了X锁，这就导致事务2不能给整张表加S锁了。此时事务2只能等待，无法成功获取表S锁

三、死锁

1. 数据库中的死锁

MyISAM表锁时deadlock free的，这是因为MyISAM 不支持事务，只支持表锁，总是一次获得所需的全部锁，要么全部满足，要么等待，因此不会出现死锁。但在InnoDB中，除单个SQL组成的事务外，锁是逐步获得的，即锁的粒度比较小，这就决定了在InnoDB中发生死锁是可能的。当然如果处理多张表，还是有可能出现死锁的。

死锁问题一般都是我们自己造成的，和多线程编程的死锁情况相似，大部分都是由于我们多个线程在获取多个锁资源的时候，获取的顺序不同而导致的死锁问题。因此我们应用在对数据库的多个表做更新的时候，不同的代码段，应对这些表按相同的顺序进行更新操作，以防止锁冲突导致死锁问题。

2. 死锁场景以及解决方法

死锁出现的场景如下：

事务1成功获取行锁1
事务2成功获取行锁2
…
事务1无法获取行锁2，被阻塞的同时没有办法执行commit/rollback，无法释放行锁1
事务2无法获取行锁1，被阻塞的同时没有办法执行commit/rollback，无法释放行锁2

所有的事务都阻塞了，相当于进程内的所有线程都阻塞了，造成了死锁问题。

解决死锁的方法：多个事务/线程获取多个相同资源锁的时候，应该按照同样的顺序，获取资源的锁。

事务被阻塞或者死锁了，mysqld（MySQL Server守护进程）设置有事务阻塞的超时时间，事务不会阻塞很长时间，超时后事务处理失败，自动释放当前占有的锁。

3. 操作

设置手动提交和可重复读隔离级别并开启事务

查询一下表数据，在可重复读隔离级别使用的是MVCC提供的快照读，并没有加锁

事务1获取id=7的排他锁，事务2获取id=8的排他锁

事务1再获取id=8的排它锁，发生阻塞

事务2再获取id=7的排它锁，发生阻塞

此时由于MySQL Server检测到发生了死锁，于是解除事务1的阻塞，进行事务1的rollback，释放其占有的行锁，于是事务2成功获取id=7的排它锁

三、锁的优化建议

  • 在能正确完成业务的前提下，为确保效率，尽量使用较低的隔离级别（必须避免脏读）
  • 设计合理的索引并尽量使用索引访问数据，使加锁更准确，减少锁冲突的机会，提高并发能力
  • 选择合理的事务大小，小事务发生锁冲突的概率小（事务越大，包含的SQL越多，可能包含更多的表资源和行资源的锁，增大了锁冲突的概率）不同的程序访问一组表时，应尽量约定以相同的顺序访问各表，对一个表而言，尽可能以固定的顺序存取表中的行。这样可以大大减少死锁的机会
  • 尽量用相等条件访问数据，这样可以避免间隙锁对并发插入的影响（其实等值查询也会加间隙锁）不要申请超过实际需要的锁级别
  • 除非必须，查询时不要显示加锁（在已提交读和可重复读隔离级别，MVCC提供了读取机制，不需要手动加锁）

到此这篇关于MySQL的意向共享锁、意向排它锁和死锁的文章就介绍到这了,更多相关MySQL锁内容请搜索我们以前的文章或继续浏览下面的相关文章希望大家以后多多支持我们！

赞 (0)

相关推荐

  • MySQL表锁、行锁、排它锁及共享锁的使用详解

    MySQL表锁、行锁、排它锁及共享锁的使用详解

    目录 前言 一.事务隔离机制的选择 二.表级锁&行级锁 三.排它锁(Exclusive)和共享锁(Shared) 1. 测试不同事务之间排它锁和共享锁的兼容性 2. 测试行锁加在索引项上 四.串行化隔离级别测试 总结 前言 事务隔离级别的实现原理:简单来说就是各种锁机制和MVCC多版本并发控制 我们学习知识的时候,需要了解知识点出现的原因,什么情况下能用到这个知识 我们说到事务,就得说到事务的ACID特性,说到隔离性的时候,事务要能够允许并发执行,并发执行为了同时保证数据的安全性,一致性和并发的

  • 一文学习MySQL 意向共享锁、意向排他锁、死锁

    一文学习MySQL 意向共享锁、意向排他锁、死锁

    目录 一.InnoDB表级锁 二.意向共享锁和意向排他锁 三.死锁 1. 数据库中的死锁 2. 死锁场景以及解决办法 3. 操作 四.锁的优化建议 一.InnoDB表级锁 我们知道,InnoDB是支持行锁,但不是每次都获取行锁,如果不使用索引的,那还是获取的表锁.而且有的时候,我们希望直接去使用表锁 在绝大部分情况下都应该使用行锁,因为事务的并发效率比表锁更高,但个别情况下也使用表级锁: 事务需要更新大部分或全部数据,表又比较大,如果使用默认的行锁,给大部分行都加锁(此时不如直接加表锁),不仅这

  • MySQL死锁使用详解及检测和避免方法

    MySQL死锁使用详解及检测和避免方法

    目录 前言 锁的释放与阻塞 死锁的发生和检测 查看锁信息(日志) 死锁的避免 前言 上一篇博客我们知道的Mysql事务的隔离机制和实现,以及锁的详细解析 链接: MySQL脏读幻读不可重复读及事务的隔离级别和MVCC.LBCC实现 在我们使用锁的时候,有一个问题是需要注意和避免的,我们知道,排它锁有互斥的特性.一个事务或者说一个线程持有锁的时候,会阻止其他的线程获取锁,这个时候会造成阻塞等待,如果循环等待,会有可能造成死锁. 这个问题我们需要从几个方面来分析,一个是锁为什么不释放,第二个是被阻塞

  • MySQL 优化 index merge引起的死锁分析

    MySQL 优化 index merge引起的死锁分析

    目录 背景 死锁日志 表结构 执行计划 为什么会用 index_merge(索引合并) 解决方案 一.从代码层面 二.从MySQL层面 背景 生产环境出现死锁流水,通过查看死锁日志,看到造成死锁的是两条一样的update语句(只有where条件中的值不同), 如下: UPDATE test_table SET `status` = 1 WHERE `trans_id` = 'xxx1' AND `status` = 0; UPDATE test_table SET `status` = 1 WH

  • 浅谈Mysql insert on duplicate key 死锁问题定位与解决

    目录 前言 死锁定位 insert on duplicate key的锁 问题解决 前言 最近在监测线上日志时发现我们一个Mysql业务db时常出现 dead lock,频次不高但却一直出现,定位后发现是在并发场景下的 insert on duplicate key update sql 出现的死锁.经过分析发现这种sql确实比较容易造成死锁,不太适用于我们目前的业务场景,于是更换后解决问题. 这篇文章就从分析死锁展开,到最终如何解决这样的问题 分享相应的思路. 死锁定位 我们目前生产环境使用M

  • Mysql锁机制之行锁、表锁、死锁的实现

    Mysql锁机制之行锁、表锁、死锁的实现

    目录 一.Mysql锁是什么?锁有哪些类别? 二.行锁和表锁的区别 三.InnoDB死锁概念和死锁案例 死锁场景一之selectforupdate: 死锁场景二之两个update 四.程序开发过程中应该如何注意避免死锁 一.Mysql锁是什么?锁有哪些类别? 锁定义:    同一时间同一资源只能被一个线程访问    在数据库中,除传统的计算资源(如CPU.I/O等)的争用以外,数据也是一种供许多用户共享的资源.如何保证数据并发访问的一致性.有效性是所有数据库必须解决的一个问题,锁冲突也是影响数据

  • MySQL锁(表锁,行锁,共享锁,排它锁,间隙锁)使用详解

    锁,在现实生活中是为我们想要隐藏于外界所使用的一种工具.在计算机中,是协调多个进程或县城并发访问某一资源的一种机制.在数据库当中,除了传统的计算资源(CPU.RAM.I/O等等)的争用之外,数据也是一种供许多用户共享访问的资源.如何保证数据并发访问的一致性.有效性,是所有数据库必须解决的一个问题,锁的冲突也是影响数据库并发访问性能的一个重要因素.从这一角度来说,锁对于数据库而言就显得尤为重要. MySQL锁 相对于其他的数据库而言,MySQL的锁机制比较简单,最显著的特点就是不同的存储引擎支持不

  • Mysql超详细讲解死锁问题的理解

    Mysql超详细讲解死锁问题的理解

    目录 1.什么是死锁? 2.Mysql出现死锁的必要条件 资源独占条件 请求和保持条件 不剥夺条件 相互获取锁条件 3. Mysql经典死锁案例 3.1 建表语句 3.2 初始化相关数据 3.3 正常转账过程 3.4 死锁转账过程 3.5 死锁导致的问题 4.如何解决死锁问题? 4.1 打破请求和保持条件 4.2 打破相互获取锁条件(推荐) 5.总结 1.什么是死锁? 死锁指的是在两个或两个以上不同的进程或线程中,由于存在共同资源的竞争或进程(或线程)间的通讯而导致各个线程间相互挂起等待,如果没

  • 一文搞懂Mysql中的共享锁、排他锁、悲观锁、乐观锁及使用场景

    一文搞懂Mysql中的共享锁、排他锁、悲观锁、乐观锁及使用场景

    目录 一.常见锁类型 二.Mysql引擎介绍 三.常用引擎间的区别 四.共享锁与排他锁 五.排他锁的实际应用 六.共享锁的实际应用 七.死锁的发生 八.另一种发生死锁的情景 九.死锁的解决方式 十.意向锁和计划锁 十一.乐观锁和悲观锁 总结 一.常见锁类型 表级锁,锁定整张表 页级锁,锁定一页 行级锁,锁定一行 共享锁,也叫S锁,在MyISAM中也叫读锁 排他锁,也叫X锁,在MyISAM中也叫写锁 悲观锁,抽象性质,其实不真实存在 乐观锁,抽象性质,其实不真实存在 常见锁类型 二.Mysql引擎

  • MySQL的意向共享锁、意向排它锁和死锁

    MySQL的意向共享锁、意向排它锁和死锁

    目录 一.InnoDB的表级锁 二.意向共享锁和意向排它锁 三.死锁 1. 数据库中的死锁 2. 死锁场景以及解决方法 3. 操作 三.锁的优化建议 一.InnoDB的表级锁 在绝大多数情况下应该使用行锁,因为事务和行锁往往是选择InnoDB的理由,但个别情况下也使用表级锁. 事务需要更新大部分或全部数据,表又比较大,如果使用默认的行锁,不仅这个事务执行效率低,而且可能造成其他事务长时间等待和锁冲突事务涉及多个表,比较复杂,很可能引起死锁,造成大量事务回滚 我们希望获取表锁时,执行以下命令: 在

  • Mysql 行级锁的使用及死锁的预防方案

    一.前言 mysql的InnoDB,支持事务和行级锁,可以使用行锁来处理用户提现等业务.使用mysql锁的时候有时候会出现死锁,要做好死锁的预防. 二.MySQL行级锁 行级锁又分共享锁和排他锁. 共享锁: 名词解释:共享锁又叫做读锁,所有的事务只能对其进行读操作不能写操作,加上共享锁后其他事务不能再加排他锁了只能加行级锁. 用法: SELECT `id` FROM table WHERE id in(1,2) LOCK IN SHARE MODE 结果集的数据都会加共享锁 排他锁: 名词解释:

  • MySQL中由load data语句引起死锁的解决案例

    MySQL中由load data语句引起死锁的解决案例

    一个线上项目报的死锁,简要说明一下产生原因.处理方案和相关的一些点. 1.背景 这是一个类似数据分析的项目,数据完全通过LOAD DATA语句导入一个InnoDB表中.为方便描述,表结构简化为如下: Create table tb(id int primary key auto_increment, c int not null) engine=innodb; 导入数据的语句对应为 Load data infile 'data1.csv' into table tb; Load data inf

  • MySql 索引、锁、事务知识点小结

    MySql 索引、锁、事务知识点小结

    本文总结了MySql 索引.锁.事务知识点.分享给大家供大家参考,具体如下: 1. 索引 索引,类似书籍的目录,可以根据目录的某个页码立即找到对应的记录. 索引的优点: 天生排序. 快速查找. 索引的缺点: 占用空间. 降低更新表的速度. 注意点:小表使用全表扫描更快,中大表才使用索引.超级大表索引基本无效. 索引从实现上说,分成 2 种:聚集索引和辅助索引(也叫二级索引或者非聚集索引) 从功能上说,分为 6 种:普通索引,唯一索引,主键索引,复合索引,外键索引,全文索引. 详细说说 6 种索引

  • MySQL对于各种锁的概念理解

    乐观锁 乐观锁大多是基于数据版本记录机制实现,一般是给数据库表增加一个"version"字段.读取数据时,将此版本号一同读出,之后更新时,对此版本号加一.此时将提交数据的版本数据与数据库表对应记录的当前版本信息进行比对,如果提交的数据版本号大于数据库表当前版本号,则予以更新,否则认为是过期数据. 比如下单操作: 查询出商品信息. select (quantity, version) from t_goods where id = #{id} 根据商品信息生成订单. 将商品数量减1. u

  • 一文分析SQL Server中事务使用的锁

    目录 序 SQL Server使用的锁及锁对象 SQL Server执行Select时使用的锁 SQL Server执行insert时使用的锁 SQL Server执行update时使用的锁 总结 序 本文属于基础知识的回顾,在日常技术交流和日常工作中经常发现有些同事了解关于数据库事务的基本知识,会看SQL语句的执行计划,也知道数据库有X锁.U锁和S锁等各种锁,但是对于这些锁在数据库事务执行期间是如何工作?为何这样配合才能完成数据库事务?数据库是如何对于各种资源加锁的?等等这类的问题不太了解,那

  • MySQL的表级锁,行级锁,排它锁和共享锁

    MySQL的表级锁,行级锁,排它锁和共享锁

    目录 前言 一.表级锁&行级锁 二.排它锁&共享锁 1. 测试不同事务之间排它锁和共享锁的兼容性 2. 测试行锁加在索引项上 三.串行化隔离级别测试 前言 如果我们和面试官聊到事务的问题,怎么回答呢? 先说下事务是什么,因为我们业务是比较复杂的,不可能一个sql就能解决的,涉及多个sql就组成一个事务.事务就是一组sql共同执行,要么完全成功,要么完全失败,不能出现部分成功或者部分失败的情况.一个事务有ACID特性(可以参考:事务的ACID特性和MySQL事务的隔离级别): 原子性:要么全

随机推荐