文档章节

【58沈剑 架构师之路】InnoDB,select为啥会阻塞insert?

张锦飞
 张锦飞
发布于 2018/12/11 16:22
字数 1441
阅读 340
收藏 0

MySQL的InnoDB的细粒度行锁,是它最吸引人的特性之一。

但是,如《InnoDB,5项最佳实践》所述,如果查询没有命中索引,也将退化为表锁。

InnoDB的细粒度锁,是实现在索引记录上的。

 

一,InnoDB的索引

InnoDB的索引有两类索引,聚集索引(Clustered Index)与普通索引(Secondary Index)。

 

InnoDB的每一个表都会有聚集索引

(1)如果表定义了PK,则PK就是聚集索引;

(2)如果表没有定义PK,则第一个非空unique列是聚集索引;

(3)否则,InnoDB会创建一个隐藏的row-id作为聚集索引;

为了方便说明,后文都将以PK说明。

 

索引的结构是B+树,这里不展开B+树的细节,说几个结论:

(1)在索引结构中,非叶子节点存储key,叶子节点存储value;

(2)聚集索引叶子节点存储行记录(row)

画外音:所以,InnoDB索引和记录是存储在一起的,而MyISAM的索引和记录是分开存储的。

 

(3)普通索引叶子节点存储了PK的值

画外音:

所以,InnoDB的普通索引,实际上会扫描两遍:

第一遍,由普通索引找到PK;

第二遍,由PK找到行记录;

索引结构,InnoDB/MyISAM的索引结构,如果大家感兴趣,未来撰文详述。

 

举个例子,假设有InnoDB表:

t(id PK, name KEY, sex, flag);

 

表中有四条记录:

1, shenjian, m, A

3, zhangsan, m, A

5, lisi, m, A

9, wangwu, f, B

以看到:

(1)第一幅图,id PK的聚集索引,叶子存储了所有的行记录;

(2)第二幅图,name上的普通索引,叶子存储了PK的值;

 

对于:

select * from t where name=’shenjian’;

(1)会先在name普通索引上查询到PK=1;

(2)再在聚集索引衫查询到(1,shenjian, m, A)的行记录;

 

下文简单介绍InnoDB七种锁中的剩下三种:

  • 记录锁(Record Locks)

  • 间隙锁(Gap Locks)

  • 临键锁(Next-Key Locks)

为了方便讲述,如无特殊说明,后文中,默认的事务隔离级别为可重复读(Repeated Read, RR)。

 

二、记录锁(Record Locks)

记录锁,它封锁索引记录,例如:

select * from t where id=1 for update;

 

它会在id=1的索引记录上加锁,以阻止其他事务插入,更新,删除id=1的这一行。

 

需要说明的是:

select * from t where id=1;

则是快照读(SnapShot Read),它并不加锁,具体在《InnoDB为什么并发高,读取快?》中做了详细阐述。

 

三、间隙锁(Gap Locks)

间隙锁,它封锁索引记录中的间隔,或者第一条索引记录之前的范围,又或者最后一条索引记录之后的范围。

 

依然是上面的例子,InnoDB,RR:

t(id PK, name KEY, sex, flag);

 

表中有四条记录:

1, shenjian, m, A

3, zhangsan, m, A

5, lisi, m, A

9, wangwu, f, B

 

这个SQL语句

select * from t 

    where id between 8 and 15 

    for update;

会封锁区间,以阻止其他事务id=10的记录插入。

画外音:

为什么要阻止id=10的记录插入?

如果能够插入成功,头一个事务执行相同的SQL语句,会发现结果集多出了一条记录,即幻影数据。

 

间隙锁的主要目的,就是为了防止其他事务在间隔中插入数据,以导致“不可重复读”。

 

如果把事务的隔离级别降级为读提交(Read Committed, RC),间隙锁则会自动失效。

 

四、临键锁(Next-Key Locks)

临键锁,是记录锁与间隙锁的组合,它的封锁范围,既包含索引记录,又包含索引区间。

 

更具体的,临键锁会封锁索引记录本身,以及索引记录之前的区间。

 

如果一个会话占有了索引记录R的共享/排他锁,其他会话不能立刻在R之前的区间插入新的索引记录。

画外音:原文是说

If one session has a shared or exclusive lock on record R in an index, another session cannot insert a new index record in the gap immediately before R in the index order.

 

依然是上面的例子,InnoDB,RR:

t(id PK, name KEY, sex, flag);

 

表中有四条记录:

1, shenjian, m, A

3, zhangsan, m, A

5, lisi, m, A

9, wangwu, f, B

 

PK上潜在的临键锁为:

(-infinity, 1]

(1, 3]

(3, 5]

(5, 9]

(9, +infinity]

 

临键锁的主要目的,也是为了避免幻读(Phantom Read)。如果把事务的隔离级别降级为RC,临键锁则也会失效。

