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

Innodb锁机制：Next-Key Lock 浅谈

2024-01-22 216
版权
版权声明：
本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《 阿里云开发者社区用户服务协议》和 《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写 侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。
简介： Innodb锁机制：Next-Key Lock 浅谈


     数据库使用锁是为了支持更好的并发，提供数据的完整性和一致性。InnoDB是一个支持行锁的存储引擎，锁的类型有：共享锁（S）、排他锁（X）、意向共享（IS）、意向排他（IX）。为了提供更好的并发，InnoDB提供了非锁定读：不需要等待访问行上的锁释放，读取行的一个快照。该方法是通过InnoDB的一个特性：MVCC来实现的。

InnoDB有三种行锁的算法：

1，Record Lock：单个行记录上的锁。

2，Gap Lock：间隙锁，锁定一个范围，但不包括记录本身。GAP锁的目的，是为了防止同一事务的两次当前读，出现幻读的情况。

3，Next-Key Lock：1+2，锁定一个范围，并且锁定记录本身。对于行的查询，都是采用该方法，主要目的是解决幻读的问题。


测试一：默认RR隔离级别

root@localhost : test 10:56:10>create table t(a int,key idx_a(a))engine =innodb;
Query OK, 0 rows affected (0.20 sec)
root@localhost : test 10:56:13>insert into t values(1),(3),(5),(8),(11);
Query OK, 5 rows affected (0.00 sec)
Records: 5  Duplicates: 0  Warnings: 0
root@localhost : test 10:56:15>select * from t;
+------+
| a    |
+------+
|    1 |
|    3 |
|    5 |
|    8 |
|   11 |
+------+
5 rows in set (0.00 sec)
section A:
root@localhost : test 10:56:27>start transaction;
Query OK, 0 rows affected (0.00 sec)
root@localhost : test 10:56:29>select * from t where a = 8 for update;
+------+
| a    |
+------+
|    8 |
+------+
1 row in set (0.00 sec)
section B:
root@localhost : test 10:54:50>begin;
Query OK, 0 rows affected (0.00 sec)
root@localhost : test 10:56:51>select * from t;
+------+
| a    |
+------+
|    1 |
|    3 |
|    5 |
|    8 |
|   11 |
+------+
5 rows in set (0.00 sec)
root@localhost : test 10:56:54>insert into t values(2);
Query OK, 1 row affected (0.00 sec)
root@localhost : test 10:57:01>insert into t values(4);
Query OK, 1 row affected (0.00 sec)
++++++++++
root@localhost : test 10:57:04>insert into t values(6);
root@localhost : test 10:57:11>insert into t values(7);
root@localhost : test 10:57:15>insert into t values(9);
root@localhost : test 10:57:33>insert into t values(10);
++++++++++
上面全被锁住，阻塞住了
root@localhost : test 10:57:39>insert into t values(12);
Query OK, 1 row affected (0.00 sec)

问题：

为什么section B上面的插入语句会出现锁等待的情况？InnoDB是行锁，在section A里面锁住了a=8的行，其他应该不受影响。why？

分析：

因为InnoDB对于行的查询都是采用了Next-Key Lock的算法，锁定的不是单个值，而是一个范围（GAP）。上面索引值有1，3，5，8，11，其记录的GAP的区间如下：是一个左开右闭的空间（原因是默认主键的有序自增的特性，结合后面的例子说明）

（-∞,1]，(1,3]，(3,5]，(5,8]，(8,11]，(11,+∞）

特别需要注意的是，InnoDB存储引擎还会对辅助索引下一个键值加上gap lock。如上面分析，那就可以解释了。

root@localhost : test 10:56:29>select * from t where a = 8 for update;
+------+
| a    |
+------+
|    8 |
+------+
1 row in set (0.00 sec)

该SQL语句锁定的范围是（5,8]，下个下个键值范围是（8,11]，所以插入5~11之间的值的时候都会被锁定，要求等待。即：插入5，6，7，8，9，10 会被锁住。插入非这个范围内的值都正常。

################################### 2016-07-21 更新

因为例子里没有主键，所以要用隐藏的ROWID来代替，数据根据Rowid进行排序。而Rowid是有一定顺序的（自增），所以其中11可以被写入，5不能被写入，不清楚的可以再看一个有主键的例子：

