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

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

2022-10-03 147
版权
版权声明：
本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《 阿里云开发者社区用户服务协议》和 《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写 侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。
简介： 从一个案例深入剖析InnoDB隐式锁和可见性判断
4.3 delete语句通过二级索引删除数据
TIME S1 S2
T1 begin；delete from testimp4 where b=9999;（不提交）
T2
select * from testimp4 where d='a' for update 


#T1时刻S1锁状态：
---TRANSACTION 94501, ACTIVE 109 sec
3 lock struct(s), heap size 1160, 3 row lock(s), undo log entries 1
MySQL thread id 11, OS thread handle 140737089492736, query id 576 localhost root
TABLE LOCK table `test`.`testimp4` trx id 94501 lock mode IX
RECORD LOCKS space id 501 page no 4 n bits 80 index b of table `test`.`testimp4` trx id 94501 lock_mode X
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
0: len 8; hex 73757072656d756d; asc supremum;;
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 3 n bits 80 index PRIMARY of table `test`.`testimp4` trx id 94501 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 000000017125; asc     q%;;
2: len 7; hex 5a0000002518ea; asc Z   %  ;;
3: len 4; hex 8000270f; asc   ' ;;
4: len 4; hex 8000270f; asc   ' ;;
5: len 1; hex 61; asc a;;

# T2时刻S1锁状态：
---TRANSACTION 94501, ACTIVE 119 sec
4 lock struct(s), heap size 1160, 4 row lock(s), undo log entries 1
MySQL thread id 11, OS thread handle 140737089492736, query id 576 localhost root
TABLE LOCK table `test`.`testimp4` trx id 94501 lock mode IX
RECORD LOCKS space id 501 page no 4 n bits 80 index b of table `test`.`testimp4` trx id 94501 lock_mode X
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
0: len 8; hex 73757072656d756d; asc supremum;;
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 3 n bits 80 index PRIMARY of table `test`.`testimp4` trx id 94501 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 000000017125; asc     q%;;
2: len 7; hex 5a0000002518ea; asc Z   %  ;;
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 5 n bits 80 index d of table `test`.`testimp4` trx id 94501 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语句后，显示二级索引加了显示锁，然后主键加了显示锁，这是因为数据查找阶段先查找的二级索引然后回表查的主键，但是对于二级索引d来讲是由于维护而加的是隐式锁，我们通过S2将其转换为了显示锁。

4.4 update语句通过主键修改数据

这里要特别注意一下，对于二级索引的更新通常是进行了删除和插入，因此这里有2行数据都有隐式锁

TIME S1 S2 S3
T1 begin;update testimp4 set b=10000 where id=9999;（不提交）

T2
select * from testimp4 where b=9999 for update
T3

select * from testimp4 where b=10000 for update 


# T1时刻S1锁状态

---TRANSACTION 94553, ACTIVE 7 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 730 localhost root
 TABLE LOCK table `test`.`testimp4` trx id 94553 lock mode IX
 RECORD LOCKS space id 501 page no 3 n bits 80 index PRIMARY of table `test`.`testimp4` trx id 94553 lock_mode X locks rec but not gap
 Record lock, heap no 12 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
 0: len 4; hex 8000270f; asc ' ;;
 1: len 6; hex 000000017159; asc qY;;
 2: len 7; hex 770000002a187f; asc w * ;;
 3: len 4; hex 8000270f; asc ' ;;
 4: len 4; hex 80002710; asc ' ;;
 5: len 1; hex 61; asc a;;
# T2时刻S1锁状态
 ---TRANSACTION 94553, ACTIVE 62 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 730 localhost root
 TABLE LOCK table `test`.`testimp4` trx id 94553 lock mode IX
 RECORD LOCKS space id 501 page no 3 n bits 80 index PRIMARY of table `test`.`testimp4` trx id 94553 lock_mode X locks rec but not gap
 Record lock, heap no 12 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
 0: len 4; hex 8000270f; asc ' ;;
 1: len 6; hex 000000017159; asc qY;;
 2: len 7; hex 770000002a187f; asc w * ;;
 3: len 4; hex 8000270f; asc ' ;;
 4: len 4; hex 80002710; 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 94553 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 94553, ACTIVE 128 sec
 3 lock struct(s), heap size 1160, 3 row lock(s), undo log entries 1
 MySQL thread id 11, OS thread handle 140737089492736, query id 730 localhost root
 TABLE LOCK table `test`.`testimp4` trx id 94553 lock mode IX
 RECORD LOCKS space id 501 page no 3 n bits 80 index PRIMARY of table `test`.`testimp4` trx id 94553 lock_mode X locks rec but not gap
 Record lock, heap no 12 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
 0: len 4; hex 8000270f; asc ' ;;
 1: len 6; hex 000000017159; asc qY;;
 2: len 7; hex 770000002a187f; asc w * ;;
 3: len 4; hex 8000270f; asc ' ;;
 4: len 4; hex 80002710; 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 94553 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 8000270f; asc ' ;;
 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 ' ;;

