InnoDB这个将近20年的"bug"修复了

0. 背景信息

1. MySQL 8.0.18 以前是怎么加锁的

2. MySQL 8.0.18 之后终于变天了

0. 背景信息

最近在课程中讲到InnoDB行锁时,讲到一个知识点

Innmysql数据库基础知识oDB行锁规则上,有这样的一个原则:

对有唯一属性的索引(主键/唯一索引)进行范围条件加锁时,

向右遍历(假设是普通正序索引,而且不加ORDER BY … D软件开发工具ESC约束)过程中,

会一直扫描并加next-key锁到第一个不满足条件的记录为止,

但如果是RC级别,这个next-key lock会退化成gap lock,而RR下不会关系型数据库和非关系型区别退化。

简言之,就是 "锁会被扩大化",从InnoDB引擎诞生以来一直都是如此。

其实严格来说,这个算是问题或缺陷,甚至也可以认为是bu

1. MySQL 8.0.18 以前是怎么加锁的

我们开发工具看看下面的案例。

首先,确认版本、隔离级别、表结构、索引以及数据。

建议:在PC端阅读本文体验更好。生麒麟贵子的女人特征

# 5.6版本
[root@yejr.run]> select version();
+------------+
| version()  |
+------------+
| 5.6.39-log |
+------------+
#隔离级别
[root@yejr.run]> select @@session.tx_isolation;
+------------------------+
| @@session.tx_isolation |
+------------------------+
| REPEATABLE-READ        |
+------------------------+
#表数据
[root@yejr.run]> select * from t1;
+----+----+----+----+
| c1 | c2 | c3 | c4 |
+----+----+----+----+
|  0 |  0 |  0 |  0 |
|  1 |  1 |  1 |  0 |
|  3 |  3 |  3 |  0 |
|  4 |  2 |  2 |  0 |
+----+----+----+----+
#表结构&索引,c1是主键(有唯一属性),c2是辅助索引
[root@yejr.run]> show create table t1\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `c1` int(10) unsigned NOT NULL DEFAULT '0',
  `c2` int(10) unsigned NOT NULL DEFAULT '0',
  `c3` int(10) unsigned NOT NULL DEFAULT '0',
  `c4` int(10) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`c1`),
  KEY `c2` (`c2`)
) ENGINE=InnoDB;

下面的两个案例中,session2的请求会被阻塞

时间点 session1 sessioin2
T1 begin; begin;
T2 select * fgiticomfort是什么轮胎rom t1 where c1<=1 for update;
T3 delete from t1 where c1=3;

被阻塞

时间点 session1 sessioin2
T1 begin; begin;
T2 delete from t1 where c1=3;
T3 select * from t1 where c1<=1 for update;

一样会孙侨潞被阻塞

原因在于 select * fr开发工具控件怎么用om t1 where c1<=1 for update 这个SQL中,除了对 c1<=1 的所有mysql数据库记录加上 LOCK_X|LOCK_ORDINA神祇领主时代RY(排他的next-key lock)之外,还需要对 c1=3 这条记录也加同样的锁

查看 information_schema 下的两个视图 innodb_locksinnodb_lock_waits 可以确认:

[root@yejr.run]> select * from INNODB_LOCKs\G

1. row **
lock_id: 2849:26:3:4 --请求索引的优缺点的锁索引的优缺点
lock_trx_id: 2849 --被阻塞的事务
lock_mode: X --拍他锁孙侨潞
lock_索引符号type: RECORD --锁类型是 LOCK_OR安卓开发工具DINASQLRY(即githubnext-lock)
lock_table: `test`.`t1`
lock_index: PRIMARY
lock_space: 26
lock_page: 3
loc神祇领主时代k_rec: 4
lo关系型数据库的特点ck_data: 3
2. row **
lock_id: 2848:26:3:4 --持有的锁
lock_trx_id: 2848 --持有锁的事务
lock_mode: X --排他锁 LOCK_X
lock_type: RECORD --锁类型是 LOCK_ORDINARY(即next-lock)
lock_table: `test`.`t1` --表
lock_index: PRIMARY --索引
lock苏卿陆容渊_space: 26 --table space id
lock_page: 3 --page no
lock_rec: 4 --heap no
lock_data: 3 --被加锁的row data,即c1=3这条记录
2 rows in set (0.00 sec)

