MySQL InnoDB中的锁-插入意向锁(Insert Intention Lock)

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
RDS MySQL Serverless 高可用系列,价值2615元额度,1个月
简介: MySQL InnoDB 插入意向锁 Insert Intention Lock

锁列表

  • 共享与列排他锁
  • 意向锁
  • 记录锁
  • 间隙锁
  • Next-Key锁
  • 插入意向锁
  • AUTO-INC锁

这次我们只来讨论和实验意向锁。

插入意向锁

插入意向锁是一种间隙锁形式的意向锁,在真正执行 INSERT 操作之前设置。
当执行插入操作时,总会检查当前插入操作的下一条记录(已存在的主索引节点)上是否存在锁对象,判断是否锁住了 gap,如果锁住了,则判定和插入意向锁冲突,当前插入操作就需要等待,也就是配合上面的间隙锁或者临键锁一起防止了幻读操作。

因为插入意向锁是一种意向锁,意向锁只是表示一种意向,所以插入意向锁之间不会互相冲突,多个插入操作同时插入同一个 gap 时,无需互相等待,比如当前索引上有记录 4 和 8,两个并发 session 同时插入记录 6,7。他们会分别为(4,8)加上 GAP 锁,但相互之间并不冲突

INSERT 语句在执行插入之前,会先在 gap 中加入插入意向锁,如果是唯一索引,还会进行 Duplicate Key 判断,如果存在相同 Key 且该 Key 被加了互斥锁,则还会加共享锁,然后等待(因为这个相同的 Key 之后有可能会回滚删除,这里非常容易死锁)。等到成功插入后,会在这条记录上加排他记录锁

实验

建表语句:

CREATE TABLE `sys_user` ( `id` int NOT NULL AUTO_INCREMENT,
`name` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '姓名', 
`name_pinyin` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '姓名拼音',
`id_card` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '身份证号',
`phone` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '手机号', 
`age` int(11) NOT NULL DEFAULT 1 COMMENT '年龄',
PRIMARY KEY (`id`), 
UNIQUE KEY `uni_idx_id_card` (`id_card`) USING BTREE COMMENT '唯一索引-身份证号', 
KEY `idx_phone_name` (`phone`,`name`) USING BTREE COMMENT '普通索引-手机号' ) ENGINE=InnoDB AUTO_INCREMENT=3495 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='用户'

AI 代码解读

插入记录

insert into sys_user (id, name, name_pinyin, id_card, phone, age)values (1, '小六', 'xiaoliu', 300000000, 13000008000, 10);

insert into sys_user (id, name, name_pinyin, id_card, phone, age)values (2, '小六', 'xiaoliu', 300000001, 13000008000, 11);

insert into sys_user (id, name, name_pinyin, id_card, phone, age)values (3, '小六', 'xiaoliu', 300000002, 13000008000, 13);

insert into sys_user (id, name, name_pinyin, id_card, phone, age)values (4, '小六', 'xiaoliu', 300000003, 13000008000, 20);
AI 代码解读

记录锁

插入意向锁插入间隙锁区间内(age字段)阻塞等待

时刻 事务A 事务B
1 begin; begin;
2 select * from sys_user where age=13 for update; (在11和20之间添加了间隙锁))
3 insert into sys_user (id, name, name_pinyin, id_card, phone, age)values (5, '小六', 'xiaoliu', 300000004, 13000008000, 12);(阻塞等待)

在事务B阻塞等待时,使用show engine innodb status查看事务(内容删除了一部分):

---TRANSACTION 133643386, ACTIVE 34 sec inserting
insert into sys_user (id, name, name_pinyin, id_card, phone, age)values (5, '小六', 'xiaoliu', 300000004, 13000008000, 19)
------- TRX HAS BEEN WAITING 28 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 484 page no 46 n bits 80 index idx_age of table `employees`.`sys_user` trx id 133643386 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 5
------------------
AI 代码解读

两个插入意向锁插入相同间隙(age字段)正常插入

时刻 事务A 事务B
1 begin; begin;
2 insert into sys_user (id, name, name_pinyin, id_card, phone, age)values (5, '小六', 'xiaoliu', 300000004, 13000008000, 19);
3 insert into sys_user (id, name, name_pinyin, id_card, phone, age)values (6, '小六', 'xiaoliu', 300000005, 13000008000, 18);

两个插入意向锁(age字段)死锁

