AI 助理
备案控制台
开发者社区 数据库 文章 正文

从一个案例深入剖析InnoDB隐式锁和可见性判断(2)

2022-10-03 187
版权
版权声明：
本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《 阿里云开发者社区用户服务协议》和 《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写 侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。
简介： 从一个案例深入剖析InnoDB隐式锁和可见性判断
4、关于page的max trx id

我们上面多次提到二级索引page的max trx id，这个max trx id实际就是PAGE_MAX_TRX_ID，它位于page的offset 56后的8个字节，实际上这个值只会存在于二级索引上，主键没有这个值，我们可以看到如下：

表结构和数据
mysql> show create table testimp4 \G
*************************** 1. row ***************************
       Table: testimp4
Create Table: CREATE TABLE `testimp4` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `a` int(11) DEFAULT NULL,
  `b` int(11) DEFAULT NULL,
  `d` varchar(200) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `b` (`b`),
  KEY `d` (`d`)
) ENGINE=InnoDB AUTO_INCREMENT=10000 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql> select *from testimp4;
+------+------+------+------------------------------------+
| id   | a    | b    | d                                  |
+------+------+------+------------------------------------+
|    5 |    5 |  300 | NULL                               |
|    6 | 7000 | 7700 | 1124                               |
|   11 | 7000 | 7700 | 1124                               |
|   12 | 7000 | 7700 | 1124                               |
|   13 | 2900 | 1800 | NULL                               |
|   14 | 2900 | 1800 | NULL                               |
| 1000 |   88 | 1499 | NULL                               |
| 4000 | 6000 | 5904 | iiiafsafasfihhhccccchhhigggofgo111 |
| 4001 | 7000 | 7700 | 1124454555                         |
| 9999 | 9999 | 9999 | a                                  |
+------+------+------+------------------------------------+
10 rows in set (0.00 sec)

每次每行更新后会更新这个值，如果大于则修改，小于则不变。函数page_update_max_trx_id中有如下片段

begin；insert into testimp4 values(10000,10000,10000,'gp');（不提交）

四、关于加锁的阶段

我们一般锁需要加锁的都是DML语句和select for update这样的语句，这里将加锁分为数据查找和数据修改两个阶段。

  • 对于select for update：

主键访问数据：访问主键判断是否存在隐式锁，然后加显示锁。二级索引访问数据（需要回表的情况）：访问二级索引判断是否存在隐式锁，然后加显示锁，接着回表主键判断是否存在隐式锁，然后加显示锁。

  • 对于update/delete：

主键访问修改数据：数据查找阶段主键判断是否存在隐式锁，然后加显示锁。数据修改阶段涉及到了其他二级索引，那么维护相应的二级索引加隐含锁。

二级索引访问修改数据：数据查找阶段二级索引判断是否存在隐式锁（可能需要回表判断），二级索引加显示锁，数据修改阶段回表修改主键数据加显示锁，然后维护各个二级索引（修改字段涉及的二级索引或者修改主键则包含全部二级索引）加隐式锁。

  • 对于insert而言如果没有堵塞（插入印象锁和gap lock堵塞），那么始终为隐式锁。

注意这里我们看到了隐式锁，隐式锁不会占用row的结构体，因此在show engine innodb status里面是看不到的，除非有其他事务显示将其转换为显示锁。我们来做几个例子如下（REPEATABLE READ隔离级别）：

4.1 插入数据
begin；insert into testimp4 values(10000,10000,10000,'gp');（不提交）


image.png

# T1时刻S1锁状态：

---TRANSACTION 94487, ACTIVE 5 sec
 1 lock struct(s), heap size 1160, 0 row lock(s), undo log entries 1
 MySQL thread id 11, OS thread handle 140737089492736, query id 482 localhost root starting
 show engine innodb status
 TABLE LOCK table `test`.`testimp4` trx id 94487 lock mode IX
# T2时刻S1锁状态：
 ---TRANSACTION 94487, ACTIVE 271 sec
 2 lock struct(s), heap size 1160, 1 row lock(s), undo log entries 1
 MySQL thread id 11, OS thread handle 140737089492736, query id 484 localhost root starting
 show engine innodb status
 TABLE LOCK table `test`.`testimp4` trx id 94487 lock mode IX
 RECORD LOCKS space id 501 page no 3 n bits 80 index PRIMARY of table `test`.`testimp4` trx id 94487 lock_mode X locks rec but not gap
 Record lock, heap no 2 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
 0: len 4; hex 80002710; asc ' ;;
 1: len 6; hex 000000017117; asc q ;;
 2: len 7; hex d0000002c40110; asc ;;
 3: len 4; hex 80002710; asc ' ;;
 4: len 4; hex 80002710; asc ' ;;
 5: len 2; hex 6770; asc gp;;