[root@yejr.run]> select * from INNODB_LOCK_waits\G
1. row *索引的优缺点*
requesting_trx_id: 2849 --请求锁的事务(被阻塞状态)
reque索引的优缺点sted_mysql基础命令lock_id: 2849:26:3:4 --请求的锁
blmysql怎么读ocking_trx_id: 2848 --持有锁的事务
blockin微信开发工具g_lock_id: 2848:26:3:4 --持有的锁

当然了,也可以从 show engine innodb status\G 的结果中确认,这里不赘述。

2. MySQL 8.0.18 之后终于变天了

这个存在了将近20年的"bug",终于在关系型数据库的基本运算包含2019.1mysql密码忘记了怎么办0.1sqlserver4发布的MySQL 8.0.18版本中被解决(修复)了,当时我居然没注意到这个release note。

InnoDB: An unnecessary next key lock was taken when performing 
a SELECT...FOR [SHARE|UPDATE] query with a WHERE condition that
specifies a range, causing one too many rows to be locked. The
most common occurrences of this issue have been addressed so
that only rows and gaps that intersect the searched range are
locked. (Bug #29508068)

简言之:就是不再需要对不必要的数据上锁啦。

再看看上面几个案例在最新的MySQL 8.0.19版本下的表现。

时间点 sessmysql索引ion1 sessioin2
T1 begin; begin;
T2 ssqlserverelect开发工具选项卡在哪里 * from t1 wher索引是什么意思e c1<=1 for update;
T3 delete from t1 where c1=3;

不再被阻塞

看下加锁详情

select ENGINE_LOCK_ID,ENGINE_TRANSACTION_ID,THREAD_ID,OBJECT_NAME,INDEX_NAME,LOCK_TYPE,LOCK_MODE,LOCK_STATUS,LOCK_DATA from data_locks;
+-----------------------------------+-----------------------+-----------+-------------+------------+-----------+---------------+-------------+-----------+
| ENGINE_LOCK_ID | ENGINE_TRANSACTION_ID | THREAD_ID | OBJECT_NAME | INDEX_NAME | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA |
+-----------------------------------+-----------------------+-----------+-------------+------------+-----------+---------------+-------------+-----------+
| 4868124032:1127:140327172372248 | 18983 | 351 | t1 | NULL | TABLE | IX | GRANTED | NULL |
| 4868124032:44:4:4:140327176578584 | 18983 | 351 | t1 | PRIMARY | RECORD | X,REC_NOT_GAP | GRANTED | 3 |
| 4868123176:1127:140327172370216 | 18982 | 350 | t1 | NULL | TABLE | IX | GRANTED | NULL |
| 4868123176:44:4:2:140327176573976 | 18982 | 350 | t1 | PRIMARY | RECORD | X | GRANTED | 0 |
| 4868123176:44:4:3:140327176573976 | 18982 | 350 | t1 | PRIMARY | RECORD | X | GRANTED | 1 |
+-----------------------------------+-----------------------+-----------+-------------+------------+-----------+---------------+-------------+-----------+

可以看到,select * from t1 where c1<=1 for update; 这个SQL只会对 c1=[0,1] 两条记录加上mysql面试题 LOCK_X|LOCK_ORDINARY 锁,不会再对 cgitee1=3 加锁了。

这个有年头的"bug"终于被搞定了,可喜可贺。

最后,来看下关于这个"bug"的描述。当然了,公开的bugitg系统看不到,需要用MOS账号才可以。下面是从代码git log里github的部分摘抄:

commit d1b0afd75ee669f54b70794eb6dab6c121f1f179
Author: Jakub opuszaski <jakub.lopuszanski@oracle.com>
Date: Wed Jul 17 16:34:01 2019 +0200

Bug #29508068 UNNECESSARY NEXT-KEY LOCK TAKEN

When doing a SELECT...FOR [SHARE|UPDATE] with a WHERE condition specifying a range,
we were locking "one row too much".
This patch fixes locking behaviour in several (hopefuly) most common cases, so that
we only lock rows and gaps which intersect the searched range.

- Added MTR to demonstrate current locking policy for end of range
- Got rid of goto
- Extracted logic of determining relation between range and row to separate function
- Extracted reoccuring patterns of modifications of search_tuple so it is easier to add same for stop_tuple
- Added prebuilt->m_stop_tuple and made sure it is in sync with prebuilt->m_mysql_handler->end_range for during read_range_first() and read_range_next()
- Added row_can_be_in_range field
- Do not lock the row (just the gap) if the row is same length and after the stop_tuple
- Do not lock the row (just the gap) if the row is same length and equal to stop_tuple and strict inequality was used for end of range
- Do not lock the row (just the gap) if the row is longer than stop_tuple and its prefix is after the stop_tuple
- Do not lock the row (just the gap) if the row is longer than stop_tuple and its prefix is equal to stop_tuple and strict inequality was used for end of range
- Do not lock the row nor gap if we already saw a row same length and equal to stop_tuple in previous iteration

Reviewed-by: Pawel Olchawa <pawel.olchawa@oracle.com>
RB:22293

所以,还是赶紧升级到MySQL 8.0的最新版本吧,不光功能更强,连锁也进一步优化了。

写到这里,不禁想嘚瑟一下,加入我的「MySQL优化课」课程git命令优势就体现出来了,一旦有重大的知识gitee更新,总是能比别人先一步知道,图便mysql安装配置教程宜买一些万年不索引更新的旧课,甚至是盗版视频,都是享受不到这种快感的。

有点标题党,贻笑大方了。水平有限,理三区两通道解有偏差的地方,还请不吝留言指正。

全文完。

            </div>

0. 背sqlserver景信息

1. MySQL 8.0.18 以前是怎么加锁的

2. MySQL 8.0.18 之后终于变天了

0. 背景信息

最近在课程中讲开发工具不能用到InnoDB行锁时,sql数据库讲到一个知识点

InnoDB行锁规则上,有这样的一个原则:

对有唯一属性的索引(主键/唯一索引)进行范围条件加锁时,

向右遍历(假设是普通正序索引,而且不加ORDER BY … DESC约束)过程中,

会一直扫描并加next-key锁到第一个不满足条件的记录为止,

索引的优缺点如果是RC级别,这个next-key lock会退化成gap lock,而RR下不会退化。

简言之,就是 "锁会被扩大化",从InnoDB引擎诞生以来一直都是如此。

其实严格来说,这个算是问题或缺陷,关系型数据库的第二范式甚至也可以认为是bu

1. MySQL 8.0.18 以前是怎么加锁的

我们看看下面的案例。

首先,确认版本、隔离级别、表结构、索引以及数据。

建议github:在PC端阅读本文体验更好。

# 5.6版本
[root@yejr.run]> select version();
+------------+
| version()  |
+------------+
| 5.6.39-log |
+------------+
#隔离级别
[root@yejr.run]> select @@session.tx_isolation;
+------------------------+
| @@session.tx_isolation |
+------------------------+
| REPEATABLE-READ        |
+------------------------+
#表数据
[root@yejr.run]> select * from t1;
+----+----+----+----+
| c1 | c2 | c3 | c4 |
+----+----+----+----+
|  0 |  0 |  0 |  0 |
|  1 |  1 |  1 |  0 |
|  3 |  3 |  3 |  0 |
|  4 |  2 |  2 |  0 |
+----+----+----+----+
#表结构&索引,c1是主键(有唯一属性),c2是辅助索引
[root@yejr.run]> show create table t1\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `c1` int(10) unsigned NOT NULL DEFAULT '0',
  `c2` int(10) unsigned NOT NULL DEFAULT '0',
  `c3` int(10) unsigned NOT NULL DEFAULT '0',
  `c4` int(10) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`c1`),
  KEY `c2` (`c2`)
) ENGINE=InnoDB;