时刻 事务A 事务B
1 begin; begin;
2 select * from sys_user where age=14 for update; select * from sys_user where age=14 for update;
3 insert into sys_user (id, name, name_pinyin, id_card, phone, age)values (6, '小六', 'xiaoliu', 300000005, 13000008000, 14); (事务A和B插入相同记录)
4 insert into sys_user (id, name, name_pinyin, id_card, phone, age)values (6, '小六', 'xiaoliu', 300000005, 13000008000, 14); (事务A和B插入相同记录)

死锁日志

------------------------
LATEST DETECTED DEADLOCK
------------------------
2022-03-01 15:04:27 140684024923904
*** (1) TRANSACTION:
TRANSACTION 133654731, ACTIVE 29 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 4 lock struct(s), heap size 1136, 3 row lock(s), undo log entries 1
MySQL thread id 4796, OS thread handle 140684279383808, query id 5765383 192.168.1.83 root update
insert into sys_user (id, name, name_pinyin, id_card, phone, age)values (6, '小六', 'xiaoliu', 300000005, 13000008000, 14)

*** (1) HOLDS THE LOCK(S):
RECORD LOCKS space id 484 page no 4 n bits 80 index PRIMARY of table `employees`.`sys_user` trx id 133654731 lock_mode X locks rec but not gap
Record lock, heap no 6 PHYSICAL RECORD: n_fields 8; compact format; info bits 128
 0: len 4; hex 80000006; asc     ;;
 1: len 6; hex 000007f768cb; asc     h ;;
 2: len 7; hex 81000000ac0110; asc        ;;
 3: len 6; hex e5b08fe585ad; asc       ;;
 4: len 7; hex 7869616f6c6975; asc xiaoliu;;
 5: len 9; hex 333030303030303035; asc 300000005;;
 6: len 11; hex 3133303030303038303030; asc 13000008000;;
 7: len 4; hex 8000000e; asc     ;;


*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 484 page no 46 n bits 80 index idx_age of table `employees`.`sys_user` trx id 133654731 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 5

*** (2) TRANSACTION:
TRANSACTION 133654788, ACTIVE 22 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1136, 2 row lock(s)
MySQL thread id 4798, OS thread handle 140684407801600, query id 5765426 192.168.1.83 root update
insert into sys_user (id, name, name_pinyin, id_card, phone, age)values (6, '小六', 'xiaoliu', 300000005, 13000008000, 14)

*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 484 page no 46 n bits 80 index idx_age of table `employees`.`sys_user` trx id 133654788 lock_mode X locks gap before rec
Record lock, heap no 5

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 484 page no 4 n bits 80 index PRIMARY of table `employees`.`sys_user` trx id 133654788 lock mode S locks rec but not gap waiting
Record lock, heap no 6 PHYSICAL RECORD: n_fields 8; compact format; info bits 128
 0: len 4; hex 80000006; asc     ;;
 1: len 6; hex 000007f768cb; asc     h ;;
 2: len 7; hex 81000000ac0110; asc        ;;
 3: len 6; hex e5b08fe585ad; asc       ;;
 4: len 7; hex 7869616f6c6975; asc xiaoliu;;
 5: len 9; hex 333030303030303035; asc 300000005;;
 6: len 11; hex 3133303030303038303030; asc 13000008000;;
 7: len 4; hex 8000000e; asc     ;;
AI 代码解读

资源分配图

个人分析死锁图应该是这样的:
image.png

注意

  1. 死锁回滚时,InnoDB会选择资源最小的那个事务进行回滚。
  2. 不用过度担心死锁,因为死锁事务回滚后再次重试就可以了。

参考

  1. https://dev.mysql.com/doc/refman/8.0/en/innodb-locking.html#innodb-record-locks
  2. https://www.infoq.cn/article/zau0ewzsdtx9zofr6c8w
  3. https://segmentfault.com/a/1190000019745324
