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

【MySQL】InnoDB锁机制之一

2016-12-17 1304
版权
版权声明：
本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《 阿里云开发者社区用户服务协议》和 《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写 侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。
本文涉及的产品
RDS MySQL Serverless 基础系列，0.5-2RCU 50GB
推荐场景：
学生管理系统数据库设计 搭建个人博客
云数据库 RDS MySQL，集群系列 2核4GB
推荐场景：
搭建个人博客
云数据库 RDS PostgreSQL，集群系列 2核4GB
简介： 一  背景     MySQL锁机制是一个极其复杂的实现，为数据库并发访问和数据一致提供保障。这里仅仅针对MySQL访问数据的三种锁做介绍，加深自己对锁方面的掌握。二 常见的锁机制 我们知道对于InnoDB存储引擎而言,MySQL 的行锁机制是通过在索引上加锁来锁定要目标数据行的。
一  背景
    MySQL锁机制是一个极其复杂的实现，为数据库并发访问和数据一致提供保障。这里仅仅针对MySQL访问数据的三种锁做介绍，加深自己对锁方面的掌握。
二 常见的锁机制
我们知道对于InnoDB存储引擎而言,MySQL 的行锁机制是通过在索引上加锁来锁定要目标数据行的。常见的有如下三种锁类型，本文未声明情况下都是在RR 事务隔离级别下的描述。
2.1 Record Locks 
  记录锁实际上是索引上的锁，锁定具体的一行或者多行记录。当表上没有创建索引时，InnoDB会创建一个隐含的聚族索引，并且使用该索引锁定数据。通常我们可以使用 show innodb status 看到行锁相关的信息。
2.2  Gap Locks
 间隙锁是锁定具体的范围，但是不包含行锁本身。比如
  1. select * from tab where id>10 and id<20；