下面的两个案例中,session2的请求会被阻塞

时间点 session1 sessioin2
T1 begin; begin;
T2 select * from t1 whesql数据库re c1<=1 for update;
T3 delete from t1 where c1=3;

被阻塞

时间点 session1 sessioin2
T1 begin; begin;
T2 delete from t1 where c1=3;
T3 select * from t1 where c1<=1 for update;

一样会被阻塞

SQL因在于 select * from t1 where c1<=1 fgit教程or update 这个SQL中,索引除了对 c1<mysql数据库命令大全=1 的所有记录加上 LOCK_X|LOCK_ORDINARY(排他的next-key lock)之外,还需要对 c1=3 这条记录也加同样的锁

查看 information_schema 下的两个视图 innodb_locksinnodb_lock神祇领主时代_waits 可以确认:

[root@yejr.run]> select * from INNODB_LOCKs\G

1. ro开发工具是什么意思w **
lock_i开发工具选项卡在哪里d: 2849:26:3:4 --请求的锁
lock_trx_id: 2849 --被阻塞的事务
lock_mod索引失效e: X --拍他锁
lock_type: RECORD --锁类型是gitee LOCK_ORDINARY(即next-lock)
locgitik_table: `tes开发工具t`.`t1`
lock_index: PRIMARY
lock_space: 26
lock_page: 3
lock_rec: 4
lock_data: 3
2. row *索引*
lock_id: 2848:26:3git:4 --持有的锁
lock_trx_id: 2848 --持有锁的事务
lock_mode: X --排他锁 LOCgitlabK_X
lock_typ开发工具控件怎么用e: RECORD --锁类型是 LOCK_ORDINARY(即next-lock)
lock_tagitble: `test`.`t1` --表
lock_in关系型数据库管理系统dex: PRIMARY --索引
lock_space: 26 --table space id
lock_page: 3 --page nosql数据库
lock_rec: 4 --heapgithub中文官网网页 no
lock_开发工具data: 3 --被加锁的row data,即c1=3这条记录
2 rows in set (0.00 sec)