# T3时刻S1锁状态：
 ---TRANSACTION 94487, ACTIVE 337 sec
 3 lock struct(s), heap size 1160, 2 row lock(s), undo log entries 1
 MySQL thread id 11, OS thread handle 140737089492736, query id 521 localhost root starting
 show engine innodb status
 TABLE LOCK table `test`.`testimp4` trx id 94487 lock mode IX
 RECORD LOCKS space id 501 page no 3 n bits 80 index PRIMARY of table `test`.`testimp4` trx id 94487 lock_mode X locks rec but not gap
 Record lock, heap no 2 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
 0: len 4; hex 80002710; asc ' ;;
 1: len 6; hex 000000017117; asc q ;;
 2: len 7; hex d0000002c40110; asc ;;
 3: len 4; hex 80002710; asc ' ;;
 4: len 4; hex 80002710; asc ' ;;
 5: len 2; hex 6770; asc gp;;
RECORD LOCKS space id 501 page no 4 n bits 80 index b of table `test`.`testimp4` trx id 94487 lock_mode X locks rec but not gap
 Record lock, heap no 2 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 80002710; asc ' ;;
 1: len 4; hex 80002710; asc ' ;;
# T4时刻S1锁状态：
 ---TRANSACTION 94487, ACTIVE 408 sec
 4 lock struct(s), heap size 1160, 3 row lock(s), undo log entries 1
 MySQL thread id 11, OS thread handle 140737089492736, query id 559 localhost root starting
 show engine innodb status
 TABLE LOCK table `test`.`testimp4` trx id 94487 lock mode IX
 RECORD LOCKS space id 501 page no 3 n bits 80 index PRIMARY of table `test`.`testimp4` trx id 94487 lock_mode X locks rec but not gap
 Record lock, heap no 2 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
 0: len 4; hex 80002710; asc ' ;;
 1: len 6; hex 000000017117; asc q ;;
 2: len 7; hex d0000002c40110; asc ;;
 3: len 4; hex 80002710; asc ' ;;
 4: len 4; hex 80002710; asc ' ;;
 5: len 2; hex 6770; asc gp;;
RECORD LOCKS space id 501 page no 4 n bits 80 index b of table `test`.`testimp4` trx id 94487 lock_mode X locks rec but not gap
 Record lock, heap no 2 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 80002710; asc ' ;;
 1: len 4; hex 80002710; asc ' ;;
RECORD LOCKS space id 501 page no 5 n bits 80 index d of table `test`.`testimp4` trx id 94487 lock_mode X locks rec but not gap
 Record lock, heap no 2 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 2; hex 6770; asc gp;;
 1: len 4; hex 80002710; asc ' ;;

实际上我们看到这里insert语句后主键和各个索引都上了隐含锁只是看不到，通过其他S2,S3,S4我们逐步把这些隐式锁转换为了显示锁。

4.2 delete语句通过主键删除数据

image.png

# T1时刻S1锁状态：
---TRANSACTION 94493, ACTIVE 3 sec
2 lock struct(s), heap size 1160, 1 row lock(s), undo log entries 1
MySQL thread id 11, OS thread handle 140737089492736, query id 567 localhost root
TABLE LOCK table `test`.`testimp4` trx id 94493 lock mode IX
RECORD LOCKS space id 501 page no 3 n bits 80 index PRIMARY of table `test`.`testimp4` trx id 94493 lock_mode X locks rec but not gap
Record lock, heap no 12 PHYSICAL RECORD: n_fields 6; compact format; info bits 32
 0: len 4; hex 8000270f; asc   ' ;;
 1: len 6; hex 00000001711d; asc     q ;;
 2: len 7; hex 550000003b071b; asc U   ;  ;;
 3: len 4; hex 8000270f; asc   ' ;;
 4: len 4; hex 8000270f; asc   ' ;;
 5: len 1; hex 61; asc a;;