这里由于对表的二级索引b通过主键进行了修改，那么二级索引包含了2条数据，一条标记为del flag，另外一条为插入如下：

(11) normal record offset:266 heapno:12 n_owned 0,delflag:Y minflag:0 rectype:0
(12) normal record offset:126 heapno:2 n_owned 0,delflag:N minflag:0 rectype:0
(13) SUPREMUM record offset:112 heapno:1 n_owned 8,delflag:N minflag:0 rectype:3

因此这两行都上了隐式锁，这是由于二级索引维护而上的，值得注意的是二级索引d不会上隐式锁，因为update语句的修改不会涉及到d列索引，因此不会维护。如果查询d列上的值（for update），会获取d列上的锁成功，然后会堵塞在主键id上如下：

---TRANSACTION 94565, ACTIVE 4 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1160, 2 row lock(s)
MySQL thread id 16, OS thread handle 140737086228224, query id 748 localhost root Sending data
select * from testimp4 where d='a' for update
------- TRX HAS BEEN WAITING 4 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 501 page no 3 n bits 80 index PRIMARY of table `test`.`testimp4` trx id 94565 lock_mode X locks rec but not gap waiting
Record lock, heap no 12 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
 0: len 4; hex 8000270f; asc   ' ;;
 1: len 6; hex 000000017161; asc     qa;;
 2: len 7; hex 7c0000002d25eb; asc |   -% ;;
 3: len 4; hex 8000270f; asc   ' ;;
 4: len 4; hex 80002710; asc   ' ;;
 5: len 1; hex 61; asc a;;

------------------
TABLE LOCK table `test`.`testimp4` trx id 94565 lock mode IX
RECORD LOCKS space id 501 page no 5 n bits 80 index d of table `test`.`testimp4` trx id 94565 lock_mode X
Record lock, heap no 12 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 1; hex 61; asc a;;
 1: len 4; hex 8000270f; asc   ' ;;

RECORD LOCKS space id 501 page no 3 n bits 80 index PRIMARY of table `test`.`testimp4` trx id 94565 lock_mode X locks rec but not gap waiting
Record lock, heap no 12 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
 0: len 4; hex 8000270f; asc   ' ;;
 1: len 6; hex 000000017161; asc     qa;;
 2: len 7; hex 7c0000002d25eb; asc |   -% ;;
 3: len 4; hex 8000270f; asc   ' ;;
 4: len 4; hex 80002710; asc   ' ;;
 5: len 1; hex 61; asc a;;

情况还有很多不在一一列举，Innodb行锁一直都是一个令人头疼的问题。


            </div>
