安科网

关于 MySQL InnoDB锁机制

跳舞的芒果

跳舞的芒果

2017-01-09

关注 关注

一  背景
    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 留作后面继续分析。

innodb mysql锁 索引

跳舞的芒果

跳舞的芒果

0 关注 0 粉丝 0 动态

关注 关注

相关推荐

MyISAM与InnoDB索引实现对比详解

MyISAM引擎使用B+Tree作为索引结构，叶节点的data域存放的是数据记录的地址。这里设表一共有三列，假设我们以Col1为主键，则上图是一个MyISAM表的主索引示意。虽然InnoDB也使用B+Tree作为索引结构，但具体实现方式却与MyISAM截然

chenjiazhu 2020-09-29

MySQL索引的各种类型

索引是数据库存储引擎用于快速查找到指定数据的一种数据结构。可以用新华字典做类比：如果新华字典中对每个字的详细解释是数据库中表的记录，那么按部首或拼音等排序的目录就是索引，使用它可以让我们快速查找的某一个字详细解释的位置。在MySQL中，存储引擎也是用了类似

silencehgt 2020-09-07

关于mysql自增id，你需要知道的

本篇文章将以问答的形式讲述关于自增id的一切。这就要求同一个叶子节点内的各条数据记录按主键顺序存放，因此每当有一条新的记录插入时，MySQL会根据其主键将其插入适当的节点和位置，如果页面达到装载因子，则开辟一个新的页（节点）。自增id是增长的 不一定连续。

抱抱熊 2020-08-15

mysql 14 覆盖索引+回表

MySQL可以利用索引返回select列表中的字段值。而不必根据主键再次读取聚簇索引数据文件查到数据，也就是平时所说的不需要回表操作。覆盖索引其实是索引覆盖的意思，索引字段就已经囊括select查询的字段，即索引字段覆盖了需查询的字段。t; 即id是聚集索

Accpcjg 2020-08-02

四种mysql存储引擎(转)

　　最近项目中使用mysql数据库时出现一个问题，运用spring的事务注解来管理事务时，发现用事务注解@transaction标记的方法里，如果出现异常，事务并没有回滚，而是直接提交到了数据库，刚开始还以为是spring的事务配置有问题，检查一遍后还是不

wenjieyatou 2020-07-30

MySQL索引原理

　　1.B+树的叶子节点包含了所有的数据；　　2.叶子节点可以直接访问其他叶子节点，不需要回到根节点；　　操作系统中，默认的是每一页是4KB；而InnoDB存储引擎，默认每一页是16KB；　　UserRecord（行记录）区域，保存的是真正的数据，会包含多

bluetears 2020-07-05

MySQL索引 索引分类 最左前缀原则 覆盖索引 索引下推 联合索引顺序

索引就是帮助RDBMS高效获取数据的数据结构。索引可以让我们避免一行一行进行全表扫描。普通索引 INDEX或者key index_name 没有任何约束，一张表可以有多个普通索引。唯一索引 UNIQUE INDEX index_name 在不同索引

bendan 2020-07-04

简单了解mysql InnoDB MyISAM相关区别

mysql支持很多表类型的表，如myisam、innodb、memory、archive、example等。每种存储引擎都有自己的优点和缺点，充分的理解每种存储引擎，有助于合理的使用它们。有人认为在同一个数据库中使用多种存储引擎很影响性能，其实这是一种十分

幸福ITman汪文威 2020-09-05

xtrabackup 实现MySQL数据库备份

Xtrabackup实现是 物理备份，而且是物理热备 目前主流的有两个工具可以实现物理热备：ibbackup和xtrabackup；ibbackup是商 业软件，需要授权，非常昂贵。而xtrabackup功能比ibbackup还要强大，但却是开源的。因此我

wanjichun 2020-07-18

mysql的刷脏

还有没使用的；innodb的策略是尽量使用内存，对于一个长时间运行的数据库来说，未被使用的内存页很少。日志写满，需要flush到磁盘，这时候更新全部堵住，写性能跌为 0，这种情况对敏感业务来说，是不能接受的。InnoDB 需要有控制脏页比例的机制，来尽量避

tanrong 2020-07-05

mysql性能监控指标