# T2时刻S1锁状态：
---TRANSACTION 94493, ACTIVE 112 sec
4 lock struct(s), heap size 1160, 3 row lock(s), undo log entries 1
MySQL thread id 11, OS thread handle 140737089492736, query id 567 localhost root
TABLE LOCK table `test`.`testimp4` trx id 94493 lock mode IX
RECORD LOCKS space id 501 page no 3 n bits 80 index PRIMARY of table `test`.`testimp4` trx id 94493 lock_mode X locks rec but not gap
Record lock, heap no 12 PHYSICAL RECORD: n_fields 6; compact format; info bits 32
 0: len 4; hex 8000270f; asc   ' ;;
 1: len 6; hex 00000001711d; asc     q ;;
 2: len 7; hex 550000003b071b; asc U   ;  ;;
 3: len 4; hex 8000270f; asc   ' ;;
 4: len 4; hex 8000270f; asc   ' ;;
 5: len 1; hex 61; asc a;;

RECORD LOCKS space id 501 page no 4 n bits 80 index b of table `test`.`testimp4` trx id 94493 lock_mode X locks rec but not gap
Record lock, heap no 12 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
 0: len 4; hex 8000270f; asc   ' ;;
 1: len 4; hex 8000270f; asc   ' ;;

# T3时刻S1锁状态：
---TRANSACTION 94493, ACTIVE 133 sec
4 lock struct(s), heap size 1160, 3 row lock(s), undo log entries 1
MySQL thread id 11, OS thread handle 140737089492736, query id 567 localhost root
TABLE LOCK table `test`.`testimp4` trx id 94493 lock mode IX
RECORD LOCKS space id 501 page no 3 n bits 80 index PRIMARY of table `test`.`testimp4` trx id 94493 lock_mode X locks rec but not gap
Record lock, heap no 12 PHYSICAL RECORD: n_fields 6; compact format; info bits 32
 0: len 4; hex 8000270f; asc   ' ;;
 1: len 6; hex 00000001711d; asc     q ;;
 2: len 7; hex 550000003b071b; asc U   ;  ;;
 3: len 4; hex 8000270f; asc   ' ;;
 4: len 4; hex 8000270f; asc   ' ;;
 5: len 1; hex 61; asc a;;

RECORD LOCKS space id 501 page no 4 n bits 80 index b of table `test`.`testimp4` trx id 94493 lock_mode X locks rec but not gap
Record lock, heap no 12 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
 0: len 4; hex 8000270f; asc   ' ;;
 1: len 4; hex 8000270f; asc   ' ;;

RECORD LOCKS space id 501 page no 5 n bits 80 index d of table `test`.`testimp4` trx id 94493 lock_mode X locks rec but not gap
Record lock, heap no 12 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
 0: len 1; hex 61; asc a;;
 1: len 4; hex 8000270f; asc   ' ;;


实际上我们看到这里delete语句后，主键加了显示锁，这是因为数据查找阶段需要加显示锁，但是各个二级索引是由于维护而加的是隐式锁，我们通过S2,S3将其转换为了显示锁。

            </div>