[root@yejr.run]> select * from INNODB_LOCK关系型数据库的基本运算包含_waits\mysql数据库基础知识G
1. row **
requesting_trx_id: 2849 --请求锁的事务(被阻塞状态)开发工具控件怎么用
requested关系型数据库的基本运算包含_lock_id: 2849:26:3:4 --请求的锁
blocking_trx_id: 2848 --持有锁的事务
bl关系型数据库的基本运算包含ocking_lockgithub中文官网网页_id: 2848:26:3:4 --持有的锁

当然了,也可以从 show engine innodb statu索引是什么意思s\G 的结果中确认,这里不赘述。

2. MySQL 8.0.18 之后终于变天了

这个存在了将近20年的"bug",终于在2019.10.14发布的MySQL 8.0.18版本中微信开发工具被解决(修复)了,当时开发工具我居然没注意到这个release note。

InnoDB: An unnecessary next key lock was taken when performing 
a SELECT...FOR [SHARE|UPDATE] query with a WHERE condition that
specifies a range, causing one too many rows to be locked. The
most common occurrences of this issue have been addressed so
that only rows and gaps that intersect the searched range are
locked. (Bug #29508068)

简言之:就是不再需要对不必要的数据上锁啦。

再看看上面几个案例在最新的MySQL 8.0.19版本下的表现。

时间点 session1 sessioin2
T1 begin;三区两通道 bemysql安装gin;
T2 select * from t1 where c1<=1 for update;
T3 delete from t1 where c1=3;

不再被阻塞

看下加锁详情

select ENGINE_LOCK_ID,ENGINE_TRANSACTION_ID,THREAD_ID,OBJECT_NAME,INDEX_NAME,LOCK_TYPE,LOCK_MODE,LOCK_STATUS,LOCK_DATA from data_locks;
+-----------------------------------+-----------------------+-----------+-------------+------------+-----------+---------------+-------------+-----------+
| ENGINE_LOCK_ID | ENGINE_TRANSACTION_ID | THREAD_ID | OBJECT_NAME | INDEX_NAME | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA |
+-----------------------------------+-----------------------+-----------+-------------+------------+-----------+---------------+-------------+-----------+
| 4868124032:1127:140327172372248 | 18983 | 351 | t1 | NULL | TABLE | IX | GRANTED | NULL |
| 4868124032:44:4:4:140327176578584 | 18983 | 351 | t1 | PRIMARY | RECORD | X,REC_NOT_GAP | GRANTED | 3 |
| 4868123176:1127:140327172370216 | 18982 | 350 | t1 | NULL | TABLE | IX | GRANTED | NULL |
| 4868123176:44:4:2:140327176573976 | 18982 | 350 | t1 | PRIMARY | RECORD | X | GRANTED | 0 |
| 4868123176:44:4:3:140327176573976 | 18982 | 350 | t1 | PRIMARY | RECORD | X | GRANTED | 1 |
+-----------------------------------+-----------------------+-----------+-------------+------------+-----------+---------------+-------------+-----------+

可以看到,select * from t1 where c1<=1 for update; 这个SQL只会git命令对 c1=[0,1] 两条记录加上 LOCK_X|LOCK_ORDINARY 锁,不会再对 c1=3 加锁了。

这个有年头的"bug"终于被搞定了,可喜可贺。

最后,来看下关于这个"bug"的描述。当然了,公开的bug系统看mysql索引不到,需要用MOS账号才可以。下面是从代码git log里的部分摘抄:

commit d1b0afd75ee669f54b70794eb6dab6c121f1f179
Author: Jakub opuszaski <jakub.lopuszanski@oracle.com>
Date: Wed Jul 17 16:34:01 2019 +0200

Bug #29508068 UNNECESSARY NEXT-KEY LOCK TAKEN

When doing a SELECT...FOR [SHARE|UPDATE] with a WHERE condition specifying a range,
we were locking "one row too much".
This patch fixes locking behaviour in several (hopefuly) most common cases, so that
we only lock rows and gaps which intersect the searched range.

- Added MTR to demonstrate current locking policy for end of range
- Got rid of goto
- Extracted logic of determining relation between range and row to separate function
- Extracted reoccuring patterns of modifications of search_tuple so it is easier to add same for stop_tuple
- Added prebuilt->m_stop_tuple and made sure it is in sync with prebuilt->m_mysql_handler->end_range for during read_range_first() and read_range_next()
- Added row_can_be_in_range field
- Do not lock the row (just the gap) if the row is same length and after the stop_tuple
- Do not lock the row (just the gap) if the row is same length and equal to stop_tuple and strict inequality was used for end of range
- Do not lock the row (just the gap) if the row is longer than stop_tuple and its prefix is after the stop_tuple
- Do not lock the row (just the gap) if the row is longer than stop_tuple and its prefix is equal to stop_tuple and strict inequality was used for end of range
- Do not lock the row nor gap if we already saw a row same length and equal to stop_tuple in previous iteration

Reviewed-by: Pawel Olchawa <pawel.olchawa@oracle.com>
RB:22293

所以,还是赶紧升级到MySQL 8.0的最新版本吧,不光功能更强,连锁也进索引页是哪一页一步优化了。

写到这里,不禁想嘚瑟一下,加入我的「MySQL优化课」课程优势就体现出来了,一旦有重大的索引页是哪一页知识更新,总是能比别人先一步知道,图便宜买一些万年不更新的旧课,甚至是盗版视频,都是享受不到这种快感的。

有点关系型数据库和非关系型标题党,贻笑大方了。水平有限,理解有mysql面试题偏差的地方,还请不吝留言指正。

全文完。