mysql 退化range锁_InnoDB这个将近20年的"bug"修复了

最新推荐文章于 2024-04-12 11:47:59 发布
最新推荐文章于 2024-04-12 11:47:59 发布
阅读量144 收藏
点赞数
文章标签： mysql 退化range锁
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_42190623/article/details/114815584
版权

原标题：InnoDB这个将近20年的"bug"修复了

有点意外，惊喜ing。

0. 背景信息

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

2. MySQL 8.0.18 之后终于变天了

0. 背景信息

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

InnoDB行锁规则上，有这样的一个原则：

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

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

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

但如果是RC级别，这个next-key lock会退化成gap lock，而RR下不会退化。

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

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

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

我们看看下面的案例。

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

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

# 5.6版本

[root@yejr.run]> selectversion;

+------------+

| 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 t1G

*************************** 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 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 * from t1 where c1<=1 for update 这个SQL中， 除了对 c1<=1 的所有记录加上 LOCK_X|LOCK_ORDINARY(排他的next-key lock)之外，还需要对 c1=3 这条记录也加同样的锁。

查看 information_schema 下的两个视图 innodb_locks 和 innodb_lock_waits 可以确认：

[root@yejr.run]> select * from INNODB_LOCKsG

*************************** 1. row ***************************

lock_id: 2849:26:3:4 --请求的锁

lock _trx_id: 2849 --被阻塞的事务

lock_mode: X --拍他锁

lock _type: RECORD --锁类型是 LOCK_ORDINARY(即next-lock)

lock_table: `test`. `t1`

lock_index: PRIMARY

lock_space: 26

lock_page: 3

lock_rec: 4

lock_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_waitsG

*************************** 1. row ***************************

requesting _trx_id: 2849 --请求锁的事务(被阻塞状态)

requested _lock_id: 2849:26:3:4 --请求的锁

blocking _trx_id: 2848 --持有锁的事务

blocking _lock_id: 2848:26:3:4 --持有的锁

当然了，也可以从 show engine innodb statusG 的结果中确认，这里不赘述。

2. MySQL 8.0.18 之后终于变天了

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

InnoDB: An unnecessary next key lockwas taken whenperforming

a SELECT...FOR [ SHARE| UPDATE] querywitha WHEREcondition that

specifies a range, causing one too many rowstobe locked. The

most common occurrences ofthis issue have been addressed so

that onlyrowsandgaps that intersectthe searched rangeare

