前面两篇文章讨论了MySQL InnoDB的锁类型与加锁方式,这次,我们来看看在不同的场景下,不同的SQL会以什么样的方式加什么类型的锁。
在开始之前,我们先了解一下什么是聚族索引?
每一张InnoDB表都有且仅有一表特殊的索引,聚族索引(Clustered Index),表中的数据是直接存放在聚族索引的叶子节点页面中,这样,根据聚族索引查询就会比普通索引更快,因为少了一次IO操作。通常,聚族索引就是表的主键;如果表没有主键,那InnoDB会把第一个非空的唯一索引当作聚族索引;如果表既无主键,又无非空的唯一索引,那么InnoDB会创建一个隐藏的索引。表中的其它全部索引,都叫做第二索引(Secondary Index),第二索引中只包含自身索引列和聚族索引列的内容,所以当一个表的主键很长时,其它的索引都会受到影响。
为什么要先讲聚族索引呢?因为这对理解InnoDB加锁机制很重要,InnoDB加锁的对象不是返回的数据记录,而是查询这些数据时所扫描过的索引。当我们执行一个锁读(SELECT ... LOCK IN SHARE MODE或者SELECT ... FOR UPDATE)时,InnoDB不是对最终的返回结果加锁,而是对查询这些结果时所扫描的索引加锁,如果被扫描的索引不是聚族索引,那被扫描的索引所指向的聚族索引以及其它指向相同聚族索引的索引也会被加锁。由此可知,当一个锁读无法使用索引的话,InnoDB就是遍历整个表(遍历整个聚族索引),从而把整张表都锁住。
我们来看一个例子,首先创建一张表:
CREATE TABLE `tb` ( `id1` int(11) NOT NULL, `id2` int(11) NOT NULL, `id3` int(11) NOT NULL, `id4` int(11) DEFAULT NULL, PRIMARY KEY (`id1`), UNIQUE KEY `uidx` (`id2`), KEY `idx` (`id3`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8
插入一些数据:
mysql> select * from tb; +-----+-----+-----+------+ | id1 | id2 | id3 | id4 | +-----+-----+-----+------+ | 1 | 1 | 1 | 1 | | 5 | 5 | 5 | 5 | | 9 | 9 | 9 | 9 | +-----+-----+-----+------+
会话S1根据id4查询一条记录
mysql> start transaction; Query OK, 0 rows affected (0.00 sec) mysql> select * from tb where id4 = 1 lock in share mode; +-----+-----+-----+------+ | id1 | id2 | id3 | id4 | +-----+-----+-----+------+ | 1 | 1 | 1 | 1 | +-----+-----+-----+------+ 1 row in set (0.00 sec) mysql>
接着会话S2中尝试对id2=5的记录加锁。
mysql> start transaction; Query OK, 0 rows affected (0.00 sec) mysql> select * from tb where id2 = 5 for update; ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction mysql>
发生了锁等待超时,因为会话S1根据非索引字段id4查询,InnoDB会扫描整个聚族索引(字段id1),并对扫描过的聚族索引及所有指向相同聚族索引的其它索引都加锁(本例中所有的索引都被加锁了),所以会话S2在尝试对id2=5的记录加锁时只能等待了。由此可见,正确的设计和使用索引,不光对性能有影响,对并行性的影响也至关重要。
再看一个例子,在可重复读隔离级别下,会话S1以id3=5(普通索引)字段加锁查询tb表
mysql> select * from tb; +-----+-----+-----+------+ | id1 | id2 | id3 | id4 | +-----+-----+-----+------+ | 1 | 1 | 1 | 1 | | 5 | 5 | 5 | 5 | | 9 | 9 | 9 | 9 | +-----+-----+-----+------+ 3 rows in set (0.01 sec) mysql> set session transaction isolation level repeatable read; Query OK, 0 rows affected (0.00 sec) mysql> start transaction; Query OK, 0 rows affected (0.00 sec) mysql> select * from tb where id3=5 for update; +-----+-----+-----+------+ | id1 | id2 | id3 | id4 | +-----+-----+-----+------+ | 5 | 5 | 5 | 5 | +-----+-----+-----+------+ 1 row in set (0.01 sec) mysql>
会话S2的情况如下
mysql> set session transaction isolation level repeatable read; Query OK, 0 rows affected (0.00 sec) mysql> start transaction; Query OK, 0 rows affected (0.00 sec) mysql> select * from tb where id3 = 5 for update; ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction mysql> insert into tb(id1,id2,id3,id4) values(2,2,2,2); ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction mysql> insert into tb(id1,id2,id3,id4) values(8,8,8,8); ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction mysql> update tb set id4 = 6 where id2 = 5; ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction mysql> update tb set id4 = 6 where id1 = 5; ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction mysql>
相关推荐
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-
这篇文章主要是对MySQL的三级锁及其应用场景进行简要介绍。 页级:引擎 BDB。 表级:引擎 MyISAM , 理解为锁住整个表,可以同时读,写不行 行级:引擎 INNODB , 单独的一行记录加锁 表级,直接锁定整张表...
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的锁机制。 如果对事务隔离级别以及并发事务带来的问题不熟悉可以翻阅我的另外一篇...
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 性能衡量...
│ 第十课MySQL8.0锁机制和事务.pdf │ 第十课MySQL锁机制和事务.pdf │ 第四课SQL基础语法.pdf │ ├─新版MySQL DBA综合实战班 第01天 │ 0_MySQL高级DBA公开课视频.avi │ 1_数据库通用知识介绍.avi │ 2_MySQL8...
sql的执行顺序 索引的优点和缺点 怎么避免索引失效(也属于sql优化的一种) 一条sql查询非常慢,我们怎么去排查...锁机制与InnoDB锁算法 从锁的类别上分MySQL都有哪些锁呢?像上面那样子进行锁定岂不是有点阻碍并发效率了
锁就是其中的一种机制。我们用商场的试衣间来做一个比喻。试衣间供许多消费者使用。因此可能有多个消费者同时要试衣服。为了避免冲突,试衣间的门上装了锁。试衣服的人在里边锁住,其他人就不能从外边打开了。只有...
之前的文章mysql锁机制详解中我们详细讲解了innodb的锁机制,锁机制是用来保证在并发情况下数据的准确性,而要保证数据准确通常需要事务的支持,而mysql存储引擎innodb是通过锁机制来巧妙地实现事务的隔离特性中的4...