mysql > show status like ‘Slave_running‘;当前客户端已连接的数量。这个值会少于预设的值，但你也能监视到这个值较大，这可保证客户端是处在活跃状态。这个值在很短的时间内超过限定值是没问题的。当Threads_run

一对儿程序猿 2020-07-04

MySQL高级【三】MySQL 体系结构和存储引擎

最上层是一些客户端和链接服务，包含本地sock 通信和大多数基于客户端/服务端工具实现的类似于 TCP/IP的通信。主要完成一些类似于连接处理、授权认证、及相关的安全方案。在该层上引入了线程池的概念，为通过认证安全接入的客户端提供线程。同样在该层上可以实现

jiangziya 2020-07-05

mysql的日志模块

连接器-------->分析器------->优化器--------->执行器-------->存储引擎 #如下图。一家商店有一个记账板，当赊账顾客多的时候，会临时记录在记账板上，避免频繁去记账本上查找更新对应顾客的信息。避免高峰

timewind 2020-07-04

进入MySQL大门的地方。学习开始，JSP语言都是需要main方法作为主入口

是对MySQL一条insert语句写入到MySQL数据库的分析。但是，对于MySQL 5.7版本来说，基本上都是写入到innodb引擎。但也还是有借鉴意义，大的框架没有太大变化。后面的文档，会通过mysqld --debug 和gdb等工具，通过分析mys

msmysql 2020-06-26

xtrabackup 实现MySQL数据库备份

Xtrabackup实现是 物理备份，而且是物理热备 目前主流的有两个工具可以实现物理热备：ibbackup和xtrabackup；ibbackup是商 业软件，需要授权，非常昂贵。而xtrabackup功能比ibbackup还要强大，但却是开源的。因此我

xiaoxiangyu 2020-06-16

MySQL 之压力测试工具的使用方法

mysqlslap是mysql自带的基准测试工具,该工具查询数据,语法简单,灵活容易使用.该工具可以模拟多个客户端同时并发的向服务器发出查询更新,给出了性能测试数据而且提供了多种引擎的性能比较。mysqlslap为mysql性能优化前后提供了直观的验证依据

老道长的测试生活 2020-06-14

【转】为什么 MySQL 使用 B+ 树

微信公众号，五分钟学算法，2020-06-14文章。为什么 MySQL 使用 B+ 树是面试中经常会出现的问题，很多人对于这个问题可能都有一些自己的理解，但是多数的回答都不够完整和准确，大多数人都只会简单说一下 B+ 树和 B 树的区别，但是都没有真正回答

tangjianft 2020-06-14

06-表的操作

现实生活中我们用来存储数据的文件有不同的类型，每种文件类型对应各自不同的处理机制：比如处理文本用txt类型，处理表格用excel，处理图片用png等。数据库中的表也应该有不同的类型，表的类型不同，会对应mysql不同的存取机制，表类型又称为存储引擎。而My

Kele0 2020-06-13

mysql InnoDB和MyISAM的区别【转】

mysql支持很多表类型的表，如myisam、innodb、memory、archive、example等。每种存储引擎都有自己的优点和缺点，充分的理解每种存储引擎，有助于合理的使用它们。有人认为在同一个数据库中使用多种存储引擎很影响性能，其实这是一种十分

xwb 2020-06-13

mysql中的存储引擎

什么是存储引擎？MySQL中的数据用各种不同的技术存储在文件中。这些技术中的每一种技术都使用不同的存储机制、索引技巧、锁定水平并且最终提供广泛的不同的功能和能力。例如，如果你在研究大量的临时数据，你也许需要使用内存存储引擎。这些不同的技术以及配套的相关功能

Kele0 2020-06-13
跳舞的芒果

跳舞的芒果

W3CSchool教程
HTML 教程
CSS 教程
Bootstrap 教程
Javascript 教程
jQuery 教程
后端教程
C 教程
Java 教程
PHP 教程
Python 教程
Go 教程
移动开发
Android 教程
Swift 教程
Kotlin 教程
jQuery Mobile 教程
ionic 教程
关于我们
新闻动态
联系方式
招聘英才
安科实验室
帮助与反馈

安科网(Ancii)，中国第一极客网

Copyright © 2013 - 2019 Ancii.com

京ICP备18063983号 京公网安备11010802014868号