文章标签：
云数据库 RDS MySQL 版
SQL
关系型数据库
索引
MySQL
相关实践学习
每个IT人都想学的“Web应用上云经典架构”实战
本实验从Web应用上云这个最基本的、最普遍的需求出发，帮助IT从业者们通过“阿里云Web应用上云解决方案”，了解一个企业级Web应用上云的常见架构，了解如何构建一个高可用、可扩展的企业级应用架构。
MySQL数据库入门学习
本课程通过最流行的开源数据库MySQL带你了解数据库的世界。 &nbsp; 相关的阿里云产品：云数据库RDS MySQL 版 阿里云关系型数据库RDS（Relational Database Service）是一种稳定可靠、可弹性伸缩的在线数据库服务，提供容灾、备份、恢复、迁移等方面的全套解决方案，彻底解决数据库运维的烦恼。 了解产品详情:&nbsp;https://www.aliyun.com/product/rds/mysql&nbsp;
xaubllxwtvaqiu
目录
相关文章
热烈的马
|
运维 安全 数据挖掘
【数据挖掘】离群点概念、类型、检测的挑战概述（图文解释 超详细）
【数据挖掘】离群点概念、类型、检测的挑战概述（图文解释 超详细）
热烈的马
1206 0
请看我回答~
|
传感器 Android开发 开发者
构建高效Android应用：Kotlin的协程与Flow
【4月更文挑战第26天】随着移动应用开发的不断进步，开发者寻求更简洁高效的编码方式以应对复杂多变的业务需求。在众多技术方案中，Kotlin语言凭借其简洁性和强大的功能库逐渐成为Android开发的主流选择。特别是Kotlin的协程和Flow这两个特性，它们为处理异步任务和数据流提供了强大而灵活的工具。本文将深入探讨如何通过Kotlin协程和Flow来优化Android应用性能，实现更加流畅的用户体验，并展示在实际开发中的应用实例。
请看我回答~
227 2
软件算法开发
|
算法 调度
基于遗传模拟退火混合优化算法的车间作业最优调度matlab仿真,输出甘特图
车间作业调度问题（JSSP）通过遗传算法（GA）和模拟退火算法（SA）优化多个作业在并行工作中心上的加工顺序和时间，以最小化总完成时间和机器闲置时间。MATLAB2022a版本运行测试，展示了有效性和可行性。核心程序采用作业列表表示法，结合遗传操作和模拟退火过程，提高算法性能。
软件算法开发
350 9
疯狂的猿
|
8月前
|
前端开发 数据安全/隐私保护
“WP页面水印助手”的WordPress插件
“WP页面水印助手”的WordPress插件
疯狂的猿
208 14
TsingtaoAI
|
8月前
|
机器学习/深度学习 人工智能 自动驾驶
企业内训｜模拟AI场景课程——某汽车厂商
4月18日和19日，东北某市，TsingtaoAI团队为某汽车厂商的智能驾驶业务和研发团队交付“模拟AI场景课程”。本课程基于该厂商在AI领域的战略布局，结合汽车行业智能化转型趋势，以“场景化、实战化、前瞻性”为核心，聚焦AI技术从理论到落地的全链路。通过模拟真实业务场景（如智能座舱优化、智能制造、自动驾驶仿真），帮助学员掌握AI基础能力，并快速应用于研发、生产、营销等环节。
TsingtaoAI
312 4
派大鑫的工作室
|
存储
rgba为什么最大值是255？？
rgba为什么最大值是255？？
派大鑫的工作室
682 0
土木林森
|
Dart 前端开发 开发工具
【Flutter前端技术开发专栏】Flutter入门指南：搭建开发环境与第一个应用
【4月更文挑战第30天】本文介绍了Flutter SDK的安装和配置过程，以及如何创建并运行第一个Flutter应用。首先确保安装了Dart SDK和Flutter SDK，支持macOS、Linux和Windows。安装完成后，设置环境变量，然后通过`flutter doctor`验证安装。接着，使用`flutter create`命令创建新项目，进入项目目录并运行`flutter run`启动应用。在`main.dart`中修改代码以自定义应用。Flutter支持热重载和DevTools调试。本文为Flutter初学者提供了快速入门的指导。
土木林森
401 0
【Flutter前端技术开发专栏】Flutter入门指南：搭建开发环境与第一个应用
嘟嘟嘟嘟嘟嘟
|
存储 Java 关系型数据库
数据传输DTS数据问题之数据写入异常如何解决
数据传输服务（DTS）是一项专注于数据迁移和同步的云服务，在使用过程中可能遇到多种问题，本合集精选常见的DTS数据传输问题及其答疑解惑，以助用户顺利实现数据流转。
嘟嘟嘟嘟嘟嘟
280 1
数据传输DTS数据问题之数据写入异常如何解决
猿java
|
SQL 关系型数据库 MySQL
MySQL中的 where 1=1会不会影响性能？看完官方文档就悟了！
本文探讨了在Mybatis中使用`where 1=1`进行动态SQL拼接是否会影响性能。通过MySQL官方资料和实际测试表明，`where 1=1`在MySQL 5.7及以上版本中会被优化器优化，因此对性能影响不大。文中详细对比了`where 1=1`与`&lt;where&gt;`标签的使用方法，并建议根据MySQL版本和团队需求选择合适的方式。最后，推荐查找官方资料以确保技术路线正确。
猿java
299 4
小Tomkk
|
网络虚拟化 Docker 容器
docker Desktop报错 error pulling image configuration 处理
docker Desktop报错 error pulling image configuration 处理
小Tomkk
245 0