会话1：
01:43:07>create table t(id int,name varchar(10),key idx_id(id),primary key(name))engine =innodb;
Query OK, 0 rows affected (0.02 sec)
01:43:11>insert into t values(1,'a'),(3,'c'),(5,'e'),(8,'g'),(11,'j');                                                                               
Query OK, 5 rows affected (0.01 sec)
Records: 5  Duplicates: 0  Warnings: 0
01:44:03>select @@global.tx_isolation, @@tx_isolation;                                                                                                 +-----------------------+-----------------+
| @@global.tx_isolation | @@tx_isolation  |
+-----------------------+-----------------+
| REPEATABLE-READ       | REPEATABLE-READ |
+-----------------------+-----------------+
1 row in set (0.01 sec)
01:44:58>select * from t;
+------+------+
| id   | name |
+------+------+
|    1 | a    |
|    3 | c    |
|    5 | e    |
|    8 | g    |
|   11 | j    |
+------+------+
5 rows in set (0.00 sec)
01:45:07>start transaction;              
01:45:09>delete from t where id=8;
Query OK, 1 row affected (0.01 sec)
会话2：
01:50:38>select @@global.tx_isolation, @@tx_isolation;
+-----------------------+-----------------+
| @@global.tx_isolation | @@tx_isolation  |
+-----------------------+-----------------+
| REPEATABLE-READ       | REPEATABLE-READ |
+-----------------------+-----------------+
1 row in set (0.01 sec)
01:50:48>start transaction; 
01:50:51>select * from t;
+------+------+
| id   | name |
+------+------+
|    1 | a    |
|    3 | c    |
|    5 | e    |
|    8 | g    |
|   11 | j    |
+------+------+
5 rows in set (0.01 sec)
01:51:35>insert into t(id,name) values(6,'f');
^CCtrl-C -- sending "KILL QUERY 9851" to server ...
Ctrl-C -- query aborted.
ERROR 1317 (70100): Query execution was interrupted
01:53:32>insert into t(id,name) values(5,'e1');
^CCtrl-C -- sending "KILL QUERY 9851" to server ...
Ctrl-C -- query aborted.
ERROR 1317 (70100): Query execution was interrupted
01:53:41>insert into t(id,name) values(7,'h');
^CCtrl-C -- sending "KILL QUERY 9851" to server ...
Ctrl-C -- query aborted.
ERROR 1317 (70100): Query execution was interrupted
01:54:43>insert into t(id,name) values(8,'gg');
^CCtrl-C -- sending "KILL QUERY 9851" to server ...
Ctrl-C -- query aborted.
ERROR 1317 (70100): Query execution was interrupted
01:55:10>insert into t(id,name) values(9,'k');
^CCtrl-C -- sending "KILL QUERY 9851" to server ...
Ctrl-C -- query aborted.
ERROR 1317 (70100): Query execution was interrupted
01:55:23>insert into t(id,name) values(10,'p');
^CCtrl-C -- sending "KILL QUERY 9851" to server ...
Ctrl-C -- query aborted.
ERROR 1317 (70100): Query execution was interrupted
01:55:33>insert into t(id,name) values(11,'iz');
^CCtrl-C -- sending "KILL QUERY 9851" to server ...
Ctrl-C -- query aborted.
ERROR 1317 (70100): Query execution was interrupted
#########上面看到 id：5，6，7，8，9，10，11都被锁了。
#########下面看到 id：5，11 还是可以插入的
01:54:33>insert into t(id,name) values(5,'cz');
Query OK, 1 row affected (0.01 sec)
01:55:59>insert into t(id,name) values(11,'ja');
Query OK, 1 row affected (0.01 sec)

分析：因为会话1已经对id=8的记录加了一个X锁，由于是RR隔离级别，INNODB要防止幻读需要加GAP锁：即id=5（8的左边），id=11（8的右边）之间需要加间隙锁（GAP）。这样[5,e]和[8,g]，[8,g]和[11,j]之间的数据都要被锁。上面测试已经验证了这一点，根据索引的有序性，数据按照主键（name）排序，后面写入的[5,cz]（[5,e]的左边）和[11,ja]（[11,j]的右边）不属于上面的范围从而可以写入。

另外一种情况，把name主键去掉会是怎么样的情况？有兴趣的同学可以测试一下。

##################################################

继续：插入超时失败后，会怎么样？

超时时间的参数：innodb_lock_wait_timeout ，默认是50秒。

超时是否回滚参数：innodb_rollback_on_timeout 默认是OFF。