画外音:关于事务的隔离级别,以及幻读,之前的文章一直没有展开说明,如果大家感兴趣,后文详述。

 

今天的内容,主要对InnoDB的索引,以及三种锁的概念做了介绍。场景与例子,也都是最简单的场景与最简单的例子。

 

InnoDB的锁,与索引类型,事务的隔离级别相关,更多更复杂更有趣的案例,后续和大家介绍。

 

五、总结

(1)InnoDB的索引与行记录存储在一起,这一点和MyISAM不一样;

(2)InnoDB的聚集索引存储行记录,普通索引存储PK,所以普通索引要查询两次

(3)记录锁锁定索引记录;

(4)间隙锁锁定间隔,防止间隔中被其他事务插入;

(5)临键锁锁定索引记录+间隔,防止幻读;

本文转载自:https://mp.weixin.qq.com/s/y_f2qrZvZe_F4_HPnwVjOw

张锦飞

张锦飞

粉丝 7
博文 65
码字总数 5198
作品 1
武汉
程序员
私信 提问
加载中

评论(0)

【 58沈剑 架构师之路】4种事务的隔离级别,InnoDB如何巧妙实现?

事务ACID特性,其中I代表隔离性(Isolation)。 什么是事务的隔离性? 隔离性是指,多个用户的并发事务访问同一个数据库时,一个用户的事务不应该被其他用户的事务干扰,多个并发事务之间要相互...

张锦飞
2018/12/11
76
0
【 58沈剑 架构师之路】InnoDB七种锁——共享/排它锁、意向锁、插入意向锁

今天,将要介绍InnoDB另外三种:共享/排他锁,意向锁,插入意向锁。 一,共享/排它锁(Shared and Exclusive Locks) 《InnoDB并发为何这么高?》一文介绍了通用的共享/排它锁,在InnoDB里当然...

张锦飞
2018/12/07
462
0
【 58沈剑 架构师之路】各种SQL到底加了什么锁?

有朋友留言:你TM讲了这么多,锁分了这么多类型,又和事务隔离级别相关,又和索引相关,究竟能不能直接告诉我,一个SQL到底加了什么锁!? 我竟无言以对。 好吧,做过简单梳理之后,今天尝试...

张锦飞
2018/12/12
157
0
【58沈剑 架构师之路】InnoDB并发如此高,原因竟然在这?

一、并发控制 为啥要进行并发控制? 并发的任务对同一个临界资源进行操作,如果不采取措施,可能导致不一致,故必须进行并发控制(Concurrency Control)。 技术上,通常如何进行并发控制? ...

张锦飞
2018/12/07
76
0
【 58沈剑 架构师之路】InnoDB七种锁——自增锁(Auto-inc Locks)

一,案例说明 MySQL,InnoDB,默认的隔离级别(RR),假设有数据表: t(id AUTO_INCREMENT, name); 数据表中有数据: 1, shenjian 2, zhangsan 3, lisi 事务A先执行,还未提交: insert into ...

张锦飞
2018/12/07
101
0

没有更多内容

加载失败,请刷新页面

加载更多

常用网络安全命令

常用网络安全命令 0. 首先打开cmd win+r 1. ipconfig命令 主要功能:显示本地主机IP地址、子网掩码、默认网关、MAC地址等 C:\> ipconfig/all 2.ping命令 主要功能:目标主机的可达性、名称、...

osc_6n4iy0i5
13分钟前
26
0
element-ui 响应式布局-栅格布局

要注意的问题 xs sm md lg xl五个尺寸的默认值均为24,意味着,任何一个尺寸属性不设置,则该尺寸下响应式宽度为24,这与bootstrap不同 尺寸属性可以设为0,则该el-col不显示 不论尺寸属性设...

osc_7ehwx8hw
15分钟前
23
0
初识二进制与软件破解

文章目录 简介 环境和工具 知识铺垫 实验 总结 简介 实验的思路来自《0day安全:软件漏洞技术分析》第一章。此次实验的内容为如何破解一个简单的密码验证功能的exe文件。通过此次实验可以大体...

osc_z3ivsxnp
16分钟前
27
0
【Q&A干货分享】敲重点,企业必须关注的等保灵魂20问

随着国内疫情防控取得积极成效,企业逐步复工复产,等保合规也重新提上重要日程。自去年12月1日,我国《信息安全等级保护管理办法》正式进入2.0时代,但目前传统的等保安全产品,市面上产品繁...

osc_1ajf1srl
17分钟前
11
0
大众点评字体反爬解析

有关大众点评字体反爬的解析 前几天收到一条私信是有关大众点评的字体反爬的问题,经过分析来此跟大家分享一下解决这个反爬的方法。 1.我们随便打开大众点评一个商家的网页,如下图 2.右键点...

osc_cgllnrkd
18分钟前
36
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部