文章标签：
云数据库 RDS MySQL 版
关系型数据库
索引
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
目录
相关文章
阿里云开发者
|
29天前
|
存储 算法 AliSQL
AliSQL 向量技术解析（一）：存储格式与算法实现
AliSQL基于MySQL 8.0原生扩展向量处理能力，支持高达16383维的向量存储与计算，集成余弦相似度、欧式距离等函数，并通过HNSW算法实现高效近似最近邻搜索。借助结构化辅助表与精度压缩技术，兼顾检索精度与性能，结合数据字典适配保障DDL原子性，为推荐系统、AI应用提供开箱即用的高维向量检索解决方案。
阿里云开发者
102 2
AliSQL 向量技术解析（一）：存储格式与算法实现
chengfengpolang
|
存储 关系型数据库 MySQL
MySQL的插件式的存储引擎架构是什么意思？底层原理是什么？
MySQL的插件式的存储引擎架构是什么意思？底层原理是什么？
chengfengpolang
525 0
大涛学长
|
SQL 存储 Cloud Native
PolarDB-X 2.0 核心技术能力解读
希望通过本系列课程，让大家更深入了解PolarDB-X 2.0 核心技术能力，加深对PolarDB-X的理解与认识，最终可以实践到大家的工作当中。
大涛学长
1066 0
PolarDB-X 2.0 核心技术能力解读
阿里云瑶池数据库_
|
5月前
|
人工智能 关系型数据库 MySQL
轻松搭建AI知识问答系统，阿里云PolarDB MCP深度实践
无论是PolarDB MySQL兼容MySQL语法的SQL执行功能，还是其特有的OLAP分析与AI能力，通过MCP协议向LLM开放接口后，显著降低了用户使用门槛，更为未来基于DB-Agent的智能体开发奠定了技术基础
阿里云瑶池数据库_
1329 19
老冀
|
SQL 算法 关系型数据库
MySQL InnoDB中的锁-自增锁（AUTO-INC Locks）
MySQL InnoDB 锁 自增锁AUTO-INC Locks
老冀
2638 0
Raymond运维
|
Ubuntu 安全 关系型数据库
MariaDB源码编译安装
本节介绍了在多种 Linux 发行版上通过源码编译安装 MariaDB 的完整过程，包括用户创建、目录准备、依赖安装、源码编译配置（CMake）、编译安装参数说明、错误排查与解决方法，以及后续的数据库初始化、配置文件设置、服务启动与安全加固等内容。整个流程适用于 Rocky、CentOS、openEuler、Ubuntu、openSUSE 等主流系统，具备良好的可操作性和跨平台兼容性。
Raymond运维
266 0
MariaDB源码编译安装
Raymond运维
|
关系型数据库 Linux 数据库
PostgreSQL源码编译安装
本节详细介绍了如何通过源码编译安装 PostgreSQL 17.6，涵盖从源码下载、依赖安装、配置编译参数、执行编译与安装、创建数据库用户与目录、初始化数据库，到配置 systemd 启动服务的完整流程。内容适用于多种 Linux 发行版，如 Rocky Linux、CentOS、openEuler、Ubuntu、Debian 等，并提供了常见错误的解决方法及一键安装脚本，帮助用户高效完成 PostgreSQL 的源码部署。
Raymond运维
558 0
PostgreSQL源码编译安装
东方睿赢
|
关系型数据库 分布式数据库 数据库
【PolarDB开源】PolarDB资源隔离技术：在多租户环境中的应用与优化
【5月更文挑战第29天】PolarDB，阿里云的云原生数据库，在多租户环境中通过逻辑（Schema/Partition隔离）和物理（分布式存储计算节点）隔离保障数据安全和资源独占。它支持动态资源分配，适应不同租户需求，处理大规模并发，提供租户管理及数据访问控制功能。通过优化资源分配算法、提升事务处理能力和强化监控告警，PolarDB确保性能和稳定性，满足多租户的高效数据库服务需求。
东方睿赢
496 1
阿里云瑶池数据库
|
存储 人工智能 自然语言处理
拥抱Data+AI｜B站引入阿里云DMS+X，利用AI赋能运营效率10倍提升
本篇文章针对B站在运营场景中的痛点，深入探讨如何利用阿里云Data+AI解决方案实现智能问数服务，赋能平台用户和运营人员提升自助取数和分析能力，提高价值交付效率的同时为数据平台减负。
阿里云瑶池数据库
1024 10
拥抱Data+AI｜B站引入阿里云DMS+X，利用AI赋能运营效率10倍提升
dotNative
|
Ubuntu 持续交付 API
如何使用 dotnet pack 打包 .NET 跨平台程序集？
`dotnet pack` 是 .NET Core 的 NuGet 包打包工具，用于将代码打包成 NuGet 包。通过命令 `dotnet pack` 可生成 `.nupkg` 文件。使用 `--include-symbols` 和 `--include-source` 选项可分别创建包含调试符号和源文件的包。默认情况下，`dotnet pack` 会先构建项目，可通过 `--no-build` 跳过构建。此外，还可以使用 `--output` 指定输出目录、`-c` 设置配置等。示例展示了创建类库项目并打包的过程。更多详情及命令选项，请参考官方文档。
dotNative
823 12