section A:
root@localhost : test 04:48:51>start transaction;
Query OK, 0 rows affected (0.00 sec)
root@localhost : test 04:48:53>select * from t where a = 8 for update;
+------+
| a    |
+------+
|    8 |
+------+
1 row in set (0.01 sec)
section B:
root@localhost : test 04:49:04>start transaction;
Query OK, 0 rows affected (0.00 sec)
root@localhost : test 04:49:07>insert into t values(12);
Query OK, 1 row affected (0.00 sec)
root@localhost : test 04:49:13>insert into t values(10);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
root@localhost : test 04:50:06>select * from t;
+------+
| a    |
+------+
|    1 |
|    3 |
|    5 |
|    8 |
|   11 |
|   12 |
+------+
6 rows in set (0.00 sec)

经过测试，不会回滚超时引发的异常，当参数innodb_rollback_on_timeout 设置成ON时，则可以回滚，会把插进去的12回滚掉。

默认情况下，InnoDB存储引擎不会回滚超时引发的异常，除死锁外。

既然InnoDB有三种算法，那Record Lock什么时候用？还是用上面的列子，把辅助索引改成唯一属性的索引。

测试二：

root@localhost : test 04:58:49>create table t(a int primary key)engine =innodb;
Query OK, 0 rows affected (0.19 sec)
root@localhost : test 04:59:02>insert into t values(1),(3),(5),(8),(11);
Query OK, 5 rows affected (0.00 sec)
Records: 5  Duplicates: 0  Warnings: 0
root@localhost : test 04:59:10>select * from t;
+----+
| a  |
+----+
|  1 |
|  3 |
|  5 |
|  8 |
| 11 |
+----+
5 rows in set (0.00 sec)
section A:
root@localhost : test 04:59:30>start transaction;
Query OK, 0 rows affected (0.00 sec)
root@localhost : test 04:59:33>select * from t where a = 8 for update;
+---+
| a |
+---+
| 8 |
+---+
1 row in set (0.00 sec)
section B:
root@localhost : test 04:58:41>start transaction;
Query OK, 0 rows affected (0.00 sec)
root@localhost : test 04:59:45>insert into t values(6);
Query OK, 1 row affected (0.00 sec)
root@localhost : test 05:00:05>insert into t values(7);
Query OK, 1 row affected (0.00 sec)
root@localhost : test 05:00:08>insert into t values(9);
Query OK, 1 row affected (0.00 sec)
root@localhost : test 05:00:10>insert into t values(10);
Query OK, 1 row affected (0.00 sec)

问题：

为什么section B上面的插入语句可以正常，和测试一不一样？

分析：

因为InnoDB对于行的查询都是采用了Next-Key Lock的算法，锁定的不是单个值，而是一个范围，按照这个方法是会和第一次测试结果一样。但是，当查询的索引含有唯一属性的时候，Next-Key Lock 会进行优化，将其降级为Record Lock，即仅锁住索引本身，不是范围。

注意：通过主键或则唯一索引来锁定不存在的值，也会产生GAP锁定。即：

会话1：
04:22:38>show create table t\G
*************************** 1. row ***************************
       Table: t