相关实践学习
如何快速连接云数据库RDS MySQL
本场景介绍如何通过阿里云数据管理服务DMS快速连接云数据库RDS MySQL,然后进行数据表的CRUD操作。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
目录
相关文章
【MySQL进阶篇】存储引擎(MySQL体系结构、InnoDB、MyISAM、Memory区别及特点、存储引擎的选择方案)
MySQL的存储引擎是其核心组件之一,负责数据的存储、索引和检索。不同的存储引擎具有不同的功能和特性,可以根据业务需求 选择合适的引擎。本文详细介绍了MySQL体系结构、InnoDB、MyISAM、Memory区别及特点、存储引擎的选择方案。
【MySQL进阶篇】存储引擎(MySQL体系结构、InnoDB、MyISAM、Memory区别及特点、存储引擎的选择方案)
MySQL存储引擎详述:InnoDB为何胜出?
MySQL 是最流行的开源关系型数据库之一,其存储引擎设计是其高效灵活的关键。InnoDB 作为默认存储引擎,支持事务、行级锁和外键约束,适用于高并发读写和数据完整性要求高的场景;而 MyISAM 不支持事务,适合读密集且对事务要求不高的应用。根据不同需求选择合适的存储引擎至关重要,官方推荐大多数场景使用 InnoDB。
66 7
Mysql索引:深入理解InnoDb聚集索引与MyisAm非聚集索引
通过本文的介绍,希望您能深入理解InnoDB聚集索引与MyISAM非聚集索引的概念、结构和应用场景,从而在实际工作中灵活运用这些知识,优化数据库性能。
132 7
【赵渝强老师】MySQL InnoDB的数据文件与重做日志文件
本文介绍了MySQL InnoDB存储引擎中的数据文件和重做日志文件。数据文件包括`.ibd`和`ibdata`文件,用于存放InnoDB数据和索引。重做日志文件(redo log)确保数据的可靠性和事务的持久性,其大小和路径可由相关参数配置。文章还提供了视频讲解和示例代码。
167 11
【赵渝强老师】MySQL InnoDB的数据文件与重做日志文件
MySQL引擎InnoDB和MyISAM的区别?
InnoDB是MySQL默认的事务型存储引擎,支持事务、行级锁、MVCC、在线热备份等特性,主索引为聚簇索引,适用于高并发、高可靠性的场景。MyISAM设计简单,支持压缩表、空间索引,但不支持事务和行级锁,适合读多写少、不要求事务的场景。
61 9
【赵渝强老师】MySQL InnoDB的表空间
InnoDB是MySQL默认的存储引擎,主要由存储结构、内存结构和线程结构组成。其存储结构分为逻辑和物理两部分,逻辑存储结构包括表空间、段、区和页。表空间是InnoDB逻辑结构的最高层,所有数据都存放在其中。默认情况下,InnoDB有一个共享表空间ibdata1,用于存放撤销信息、系统事务信息等。启用参数`innodb_file_per_table`后,每张表的数据可以单独存放在一个表空间内,但撤销信息等仍存放在共享表空间中。
【赵渝强老师】MySQL InnoDB的段、区和页
MySQL的InnoDB存储引擎逻辑存储结构与Oracle相似,包括表空间、段、区和页。表空间由段和页组成,段包括数据段、索引段等。区是1MB的连续空间,页是16KB的最小物理存储单位。InnoDB是面向行的存储引擎,每个页最多可存放7992行记录。
数据库传奇:MySQL创世之父的两千金My、Maria
《数据库传奇:MySQL创世之父的两千金My、Maria》介绍了MySQL的发展历程及其分支MariaDB。MySQL由Michael Widenius等人于1994年创建,现归Oracle所有,广泛应用于阿里巴巴、腾讯等企业。2009年,Widenius因担心Oracle收购影响MySQL的开源性,创建了MariaDB,提供额外功能和改进。维基百科、Google等已逐步替换为MariaDB,以确保更好的性能和社区支持。掌握MariaDB作为备用方案,对未来发展至关重要。
45 3
MySQL崩溃保险箱:探秘Redo/Undo日志确保数据库安全无忧!
《MySQL崩溃保险箱:探秘Redo/Undo日志确保数据库安全无忧!》介绍了MySQL中的三种关键日志:二进制日志(Binary Log)、重做日志(Redo Log)和撤销日志(Undo Log)。这些日志确保了数据库的ACID特性,即原子性、一致性、隔离性和持久性。Redo Log记录数据页的物理修改,保证事务持久性;Undo Log记录事务的逆操作,支持回滚和多版本并发控制(MVCC)。文章还详细对比了InnoDB和MyISAM存储引擎在事务支持、锁定机制、并发性等方面的差异,强调了InnoDB在高并发和事务处理中的优势。通过这些机制,MySQL能够在事务执行、崩溃和恢复过程中保持
48 3
数据库灾难应对:MySQL误删除数据的救赎之道,技巧get起来!之binlog
《数据库灾难应对:MySQL误删除数据的救赎之道,技巧get起来!之binlog》介绍了如何利用MySQL的二进制日志(Binlog)恢复误删除的数据。主要内容包括: 1. **启用二进制日志**:在`my.cnf`中配置`log-bin`并重启MySQL服务。 2. **查看二进制日志文件**:使用`SHOW VARIABLES LIKE 'log_%';`和`SHOW MASTER STATUS;`命令获取当前日志文件及位置。 3. **创建数据备份**:确保在恢复前已有备份,以防意外。 4. **导出二进制日志为SQL语句**:使用`mysqlbinlog`
66 2