RR事务隔离级别下会锁定10-20之间的记录，不允许类似15这样的值插入到表里，以便消除“幻读”带来的影响。间隙锁的跨度可以是1条记录(Record low就可以认为是一个特殊的间隙锁 ，多行，或者为空。当访问的字段是唯一键/主键时，间隙锁会降级为Record lock。RR事务隔离级别下访问一个空行 ，也会有间隙锁，后续会举例子说明。
我们可以通过将事务隔离级别调整为RC 模式或者设置innodb_locks_unsafe_for_binlog=1 (该参数已经废弃)来禁用Gap锁。

2.3 Next-Key Locks
  是Record Lock+Gap Locks,锁定一个范围并且包含索引本身。例如索引值包含 2，4，9，14 四个值，其gap锁的区间如下：
  （-∞,2]，(2,4]，(4,9]，(9,14]，(14,+∞）
本文着重从主键,唯一键、非唯一索引,不存在值访问四个方面来阐述RR模式下锁的表现。
三 测试案例
3.1 主键/唯一键 
  1. CREATE TABLE `lck_primarkey` (
  2.   `id` int(11) NOT NULL,
  3.    val int(11) not null default 0,
  4.   primary key (`id`),
  5.   key idx_val(val)
  6. ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  7. insert into lck_primarkey values(2,3),(4,5),(9,8),(14,13)
会话1 
  1. [session1] >select * from lck_primarkey;
  2. +----+-----+
  3. | id | val |
  4. +----+-----+
  5. | 2 | 3 |
  6. | 4 | 5 |
  7. | 9 | 8 |
  8. | 14 | 13 |
  9. +----+-----+
  10. 4 rows in set (0.00 sec)
  11. [session1] >begin;
  12. Query OK, 0 rows affected (0.00 sec)
  13. [session1] >select * from lck_primarkey where id=9 for update;
  14. +----+-----+
  15. | id | val |
  16. +----+-----+
  17. | 9 | 8 |
  18. +----+-----+
  19. 1 row in set (0.00 sec)
会话2 
  1. [session2] >begin;
  2. Query OK, 0 rows affected (0.00 sec)
  3. [session2] >insert into lck_primarkey values(7,6);
  4. Query OK, 1 row affected (0.00 sec)
  5. [session2] >insert into lck_primarkey values(5,5);
  6. Query OK, 1 row affected (0.00 sec)
  7. [session2] >insert into lck_primarkey values(13,13);
  8. Query OK, 1 row affected (0.00 sec)
  9. [session2] >insert into lck_primarkey values(10,9);
  10. Query OK, 1 row affected (0.00 sec)
分析
   从例子看，当访问表的where字段是主键或者唯一键的时候，session2中的插入操作并未被 session1 中的id=8 影响。官方表述
  1. “Gap locking is not needed for statements that lock rows using a unique index to search for a unique row. (This does not include the case that the search condition includes only some columns of a multiple-column unique index; in that case, gap locking does occur.) For example, if the id column has a unique index, the following statement uses only an index-record lock for the row having id value 100 and it does not matter whether other sessions insert rows in the preceding gap:
  2.    select * from tab where id=100 for update”
  3. 就是说当语句通过主键或者唯一键访问数据的时候，Innodb会使用Record lock锁住记录本身，而不是使用间隙锁锁定范围。
需要注意以下两种情况:
1 通过主键或则唯一索引访问不存在的值，也会产生GAP锁。
  1. [session1] >begin;
  2. Query OK, 0 rows affected (0.00 sec)
  3. [session1] >select * from lck_primarkey where id=7 for update;
  4. Empty set (0.00 sec)
  5. [session2] >insert into lck_primarkey values(8,13);
  6. ^CCtrl-C -- sending "KILL QUERY 303042481" to server ...
  7. Ctrl-C -- query aborted.
  8. ERROR 1317 (70100): Query execution was interrupted
  9. [session2] >insert into lck_primarkey values(5,13);
  10. ^CCtrl-C -- sending "KILL QUERY 303042481" to server ...
  11. Ctrl-C -- query aborted.
  12. ERROR 1317 (70100): Query execution was interrupted
  13. [session2] >insert into lck_primarkey values(3,13);
  14. Query OK, 1 row affected (0.00 sec)
  15. [session2] >insert into lck_primarkey values(10,13);
  16. Query OK, 1 row affected (0.00 sec)
2 通过唯一索引中的一部分字段来访问数据，比如unique key(a,b,c) ，select * from tab where a=x and b=y; 读者朋友可以自己做这个例子。

3.2 非唯一键

  1. CREATE TABLE `lck_secondkey` (
  2.   `id` int(11) NOT NULL,
  3.    KEY `idx_id` (`id`)
  4. ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  5. insert into lck_secondkey values(2),(4),(9),(14)
会话1
  1. [session1] >begin ;
  2. Query OK, 0 rows affected (0.00 sec)
  3. [session1] >select * from lck_secondkey;
  4. +----+
  5. | id |
  6. +----+
  7. | 2 |
  8. | 3 |
  9. | 4 |
  10. | 9 |
  11. | 14 |
  12. +----+
  13. 5 rows in set (0.00 sec)
  14. [session1] >select * from lck_secondkey where id=9 for update;
  15. +----+
  16. | id |
  17. +----+
  18. | 9 |
  19. +----+
  20. 1 row in set (0.00 sec)
会话2

  1. [session2] >begin;
  2. Query OK, 0 rows affected (0.00 sec)
  3. [session2] >insert into lck_secondkey values(3);
  4. Query OK, 1 row affected (0.00 sec)
  5. [session2] >insert into lck_secondkey values(4);
  6. ^CCtrl-C -- sending "KILL QUERY 303040567" to server ...
  7. Ctrl-C -- query aborted.
  8. ERROR 1317 (70100): Query execution was interrupted
  9. [session2] >insert into lck_secondkey values(5);
  10. ^CCtrl-C -- sending "KILL QUERY 303040567" to server ...
  11. Ctrl-C -- query aborted.
  12. ERROR 1317 (70100): Query execution was interrupted
  13. [session2] >insert into lck_secondkey values(6);
  14. ^CCtrl-C -- sending "KILL QUERY 303040567" to server ...
  15. Ctrl-C -- query aborted.
  16. ERROR 1317 (70100): Query execution was interrupted
  17. [session2] >insert into lck_secondkey values(7);
  18. ^CCtrl-C -- sending "KILL QUERY 303040567" to server ...
  19. Ctrl-C -- query aborted.
  20. ERROR 1317 (70100): Query execution was interrupted
  21. [session2] >insert into lck_secondkey values(8);
  22. ^CCtrl-C -- sending "KILL QUERY 303040567" to server ...
  23. Ctrl-C -- query aborted.
  24. ERROR 1317 (70100): Query execution was interrupted
  25. [session2] >insert into lck_secondkey values(9);
  26. ^CCtrl-C -- sending "KILL QUERY 303040567" to server ...
  27. Ctrl-C -- query aborted.
  28. ERROR 1317 (70100): Query execution was interrupted
  29. [session2] >insert into lck_secondkey values(10);
  30. ^CCtrl-C -- sending "KILL QUERY 303040567" to server ...
  31. Ctrl-C -- query aborted.
  32. ERROR 1317 (70100): Query execution was interrupted
  33. [session2] >insert into lck_secondkey values(11);
  34. ^CCtrl-C -- sending "KILL QUERY 303040567" to server ...
  35. Ctrl-C -- query aborted.
  36. ERROR 1317 (70100): Query execution was interrupted
  37. [session2] >insert into lck_secondkey values(12);
  38. ^CCtrl-C -- sending "KILL QUERY 303040567" to server ...
  39. Ctrl-C -- query aborted.
  40. ERROR 1317 (70100): Query execution was interrupted
  41. [session2] >insert into lck_secondkey values(13);
  42. ^CCtrl-C -- sending "KILL QUERY 303040567" to server ...
  43. Ctrl-C -- query aborted.
  44. ERROR 1317 (70100): Query execution was interrupted
  45. [session2] >insert into lck_secondkey values(14);
  46. Query OK, 1 row affected (0.00 sec)
分析
  事务1 对id=9进行for update 访问,session2 插入[4,13]的值都是失败的。根据MySQL的锁原理，Innodb 范围索引或者表是通过Next-key locks 算法，RR事务隔离级别下，通过非唯一索引访问数据行并不是锁定唯一的行，而是一个范围。从例子上可以看出来MySQL对 [4,9] 和(9,14]之间的记录加上了锁，防止其他事务对4-14范围中的值进行修改。可能有读者对其中 id=4 不能修改，但是id=14的值去可以插入有疑问？可以看接下来的例子
  1. [session1] >select * from lck_primarkey;
  2. +----+-----+
  3. | id | val |
  4. +----+-----+
  5. | 2 | 3 |
  6. | 4 | 5 |
  7. | 9 | 8 |
  8. | 14 | 13 |
  9. +----+-----+
  10. 4 rows in set (0.00 sec)
  11. [session1] >begin;
  12. Query OK, 0 rows affected (0.00 sec)
  13. [session1] >select * from lck_primarkey where val=8 for update;
  14. +----+-----+
  15. | id | val |
  16. +----+-----+
  17. | 9 | 8 |
  18. +----+-----+
  19. 1 row in set (0.00 sec)
会话2
  1. [session2] >begin;
  2. Query OK, 0 rows affected (0.00 sec)
  3. [session2] >insert into lck_primarkey values(3,5);
  4. Query OK, 1 row affected (0.00 sec)
  5. [session2] >insert into lck_primarkey values(15,13);
  6. Query OK, 1 row affected (0.00 sec)
  7. [session2] >select * from lck_primarkey;
  8. +----+-----+
  9. | id | val |
  10. +----+-----+
  11. | 2 | 3 |
  12. | 3 | 5 |
  13. | 4 | 5 |
  14. | 9 | 8 |
  15. | 14 | 13 |
  16. | 15 | 13 |
  17. +----+-----+
  18. 6 rows in set (0.00 sec)
  19. [session2] >insert into lck_primarkey values(16,12);
  20. ^CCtrl-C -- sending "KILL QUERY 303040567" to server ...
  21. Ctrl-C -- query aborted.
  22. ERROR 1317 (70100): Query execution was interrupted
  23. [session2] >insert into lck_primarkey values(16,6);
  24. ^CCtrl-C -- sending "KILL QUERY 303040567" to server ...
  25. Ctrl-C -- query aborted.
  26. ERROR 1317 (70100): Query execution was interrupted
  27. [session2] >insert into lck_primarkey values(16,5);
  28. ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
  29. [session2] >
  30. [session2] >insert into lck_primarkey values(1,5);
  31. Query OK, 1 row affected (0.00 sec)
分析
   因为session1 对非唯一键val=8 加上了gap锁 [4,5] -[14,13],非此区间的记录都可以插入表中。记录(1,5),(15,13)不在此gap锁区间，记录(16,12),(16,6),(16,5)中的val值在被锁的范围内，故不能插入。
四  总结
   写本文的目的主要是在于温故而知新，侧重于温故。本文着重介绍了三种锁，其实还有两种锁Insert Intention Locks和AUTO-INC Locks 留作后面继续分析。
五 推荐资料
1 官方资料 
2 Innodb锁机制：Next-Key Lock 浅谈 
3 MySQL 加锁处理分析 
文章标签：
云数据库 RDS MySQL 版
关系型数据库
索引
MySQL
算法
数据库
存储
关键词：
云数据库 RDS MySQL 版innodb
mysql innodb
云数据库 RDS MySQL 版机制
云数据库 RDS MySQL 版锁
innodb锁
相关实践学习
如何快速连接云数据库RDS MySQL
本场景介绍如何通过阿里云数据管理服务DMS快速连接云数据库RDS MySQL，然后进行数据表的CRUD操作。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心，利用清洁计算为万物互联的新世界提供源源不断的能源动力，目前开服的区域包括中国（华北、华东、华南、香港）、新加坡、美国（美东、美西）、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程，来了解阿里云能够为你的业务带来哪些帮助 &nbsp; &nbsp; 相关的阿里云产品：云服务器ECS 云服务器 ECS（Elastic Compute Service）是一种弹性可伸缩的计算服务，助您降低 IT 成本，提升运维效率，使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
yangyi_dba
目录
相关文章
蓝染-惣右介
|
4天前
|
存储 缓存 关系型数据库
【MySQL进阶篇】存储引擎（MySQL体系结构、InnoDB、MyISAM、Memory区别及特点、存储引擎的选择方案）
MySQL的存储引擎是其核心组件之一，负责数据的存储、索引和检索。不同的存储引擎具有不同的功能和特性，可以根据业务需求 选择合适的引擎。本文详细介绍了MySQL体系结构、InnoDB、MyISAM、Memory区别及特点、存储引擎的选择方案。
蓝染-惣右介
56 1
【MySQL进阶篇】存储引擎（MySQL体系结构、InnoDB、MyISAM、Memory区别及特点、存储引擎的选择方案）
栈江湖
|
9天前
|
存储 关系型数据库 MySQL
MySQL存储引擎详述：InnoDB为何胜出？
MySQL 是最流行的开源关系型数据库之一，其存储引擎设计是其高效灵活的关键。InnoDB 作为默认存储引擎，支持事务、行级锁和外键约束，适用于高并发读写和数据完整性要求高的场景；而 MyISAM 不支持事务，适合读密集且对事务要求不高的应用。根据不同需求选择合适的存储引擎至关重要，官方推荐大多数场景使用 InnoDB。
栈江湖
51 7
蓝易云
|
18天前
|
存储 关系型数据库 MySQL
Mysql索引：深入理解InnoDb聚集索引与MyisAm非聚集索引
通过本文的介绍，希望您能深入理解InnoDB聚集索引与MyISAM非聚集索引的概念、结构和应用场景，从而在实际工作中灵活运用这些知识，优化数据库性能。
蓝易云
88 7
栈江湖
|
6天前
|
存储 Oracle 关系型数据库
数据库传奇：MySQL创世之父的两千金My、Maria
《数据库传奇：MySQL创世之父的两千金My、Maria》介绍了MySQL的发展历程及其分支MariaDB。MySQL由Michael Widenius等人于1994年创建，现归Oracle所有，广泛应用于阿里巴巴、腾讯等企业。2009年，Widenius因担心Oracle收购影响MySQL的开源性，创建了MariaDB，提供额外功能和改进。维基百科、Google等已逐步替换为MariaDB，以确保更好的性能和社区支持。掌握MariaDB作为备用方案，对未来发展至关重要。
栈江湖
24 3
栈江湖
|
6天前
|
安全 关系型数据库 MySQL
MySQL崩溃保险箱：探秘Redo/Undo日志确保数据库安全无忧！
《MySQL崩溃保险箱：探秘Redo/Undo日志确保数据库安全无忧！》介绍了MySQL中的三种关键日志：二进制日志（Binary Log）、重做日志（Redo Log）和撤销日志（Undo Log）。这些日志确保了数据库的ACID特性，即原子性、一致性、隔离性和持久性。Redo Log记录数据页的物理修改，保证事务持久性；Undo Log记录事务的逆操作，支持回滚和多版本并发控制（MVCC）。文章还详细对比了InnoDB和MyISAM存储引擎在事务支持、锁定机制、并发性等方面的差异，强调了InnoDB在高并发和事务处理中的优势。通过这些机制，MySQL能够在事务执行、崩溃和恢复过程中保持
栈江湖
29 3
栈江湖
|
6天前
|
SQL 关系型数据库 MySQL
数据库灾难应对：MySQL误删除数据的救赎之道，技巧get起来！之binlog
《数据库灾难应对：MySQL误删除数据的救赎之道，技巧get起来！之binlog》介绍了如何利用MySQL的二进制日志（Binlog）恢复误删除的数据。主要内容包括： 1. **启用二进制日志**：在`my.cnf`中配置`log-bin`并重启MySQL服务。 2. **查看二进制日志文件**：使用`SHOW VARIABLES LIKE &#39;log_%&#39;;`和`SHOW MASTER STATUS;`命令获取当前日志文件及位置。 3. **创建数据备份**：确保在恢复前已有备份，以防意外。 4. **导出二进制日志为SQL语句**：使用`mysqlbinlog`
栈江湖
35 2
AIweker
|
20天前
|
关系型数据库 MySQL 数据库
Python处理数据库：MySQL与SQLite详解 | python小知识
本文详细介绍了如何使用Python操作MySQL和SQLite数据库，包括安装必要的库、连接数据库、执行增删改查等基本操作，适合初学者快速上手。
AIweker
140 15
1436047922066202
|
13天前
|
SQL 关系型数据库 MySQL
数据库数据恢复—Mysql数据库表记录丢失的数据恢复方案
Mysql数据库故障： Mysql数据库表记录丢失。 Mysql数据库故障表现： 1、Mysql数据库表中无任何数据或只有部分数据。 2、客户端无法查询到完整的信息。
1436047922066202
44 5
1436047922066202
|
20天前
|
关系型数据库 MySQL 数据库
数据库数据恢复—MYSQL数据库文件损坏的数据恢复案例
mysql数据库文件ibdata1、MYI、MYD损坏。 故障表现：1、数据库无法进行查询等操作；2、使用mysqlcheck和myisamchk无法修复数据库。
1436047922066202
75 6
Apache_Tomcat_Nignx
|
24天前
|
SQL 关系型数据库 MySQL
MySQL导入.sql文件后数据库乱码问题
本文分析了导入.sql文件后数据库备注出现乱码的原因，包括字符集不匹配、备注内容编码问题及MySQL版本或配置问题，并提供了详细的解决步骤，如检查和统一字符集设置、修改客户端连接方式、检查MySQL配置等，确保导入过程顺利。
Apache_Tomcat_Nignx
90 3

热门文章

最新文章

  • 1
    MySQL数据库下载安装教程（Windows&Linux）
  • 2
    MySQL常用函数：IF、SUM等用法
  • 3
    Python处理数据库：MySQL与SQLite详解 | python小知识
  • 4
    从新手到高手：彻底掌握MySQL表死锁
  • 5
    MySQL事务隔离级别及默认隔离级别的设置
  • 6
    Liunx部署java项目Tomcat、Redis、Mysql教程
  • 7
    MySQL导入.sql文件后数据库乱码问题
  • 8
    MySQL的插件式认证到底是干什么的？
  • 9
    数据库数据恢复—MYSQL数据库文件损坏的数据恢复案例
  • 10
    Servlet+MySQL增删改查 原文出自[易百教程] 转载请保留原文链接: https://www.yiibai.com/geek/1391
  • 1
    10个行锁、死锁案例⭐️24张加锁分析图🚀彻底搞懂Innodb行锁加锁规则！
    384
  • 2
    MySQL高可用解决方案演进：从主从复制到InnoDB Cluster架构
    346
  • 3
    mysql中MyISAM和InnoDB的区别是什么
    59
  • 4
    【MySQL系列笔记】InnoDB引擎-数据存储结构
    454
  • 5
    【MySQL】InnoDB 什么情况下会产生死锁
    228
  • 6
    详解InnoDB(2)——日志
    126
  • 7
    详解InnoDB(1)——事务
    95
  • 8
    InnoDB中的索引方案
    68
  • 9
    MySQL InnoDB引擎（下）
    60
  • 10
    MySQL InnoDB引擎（上）
    63

    • 相关课程

    更多
  • MySQL企业常见架构与调优经验分享
  • 云数据库MySQL版快速上手教程
  • 阿里云云原生数据仓库AnalyticDB MySQL版 使用教程
  • MySQL实战进阶
  • 数据库及SQL/MySQL基础
  • 云数据库MySQL快速入门

    • 相关电子书

    更多
  • 搭建电商项目架构连接MySQL
  • 搭建4层电商项目架构，实战连接MySQL
  • PolarDB MySQL引擎重磅功能及产品能力盛大发布

    • 相关实验场景

    更多
  • MySQL基础-学生管理系统数据库设计
  • 如何快速连接云数据库RDS MySQL
  • MySQL数据库快速部署实践
  • MySQL引擎及架构优化
  • 基于EBS部署高性能的MySQL服务
  • 使用CloudLens采集RDS日志并进行审计分析

    • 推荐镜像

    更多
  • mysql
  • mariadb
  • postgresql
    • 下一篇
    手把手教你白嫖阿里云服务器(免费领服务器)