Create Table: CREATE TABLE `t` (
  `id` int(11) NOT NULL,
  `name` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
1 row in set (0.00 sec)
04:22:49>start transaction;
04:23:16>select * from t where id = 15 for update;
Empty set (0.00 sec)
会话2：
04:26:10>insert into t(id,name) values(10,'k');
Query OK, 1 row affected (0.01 sec)
04:26:26>insert into t(id,name) values(12,'k');
^CCtrl-C -- sending "KILL QUERY 9851" to server ...
Ctrl-C -- query aborted.
ERROR 1317 (70100): Query execution was interrupted
04:29:32>insert into t(id,name) values(16,'kxx');
^CCtrl-C -- sending "KILL QUERY 9851" to server ...
Ctrl-C -- query aborted.
ERROR 1317 (70100): Query execution was interrupted
04:29:39>insert into t(id,name) values(160,'kxx');
^CCtrl-C -- sending "KILL QUERY 9851" to server ...
Ctrl-C -- query aborted.
ERROR 1317 (70100): Query execution was interrupted

如何让测试一不阻塞？可以显式的关闭Gap Lock：

1：把事务隔离级别改成：Read Committed，提交读、不可重复读。SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

2：修改参数：innodb_locks_unsafe_for_binlog 设置为1。

总结：

本文只对 Next-Key Lock 做了一些说明测试，关于锁还有很多其他方面的知识，可以查阅相关资料进行学习。


文章标签：
关系型数据库
索引
算法
存储
SQL
关键词：
innodb锁
innodb机制
innodb next-key lock
innodb lock
innodb锁机制
hsfxuebao
目录
相关文章
huisheng_qaq
|
存储 缓存 关系型数据库
【MySQL进阶-08】深入理解innodb存储格式，双写机制，buffer pool底层结构和淘汰策略
【MySQL进阶-08】深入理解innodb存储格式，双写机制，buffer pool底层结构和淘汰策略
huisheng_qaq
631 0
菜菜的后端私房菜
|
6月前
|
存储 人工智能 关系型数据库
10个行锁、死锁案例⭐️24张加锁分析图🚀彻底搞懂Innodb行锁加锁规则！
10个行锁、死锁案例⭐️24张加锁分析图🚀彻底搞懂Innodb行锁加锁规则！
菜菜的后端私房菜
345 6
游客mldfis24krfue
|
3月前
|
监控 关系型数据库 MySQL
在Linux中，mysql的innodb如何定位锁问题？
在Linux中，mysql的innodb如何定位锁问题？
游客mldfis24krfue
104 2
龙大吉
|
3月前
|
SQL 存储 关系型数据库
"MySQL增列必锁表？揭秘InnoDB在线DDL，让你的数据库操作飞一般，性能无忧！"
【8月更文挑战第11天】在数据库领域，MySQL凭借其稳定高效的表现深受开发者喜爱。对于是否会在给数据表添加列时锁表的问题，MySQL的行为受版本、存储引擎等因素影响。从5.6版起，InnoDB支持在线DDL，可在改动表结构时保持表的可访问性，避免长时间锁表。而MyISAM等则需锁表完成操作。例如，在使用InnoDB的表上运行`ALTER TABLE users ADD COLUMN email VARCHAR(255);`时，通常不会完全锁表。虽然在线DDL提高了灵活性，但复杂操作或大表变更仍可能暂时影响性能。因此，进行结构变更前应评估其影响并择机执行。
龙大吉
70 6
hsfxuebao
|
5月前
|
存储 SQL 关系型数据库
【MySQL技术内幕】6.3-InnoDB中的锁
【MySQL技术内幕】6.3-InnoDB中的锁
hsfxuebao
198 57
威哥爱编程
|
4月前
|
SQL 关系型数据库 BI
关于InnoDB行锁和4种锁是怎么实现的？
总的来说，InnoDB的行锁机制通过索引来实现对数据行的精确控制，并通过多种锁类型和兼容性规则来处理并发事务中的冲突。开发者需要注意合理使用索引和优化事务处理，以提高数据库的并发性能和稳定性。
威哥爱编程
31 1
jianz123
|
4月前
|
存储 关系型数据库 MySQL
InnoDB的隔离级别实现机制深度解析18
【7月更文挑战第18天】MySQL 数据库 InnoDB 存储引擎的隔离级别是通过锁和 MVCC 的机制实现的。
jianz123
68 0
hsfxuebao
|
6月前
|
存储 算法 关系型数据库
MySQL相关（八）- innodb行级锁深入剖析
MySQL相关（八）- innodb行级锁深入剖析
hsfxuebao
128 0
hsfxuebao
|
6月前
|
存储 算法 关系型数据库
MySQL相关（七）- innodb 锁的介绍及使用
MySQL相关（七）- innodb 锁的介绍及使用
hsfxuebao
64 0
得过且过的勇者y
|
6月前
|
存储 SQL 关系型数据库
MySQL之深入InnoDB存储引擎——Checkpoint机制
一、引入 由于页的操作首先都是在缓冲池中完成的，那么如果一条DML语句改变了页中的记录，那么此时页就是脏的，即缓冲池中页的版本要比磁盘的新。那么数据库需要将新版本的页刷新到磁盘。倘若每次一个页发生变化就刷新，那么开销会很大，若热点数据集中在某几个页中，那么数据库的性能将变得非常差。 同时如果在缓冲池将新版本的页刷新到磁盘时发生了宕机，那么数据就不能恢复了。为了避免发生数据丢失的问题，当前事务数据库普遍都采用了 Write Ahead Log 策略，即当事务提交时，先写重做日志，再修改页。当由于发生宕机而导致数据丢失时，通过重做日志来完成数据的恢复，从而满足事务的持久性要求。
得过且过的勇者y
122 0