locked. (Bug #29508068)

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

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

时间点

session1

sessioin2

T1

begin;

begin;

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只会对 c1=[0,1] 两条记录加上 LOCK_X|LOCK_ORDINARY 锁，不会再对 c1=3 加锁了。

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

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

commitd1b0afd75ee669f54b70794eb6dab6c121f1f179

Author: Jakub Łopuszański

Date: Wed Jul 1716: 34: 012019+ 0200

Bug #29508068 UNNECESSARY NEXT-KEY LOCK TAKEN

Whendoing a SELECT...FOR [ SHARE| UPDATE] witha WHEREcondition specifying a range,

we were locking "one row too much".

This patchfixes locking behaviour inseveral (hopefuly) most common cases, so that

we onlylockrowsandgaps which intersectthe searched range.

- Added MTR todemonstrate currentlocking policyforendofrange

- Got rid ofgoto

- Extracted logic ofdetermining relation betweenrangeandrowtoseparate function

- Extracted reoccuring patterns ofmodifications ofsearch_tuple so it iseasier toaddsame forstop_tuple

- Added prebuilt->m_stop_tuple andmade sure it isinsyncwithprebuilt->m_mysql_handler->end_range forduring read_range_first andread_range_next

- Added row_can_be_in_range field

- Donotlockthe row(just the gap) ifthe rowissame lengthandafterthe stop_tuple

- Donotlockthe row(just the gap) ifthe rowissame lengthandequal tostop_tuple andstrictinequality was used forendofrange

- Donotlockthe row(just the gap) ifthe rowislonger thanstop_tuple andits prefix isafterthe stop_tuple

- Donotlockthe row(just the gap) ifthe rowislonger thanstop_tuple andits prefix isequal tostop_tuple andstrictinequality was used forendofrange

- Donotlockthe rownor gap ifwe already saw a rowsame lengthandequal tostop_tuple inprevious iteration

Reviewed- by: Pawel Olchawa

RB: 22293

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

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

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

由叶老师主讲的知数堂「MySQL优化课」课程早已升级到MySQL 8.0版本了，现在上车刚刚好，扫码开启MySQL 8.0的修行之旅吧。返回搜狐，查看更多

责任编辑：

黄子衿
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
mysql 退化range锁_MySQL-锁总结
weixin_35334000的博客
02-05 512
锁锁机制用于管理对共享资源的并发访问。lock和latch在数据库中，lock和Latch都称为锁，但是两者意义不同。latch称为闩锁(shuang suo)，其要求锁定的时间必须非常短。若持续的时间长，则应用的性能会非常差。在InnoDB存储引擎中，latch又分为mutex互斥锁 和 rwLock读写锁。其目的是为了保证并发线程操作临界资源的正确性。通常没有死锁的检测机制。lock的对象是事...
MySQL加锁过程分析
wait2023的博客
03-24 916
目录1 MySQL加锁规则2 案例分析2.1 数据准备2.2 案例12.3 案例23 总结如何分析加锁 最近在通过极客时间的专栏，MySQL45讲复习mysql，看完了锁的部分，结合原来看到《Mysql是怎样运行的书》，做了一些小总结 1 MySQL加锁规则 这里我们提到的加锁规则都是在隔离级别为可重复读的情况，具体规则如下： 我们加锁的最基本的单位都是next-key lock，是对一个左开右闭的区间进行加锁 这里注意next-key lock可以认为是一个复合锁，是record lock 和ga
MySQL中的锁（简单）
AliceNo的博客
11-28 1132
在 Microsoft SQL Server（MS SQL）中，锁是管理并发访问数据库资源的重要机制。锁可以防止多个事务同时访问或修改相同的数据，以确保数据库的一致性和完整性。
mysql隔离级别RR下的行锁、临键锁、间隙锁详解及运用
qq_35572020的博客
11-07 2297
mysql隔离级别RR下的行锁、临键锁、间隙锁详解及运用
MySQL的锁机制（通俗易懂）
qq_41845823的博客
04-19 1785
MySQL的锁机制 mysql`锁分为全局锁、表锁和行锁 全局锁 ​ 全局锁是将整个数据库锁起来，使得其他的事务只能进行读，不能写，主要用在数据库备份时 表锁 ​ 表锁分为普通表锁，元数据锁和意向锁 普通表锁是为了防止事务之间对表数据的访问发生冲突； 元数据锁分为表级共享锁和表级排他锁，主要是防止事务访问表和事务修改表结构之间的冲突，增删改查数据会产生共享读锁或者共享写锁，这两个锁会与update表结构产生的排他锁产生冲突； 意向锁分为意向共享锁和意向排他锁，主要是为了防止行锁和表锁之间的冲突，
MySql InnoDB行锁——“三剑客”
u013487071的博客
04-11 928
提示：文章写完后，目录可以自动生成，如何生成可参考右边的帮助文档 行锁“三剑客”前言一、行锁三剑客是什么？二、记录锁（Record Locks）三、间隙锁（Gap Locks）四、临键锁（Next-Key Locks）总结 前言 行锁在 InnoDB 中是基于索引实现的，所以一旦某个加锁操作没有使用索引，那么该锁就会退化为表锁。 提示：以下是本篇文章正文内容，下面案例可供参考 一、行锁三剑客是什么？ 行锁三剑客指的是：InnoDB引擎下的记录锁（Record Locks），间隙锁（Gap Locks.
MySQL的锁机制——记录锁、间隙锁、临键锁
m0_51431003的博客
03-09 3466
当我们使用索引，无论是等值还是范围查询，没有命中一条记录时候，加的就是间隙锁。我们在表中插入4条记录，主键分别是1、4、7、10。图中的范围区间就会被锁住，都是左开右开的区间。
MySQL修改innodb_data_file_path参数的一些注意事项
09-09
这个参数至关重要，因为它决定了InnoDB表空间的布局和扩展方式。在默认情况下，如果未在配置文件（如my.cnf）中指定`innodb_data_home_dir`和`innodb_data_file_path`，InnoDB将会在数据目录（datadir）下创建一个名...
py_innodb_page_info工具类 包含python2，python3两个版本
07-01
py_innodb_page_info工具为《INNODB存储引擎》作者姜承尧写的。 该工具用来分析表空间中的各页得类型和信息，用python编写。 网上多是python2版本的，这里给出python3版本的。
py_innodb_page_info.zip
12-01
《MySQL技术内幕-InnoDB存储引擎》是一本深入探讨MySQL数据库InnoDB存储引擎的权威书籍，而`py_innodb_page_info.zip`则是该书中提到的一个实用工具，用于分析InnoDB存储引擎的数据页信息。这个Python工具帮助我们...
tables_mysql_innodb.sql
08-10
Quartz初始化数据库建表SQL，包含每个字段及每个表的注释，可以帮助你快速创建所需要的表
『 MySQL篇 』：MySQL 锁机制介绍
m0_56361048的博客
06-15 8050
MySQL锁机制详解
MySQL锁机制解析 + 行级锁加锁过程中锁退化剖析
最新发布
weixin_73077810的博客
04-12 881
本文将深入探讨MySQL中的锁分类概念及介绍行级锁的加锁细节，主要分为全局锁、表级锁和行级锁三类。包括对唯一索引和非唯一索引的等值查询、范围查询等情况下的加锁规则，以及没有加索引的查询的影响。最后，文章强调了在执行具有加锁性质的查询时，务必检查查询是否走了索引，以避免全表扫描导致的性能问题。
MySQL事务与锁详解
热门推荐
Coder Liu的博客
10-13 1万+
事务 事务支持ACID特性 A原子性:所有操作要么都做要么都不做 C一致性:事务将数据库从一种状态变为另一种状态一致性,保证数据库完整性约束,例如唯一索引约束等 I隔离性:事务与事务之间是不可见的 D持久性:事务一旦提交那么事务就是永久性的 ANSI/ISO SQL标准定义了4中事务隔离级别：未提交读（read uncommitted），读提交（read committed），可重复读（repea...
【漏洞提示】MySQL8.0.29因重大bug官网已下架
fashion的博客
12-02 1837
前阵子，MySQL官网已经将 MySQL 8.0.29版本下架。据悉下架原因是由于MySQL 8.0.29 存在关于InnoDB解释器的重大Bug。而最新版本 8.0.30及以上的版本已修复此漏洞。
阿里大神万字总结：InnoDB锁类型及其详细分析（仅此一篇，干货建议收藏！！）
程序员干货站
05-28 600
0 事务支持 首先回顾一下事务相关的基础概念。 在默认情况下，InnoDB 开启自动提交，每一个 SQL 语句会形成其独立的事务，在语句执行完毕后自动提交，如果语句执行失败，则自动回滚。用户可以通过 START TRANSACTION 或 BEGIN 命令显式开启事务，通过 COMMIT 或 ROLLBACK 显示提交或回滚事务。用户可以通过 SET AUTO_COMMIT=0 显式关闭自动提交。 隔离级别方面，InnoDB 支持标准的： READ UNCOMMITTED ， READ COMMITED ，
MySQL锁机制&事务
java_beautiful的博客
08-23 458
锁是计算机协调多个进程或线程并发访问某一资源的机制（避免争抢）。在数据库中，除传统的计算资源（如 CPU、RAM、I/O 等）的争用以外，数据也是一种供许多用户共享的资源。如何保证数据并发访问的一致性、有效性是所有数据库必须解决的一个问题，锁冲突也是影响数据库并发访问性能的一个重要因素。从这个角度来说，锁对数据库而言显得尤其重要，也更加复杂。行锁特点 ：偏向InnoDB 存储引擎，开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高。
mysql 退化range锁_mysql数据库锁
weixin_35849560的博客
02-27 180
mysql开启事务的三种方式(查看事务是否自动提交)show variables LIKE 'autocommit'手动提交事务：BEGIN;START TRANSACTION;update teacher set name = 'zhangsan' where id = 1;insert teacher (name,age) value = ('lisi', 30);COMMIT;ROLLBA...
Mysql - 争取一篇讲清楚Mysql中的锁（全局锁、表级锁、行级锁）及加锁规则、死锁问题
it_lihongmin的博客
03-17 1093
目录 全局锁（Flush tables with read lock （FTWRL）） 表级锁（表锁、MDL读锁写锁） 行级锁（行锁、临建锁、间隙锁） Mysql中的锁比较多并且加锁规则复杂，争取这一篇博客可以分析清楚，锁分为全局锁和表级锁是整个Mysql实例和所有的存储引擎都可以使用的。行级锁是InnoDB引擎特有的，而InnoDB最大的区别于其他引擎的特点就是事务，事务与锁、MVCC、Redo Log、Undo Log、Binlog都有关系，所以InnoDB为了实现事务代价还是比较大...
mysql innodb_support_xa_innodb_support_xa的作用
06-12
`innodb_support_xa`参数用于控制InnoDB存储引擎是否支持分布式事务的XA协议。如果设置为1，则表示支持XA协议，否则不支持。 XA协议是一种分布式事务协议，可以用于在多个数据库实例之间协调事务的提交和回滚。如果...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值