上一篇文章我们提到MySQL InnoDB对数据行的锁定类型一共有四种:共享锁(读锁,S锁)、排他锁(写锁,X锁)、意向共享锁(IS锁)和意向排他锁(IX锁),今天我们要讨论的是MySQL InnoDB对数据行的锁定方式。
MySQL InnoDB支持三种行锁定方式:
- 行锁(Record Lock):锁直接加在索引记录上面。
- 间隙锁(Gap Lock):锁加在不存在的空闲空间,可以是两个索引记录之间,也可能是第一个索引记录之前或最后一个索引之后的空间。
- Next-Key Lock:行锁与间隙锁组合起来用就叫做Next-Key Lock。
默认情况下,InnoDB工作在可重复读隔离级别下,并且以Next-Key Lock的方式对数据行进行加锁,这样可以有效防止幻读的发生。Next-Key Lock是行锁与间隙锁的组合,这样,当InnoDB扫描索引记录的时候,会首先对选中的索引记录加上行锁(Record Lock),再对索引记录两边的间隙加上间隙锁(Gap Lock)。如果一个间隙被事务T1加了锁,其它事务是不能在这个间隙插入记录的。
我们来看看例子,首先建一张表。
CREATE TABLE tb1 ( id int(11) NOT NULL, id2 int(11) NOT NULL, PRIMARY KEY (id), KEY idx (id2) ) ENGINE=InnoDB DEFAULT CHARSET=utf8
创建一些记录。
insert into tb1 values(1, 3), (2, 6), (3, 9);
tb1表现在有3条记录,其中普通索引字段id2的值3、6、9把间隙分成了四份:(-,3)、(3、6)、(6、9)、(9、+)。现在我们看看基于id2 = 6加锁的情况,会话S1中对id2 = 6的记录加S锁。
mysql> start transaction; Query OK, 0 rows affected (0.00 sec) mysql> select * from tb1 where id2 = 6 lock in share mode; +----+-----+ | id | id2 | +----+-----+ | 2 | 6 | +----+-----+ 1 row in set (0.00 sec) mysql>
会话S2中尝试插入id2 = 5或id2 = 7的记录。
mysql> start transaction; Query OK, 0 rows affected (0.00 sec) mysql> insert into tb1 values(4, 5); ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction mysql> insert into tb1 values(4, 7); ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction mysql>
从结果可知,会话2发生了锁等待超时,因为会话S1中的事务锁住了这些空隙(3、6)与(6、9)。如果插入的记录是id2 = 1或id2 = 10,那就不会有问题,因为这些间隙没有被任何事务锁住,如:
mysql> start transaction; Query OK, 0 rows affected (0.00 sec) mysql> insert into tb1 values(4, 5); ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction mysql> insert into tb1 values(4, 7); ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction mysql> insert into tb1 values(4, 2); Query OK, 1 row affected (0.00 sec) mysql> insert into tb1 values(5, 10); Query OK, 1 row affected (0.00 sec) mysql>
间隙锁在InnoDB的唯一作用就是防止其它事务的插入操作,以此来达到防止幻读的发生,所以间隙锁不分什么共享锁与排它锁。另外,在上面的例子中,我们选择的是一个普通(非唯一)索引字段来测试的,这不是随便选的,因为如果InnoDB扫描的是一个主键、或是一个唯一索引的话,那InnoDB只会采用行锁方式来加锁,而不会使用Next-Key Lock的方式,也就是说不会对索引之间的间隙加锁,仔细想想的话,这个并不难理解,大家也可以自己测试一下。
要禁止间隙锁的话,可以把隔离级别降为读已提交,或者开启参数innodb_locks_unsafe_for_binlog。
相关推荐
MySQL的引擎简介,InnoDB的锁机制与事务隔离级别
主要给大家介绍了关于MySQL InnoDB中锁机制的相关资料,文中通过示例代码介绍的非常详细,对大家学习或者使用MySQL具有一定的参考学习价值,需要的朋友们下面来一起学习学习吧
关于mysql中的innodb存储引擎的索引机制简介,详细介绍了锁机制
内容深入,从源代码的角度深度解析了InnoDB的体系结构、实现原理、工作机制,并给出了大量最佳实践,能帮助你系统而深入地掌握InnoDB,更重要的是,它能为你设计和管理高性能、高可用的数据库系统提供绝佳的指导。...
相对其他数据库而言,MySQL的锁机制比较简单,其最显著的特点是不同的存储引擎支持不同的锁机制。比如,MyISAM和MEMORY存储引擎采用的是表级锁(table-level locking);BDB存储引擎采用的是页面锁(page-level ...
卷1》由资深MySQL专家,机工畅销图书作者亲自执笔,在以往出版的两本InnoDB介绍性图书的基础之上,更深入地介绍InnoDB存储引擎的内核,例如latch、B+树索引、事务、锁等,从源代码的角度深度解析了InnoDB的体系结构...
本文实例讲述了MySQL锁机制与用法。分享给大家供大家参考,具体如下: MySQL的锁机制比较简单,其最显著的特点是不同的存储引擎支持不同的锁机制。比如,MyISAM和MEMORY存储引擎采用的是表级锁;BDB存储引擎采用的是...
数据库也会发生死锁的现象,数据库系统实现了各种死锁检测和死锁超时机制来解除死锁,锁监视器进行死锁检测,MySQL的InnoDB处理死锁的方式是 将持有最少行级排它锁的事务进行回滚,相对比较简单的死锁回滚办法
本文主要论述关于mysql锁机制,mysql版本为5.7,引擎为innodb,由于实际中关于innodb锁相关的知识及加锁方式很多,所以没有那么多精力罗列所有场景下的加锁过程并加以分析,仅根据现在了解的知识,结合官方文档,...
锁的基本介绍 要说锁,应该追溯到操作系统中的多线程原理,锁...相对其他数据库而言,MySQL的锁机制比较简单,其最 显著的特点是不同的存储引擎支持不同的锁机制。比如,MyISAM和MEMORY存储引擎采用的是表级锁(table-
1.基础篇 1.1 explain执行计划 1.2 sq|编程 1.3数据类型 1.4查询处理 1.5子查询 1.6联接与集合操作 ...4.2InnoDB锁问题 5.优化MySQL Server 5.1MySQL体系结构概览内存管理及优化 5.2InnoDB log机制及优化
第一课数据库介绍篇.pdf 第七课MySQL数据库设计.pdf 第三十一课percona-toolkits 的实战及自动化.pdf ... 第十课MySQL8.0锁机制和事务.pdf 第十课MySQL锁机制和事务.pdf 第四课SQL基础语法.pdf
MySQL 索引与innodb锁机制 day027-Secondary Index day028-join算法锁_1 day029-锁_2 day030-锁_3 day031-锁_4 day032-锁_5 day032-锁5标清 day033-锁_6 事物_1 day033-锁_6 事物1标清 day034-事物_2 MySQL 性能衡量...
数据库通过锁以及锁协议来进行并发控制,解决并发事务带来的问题,本篇博文主要是解析数据库的锁协议和Mysql的默认存储引擎InnoDB的锁机制。 如果对事务隔离级别以及并发事务带来的问题不熟悉可以翻阅我的另外一篇...
│ 第十课MySQL8.0锁机制和事务.pdf │ 第十课MySQL锁机制和事务.pdf │ 第四课SQL基础语法.pdf │ ├─新版MySQL DBA综合实战班 第01天 │ 0_MySQL高级DBA公开课视频.avi │ 1_数据库通用知识介绍.avi │ 2_MySQL8...
sql的执行顺序 索引的优点和缺点 怎么避免索引失效(也属于sql优化的一种) 一条sql查询非常慢,我们怎么去排查...锁机制与InnoDB锁算法 从锁的类别上分MySQL都有哪些锁呢?像上面那样子进行锁定岂不是有点阻碍并发效率了
这篇文章主要是对MySQL的三级锁及其应用场景进行简要介绍。 页级:引擎 BDB。 表级:引擎 MyISAM , 理解为锁住整个表,可以同时读,写不行 行级:引擎 INNODB , 单独的一行记录加锁 表级,直接锁定整张表...
锁就是其中的一种机制。我们用商场的试衣间来做一个比喻。试衣间供许多消费者使用。因此可能有多个消费者同时要试衣服。为了避免冲突,试衣间的门上装了锁。试衣服的人在里边锁住,其他人就不能从外边打开了。只有...
之前的文章mysql锁机制详解中我们详细讲解了innodb的锁机制,锁机制是用来保证在并发情况下数据的准确性,而要保证数据准确通常需要事务的支持,而mysql存储引擎innodb是通过锁机制来巧妙地实现事务的隔离特性中的4...