innodb不同场景下加锁分析总结

最新推荐文章于 2024-11-10 22:27:03 发布
最新推荐文章于 2024-11-10 22:27:03 发布
阅读量476 收藏
点赞数
分类专栏： mysql innodb 文章标签： mysql 锁分析 innodb
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/postgres20/article/details/127846586
版权

Read-Uncommitted/Read-Committed隔离级别上锁过程

查询条件

SQL示例

加锁分析

查询条件为主键等值

SELECT … WHERE PK = XX FOR UPDATE;

1：如果有对应记录则对记录加LOCK_X|LOCK_REC_NOT_GAP锁

2：如果没有则不加锁，以下场景都相同处理

UPDATE … WHERE PK = XX;

1：如果未更新其他索引列。

只需要在主键记录上加X锁即可，加锁为LOCK_X|LOCK_REC_NOT_GAP。

2：更新包括其他索引列。

除了主键记录加X锁，还需要在涉及索引上加LOCK_X|LOCK_REC_NOT_GAP锁。

DELETE … WHERE PK = XX;

对主键、各个索引对应的记录都要加X锁，LOCK_X|LOCK_REC_NOT_GAP。

查询条件为主键范围

select * from t where c1 >= 20 for update;

会对所有满足条件的记录加锁，如果没有则不加锁

update t set c2 = c2 + 1 where c1 >= 20;

会依次对c1 >=20的主键记录行加X锁，对应的索引行做加X锁操作。

查询条件为唯一索引等值

SELECT … WHERE UK = XX FOR UPDATE;

需要在索引记录上加X锁:LOCK_X|LOCK_REC_NOT_GAP，同时还要在对应主键行上加X锁。

UPDATE … WHERE UK = XX;

1:未更新其他索引列

对唯一索引上数据加X锁（LOCK_X|LOCK_REC_NOT_GAP），然后对应的主键行也需要加X锁。

2:更新其他索引列

依次对唯一索引数据、主键行、索引数据加X锁。

DELETE … WHERE UK = XX;

会对唯一索引数据加X锁，根据唯一索引找到主键行后，会再依次对主键行、唯一索引、索引数据加X锁。

查询条件为唯一索引范围

SELECT … WHERE UK >= XX FOR UPDATE;

1: 如果是通过主键，则对主键行依次加X锁， 同时在加锁后，分析发现不满足条件会立即释放该行上的X锁。

2：如果是通过唯一索引，则依次对满足条件的唯一索引、主键记录加X锁。

UPDATE … WHERE UK <= XX FOR UPDATE;

会对唯一索引和对应主键行依次加X锁，然后判断不满足range条件，释放唯一索引和对应主键行上的X锁。

查询条件为非唯一索引

SELECT … WHERE IDX = XX FOR UPDATE;

非唯一索引情况与唯一索引情况加锁情况一致

查询条件上无索引

SELECT … WHERE COL = XX FOR UPDATE;

路径选择主键，会依次对主键行加锁，分析条件，不满足条件释放锁，满足条件持有锁。

多条件查询

select * from t where c2 = xx and c3 = xx for update;

当存在多个条件的时候，除了主键行上的锁，其他的加锁情况取决于选择的路径。

选择了走唯一索引，就会对满足条件的唯一索引行加X锁，然后对主键行加X锁。

主键路径，就会对所有行一次加X锁，分析条件，最终持有主键上c1 = 20的X锁。

Repeatable Read隔离级别上锁过程 

查询条件

SQL示例

加锁分析

查询条件为主键等值

SELECT … WHERE PK = XX FOR UPDATE;

1：如果有对应记录则对记录加LOCK_X|LOCK_REC_NOT_GAP锁

2：如果没有则会对后面的记录加GAP锁，（LOCK_X|LOCK_GAP），防止有其他语句插入pk=xx的行。

UPDATE … WHERE PK = XX;

1：如果未更新其他索引列。

只需要在主键记录上加X锁即可，加锁为LOCK_X|LOCK_REC_NOT_GAP。

2：更新包括其他索引列。

除了主键记录加X锁，还需要在涉及索引上加LOCK_X|LOCK_REC_NOT_GAP锁。

3：没有满足条件的记录则不加锁

DELETE … WHERE PK = XX;

1：如果有满足条件的记录对主键、各个索引对应的记录都要加X锁，LOCK_X|LOCK_REC_NOT_GAP。

2：没有则不加锁

查询条件为主键范围

select * from t where c1 >= 20 for update;

这里会对c1=20加X锁(LOCK_X|LOCK_REC_NOT_GAP)，对c1>20对应的行加exclusive next-key lock(LOCK_X|LOCK_ORDINARY)，同时会对表示记录上界的’supremum’加exclusive next-key lock。以阻塞其他事务对c1>=20的插入。

select * from t where c1 <= 20 for update;

这里会依次对c1<=20的主键记录加exclusive next-key lock(LOCK_X|LOCK_ORDINARY)锁。且在判断c1=30不符合查询条件后，虽然server层调用unlock_row，但对于RC隔离级别以上且没有设置innodb_locks_unsafe_for_binlog那么并不会释放锁。

update t set c2 = c2 + 1 where c1 >= 20;

1:未更新其他索引列。

加锁与上面SELECT… WHERE PK >= XX FOR UPDATE;一致。

2:更新包含索引列。

对主键c1=20加X锁，索引行加X锁，然后对后面的主键行加exclusive next-key lock(LOCK_X|LOCK_ORDINARY)，同时对应的i_c2索引行加X锁，最后对表示记录上界的’supremum’加exclusive next-key lock。

UPDATE … WHERE PK <= 20;

1:未更新其他索引列。

加锁与SELECT… WHERE PK <= XX FOR UPDATE;一致

2:包含索引列。

对pk<=20的主键满足条件记录加exclusive next-key lock(LOCK_X|LOCK_ORDINARY)，同时对应的索引行加X锁。然后对pk>20的第一条记录加exclusive next-key lock，因不满足条件，因此server层查询停止。同样并不会释放后面记录上的锁。

DELETE … WHERE PK >= 20;

会对pk=20加X锁，对pk=20对应的索引加X锁，然后依次对pk>20的主键记录加exclusive next-key lock(LOCK_X|LOCK_ORDINARY)，同时对应的索引行加X锁，最后对’supremum’加LOCK_X|LOCK_ORDINARY。

查询条件为唯一索引等值

SELECT … WHERE UK = XX FOR UPDATE;

需要在索引记录上加X锁:LOCK_X|LOCK_REC_NOT_GAP，同时还要在对应主键行上加X锁。

UPDATE … WHERE UK = XX;

1:未更新其他索引列

对唯一索引上数据加X锁（LOCK_X|LOCK_REC_NOT_GAP），然后对应的主键行也需要加X锁。

2:更新其他索引列

依次对唯一索引数据、主键行、索引数据加X锁。

DELETE … WHERE UK = XX;

会对唯一索引数据加X锁，根据唯一索引找到主键行后，会再依次对主键行、唯一索引、索引数据加X锁。

查询条件为唯一索引范围

SELECT … WHERE UK >= XX FOR UPDATE;

1: 如果是通过主键，对满足条件的主键记录分别加exclusive next-key lock(LOCK_X|LOCK_ORDINARY)，同时对上界’supremum’加exclusive next-key lock，锁住全部数据范围。

2：如果是通过唯一索引，那么会对满足条件的索引记录分别加exclusive next-key lock，对应主键行加X锁，同时对索引上的’supremum’ record加exclusive next-key lock。

SELECT … WHERE UN <= XX FOR UPDATE;

对索引上满足条件的记录加exclusive next-key lock，对对应的主键行加X锁，然后对大于xx的第一条记录加exclusive next-key lock，且并不会去释放。

UPDATE … WHERE UK >= XX;

1:未包含索引列。

等同上面指定走唯一索引的SELECT…FOR UPDATE语句加锁。

2: 包含索引列。除了之前提到的主键和唯一索引加锁之外，对应被涉及到的索引行也要加X锁。

UPDATE … WHERE UK <= XX FOR UPDATE;

1:未包含索引列。

会对索引上<xx的记录加exclusive next-key lock，对对应的主键行加X锁。因为没有ICP，这里>xx的第一条记录对应的索引行和主键行也会加X锁，同时不会释放。

2:包含索引列

会对索引上<xx的记录加exclusive next-key lock，对对应的主键行和索引加X锁。对>xx的第一条记录加exclusive next-key lock，对应主键行加X锁。

DELETE … WHERE UK >= XX;

走索引，会对>=xx的第一条记录加exclusive next-key lock，对应主键行加X锁，索引上数据行进行加X锁操作，对索引上’supremum’ record加exclusive next-key lock。

查询条件为非唯一索引等值

SELECT … WHERE IDX = XX FOR UPDATE;

会对=xx在索引上加exclusive next-key lock(LOCK_X|LOCK_ORDINARY)，对应主键加X锁(LOCK_X|LOCK_REC_NOT_GAP)，然后在下一条记录上加exclusive gap lock(LOCK_X|LOCK_GAP)。即该语句会锁定范围。

RR和RC隔离级别都要加的锁 

唯一二级索引（uk)更新或插入

UPDATE set uk=xxx… WHERE UK = XX;

insert into ...uk =xxx

如果UK原来存在，需要做去重判断，此时要对这个UK加S锁。比方说插入uk=1,表里已经有这个uk=1的记录，或者被删除还未被清理，需要先对uk=1这索引加S LOCK_ORDINARY锁再加X锁。更新UK，这等价于先删除再插入，一定有一条相同UK老记录，也需要先加S锁。

原因在于代码逻辑中 row_ins_scan_sec_index_for_duplicate 重复检查要加S LOCK_ORDINARY锁。

一个UK更新例子：


CREATE TABLE `t_test` (
  `a` bigint(20) NOT NULL COMMENT 'a',
  `b` varchar(32) NOT NULL COMMENT 'b',
  `c` varchar(32) DEFAULT NULL COMMENT 'c',
  `d` int(11) NOT NULL COMMENT 'd',
  `e` bigint(20) NOT NULL COMMENT 'e',
  PRIMARY KEY (`a`),
  UNIQUE KEY `uk_b_d` (`b`,`d`) USING BTREE,
  KEY `idx_b_e` (`b`,`e`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='test';

insert into t_test values(1,'1','1',1,1);
insert into t_test values(5,'5','5',5,5);
insert into t_test values(10,'10','10',10,10);
insert into t_test values(15,'15','15',15,15);
update t_test set a = 7, b = "5", c = "5",  d = 5, e = 7  where  b = "5" and d = 5;

锁情况：
TRANSACTIONS
======================
Trx id counter 1363
Purge done for trx's n:o < 1363 undo n:o < 0 state: running but idle
History list length 3
Total number of lock structs in row lock hash table 2
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 421893635597552, not started
0 lock struct(s), heap size 1160, 0 row lock(s)
---TRANSACTION 421893635594896, not started
0 lock struct(s), heap size 1160, 0 row lock(s)
---TRANSACTION 1358, ACTIVE 6 sec
3 lock struct(s), heap size 1160, 2 row lock(s), undo log entries 1
MySQL thread id 4, OS thread handle 140418654865152, query id 99 localhost 127.0.0.1 root
TABLE LOCK table `test`.`t_test` trx id 1358 lock mode IX
RECORD LOCKS space id 24 page no 4 n bits 72 index uk_b_d of table `test`.`t_test` trx id 1358 lock_mode X locks rec but not gap
RECORD LOCKS space id 24 page no 3 n bits 72 index PRIMARY of table `test`.`t_test` trx id 1358 lock_mode X locks rec but not gap
======================

Mysql(InnoDB)的SQL加锁分析
不能说的秘密的博客
03-17 1339
一、背景 工作经常遇到在编写并发场景sql时考虑欠缺，造成过一些bug和sql死锁。于是乎，笔者查阅了不少相关资料。 本文就MySQL/InnoDB的加锁问题，展开较为深入的分析与讨论，主要是介绍一种思路，运用此思路，拿到任何一条SQL语句，都能完整的分析出这条语句会加什么锁？会有什么样的使用风险？甚至是分析线上的一个死锁场景，了解死锁产生的原因。 注：MySQL是一个支持插件式存储引擎的数据库系...
innoDB下的加锁机制（含测试场景图）
weiweichaoren123的博客
03-17 459
innoDB下的加锁机制（含测试场景图） 思维导图 测试准备条件 工具：navicat，利用多个新建查询面板模拟多事务 表结构和数据： 开始测试： 场景一：对 num = 1 加入行锁，去修改 num = 1 和 num = 3 的数据 结论：事务A对 num = 1 加锁，事务B不能修改 num = 1，但能修改 num = 3 场景二：对 num > 1 加锁，去修改 num = 1 和 num = 3 的值，新增 num = 2 结论：对 num > 1 加锁，修改
完整版：Innodb到底是怎么加锁的
梁桂钊的博客
11-16 914
点击上方“服务端思维”，选择“设为星标”回复”669“获取独家整理的精选资料集回复”加群“加入全国服务端高端社群「后端圈」作者 | 小孩子4919出品| 我们都是小青蛙不知道从什么时候开...
mysql InnoDB引擎各种隔离级别的加锁机制
m0_37903574的博客
08-22 2350
上述语句我们在执行时，是能成功的插入数据的，通过锁分析也可得知在RC隔离级别下，对于普通索引的数据我们只加了行级锁，但是没有加gap lock的。当我们在执行步骤4时其实就会被阻塞了，数据是没办法插入成功的，因为此时在age的的非唯一索引上加了age=4的数据的行级别锁，在非主键键索引上在age（3,4）（4,44）之间加了gap lock。最终发现，执行到步骤4时，数据是可以插入成功的，那就是因为，我们age为64的数据不在gap锁锁定age的范围（3,4），（4,44）范围内。行级锁+间隙锁组成的锁。
MySQL INNODB是怎么加锁的？
一个技术菜鸟的博客
08-06 1336
怎么查询？ 查询无处不在，无论是select 还是update、delete，首先要做的就是查询是否存在目标记录。 innodb的表是由几棵索引树组成的，首先有一颗主键索引树，每行完整数据只存在于其叶子节点上，非叶子节点仅用于排序；然后还有唯一索引树和普通索引树，唯一索引树和普通索引树的叶子节点仅存储索引值和主键值，因此通过唯一索引或普通索引查询时，可能需要再根据主键索引值查询主键索引树，这也就是所谓的回表查询。 索引树均是向上排序，即左边的叶子节点一定比右边的叶子节点的小，普通索引树中相等的几个索引值的叶
MySQL的死锁系列 - 常见加锁场景分析
ITMuch的专栏
08-31 291
点击上方IT牧场，选择置顶或者星标技术干货每日送达！在上一篇文章《锁的类型以及加锁原理》主要总结了 MySQL 锁的类型和模式以及基本的加锁原理，今天我们就从原理走向实战，分析常见...
MySQL死锁系列-常见加锁场景分析
taylor的专栏
05-28 2743
在上一篇文章《锁的类型以及加锁原理》主要总结了 MySQL 锁的类型和模式以及基本的加锁原理，今天我们就从原理走向实战，分析常见 SQL 语句的加锁场景。了解了这几种场景，相信小伙伴们也能举一反三，灵活地分析真实开发过程中遇到的加锁问题。 如下图所示，数据库的隔离等级，SQL 语句和当前数据库数据会共同影响该条 SQL 执行时数据库生成的锁模式，锁类型和锁数量。 下面，我们会首先讲解一下隔离等级、不同 SQL 语句 和 当前数据库数据对生成锁影响的基本规则，然后再依次具体 SQL 的加锁场景。 隔离等级对
MYSQL InnoDB 加锁分析
weixin_35833012的博客
03-04 350
前言 理解 InnoDB 行锁，分析一条 SQL语句会加什么样的行锁，会锁住哪些数据范围对业务 SQL 设计和分析线上死锁问题都会有很大帮助。本文的目的是对InnoDB的事务锁模块做个简单的介绍。 隔离级别 InnoDB 中的 锁和当前的隔离级别有很大关系，这里简单介绍一下数据库事物的四种隔离级别一级每种隔离级别的数据读取问题。 Read uncommitted(读未提交) 可以读未提交记录。 ...
10个行锁、死锁案例⭐️24张加锁分析图彻底搞懂Innodb行锁加锁规则！
关注菜菜的后端私房菜，获取更多技术干货~
02-21 2193
本篇文章通过大量案例、图例分析不同情况下的行锁加锁规则update、delete 先查再改，可以看成锁定读，insert则是有单独一套加锁规则锁定读加锁规则在RC及以下隔离级别，锁定读使用record锁；在RR及以上隔离级别，锁定读使用next key锁等值查询：如果找不到记录，该查询条件所在区间加GAP锁；如果找到记录，唯一索引临键锁退化为记录锁，非唯一索引需要扫描到第一条不满足条件的记录，最后临键锁退化为间隙锁（不在最后一条不满足条件的记录上加记录锁）
mysql 加锁分析与实践
bohu83的博客
09-25 976
一序      本文分为两个部分，第一部分主要基于何登成大神的文章。何博士作为阿里数据库内核团队大神。文章更是深入浅出。膜拜一下：原文地址如下  http://hedengcheng.com/?p=771    第二部分介绍常见的实践注意事项。 二 背景   MVCC：Snapshot Read vs Current Read        MySQL InnoDB存储引擎，实现的是基于...
mysql加锁分析_v1
01-25
### MySQL/InnoDB 加锁分析 #### 背景与重要性 MySQL作为一款广泛使用的开源关系型数据库管理系统，其核心存储引擎InnoDB对于保证数据的一致性和事务处理能力至关重要。尤其是在高并发环境下，如何有效地管理锁...
4.5 mysql 加锁分析.pdf
05-19
本文旨在深入探讨MySQL/InnoDB的加锁原理及其应用场景，帮助读者更好地理解和掌握如何分析任意SQL语句的加锁行为及其可能带来的风险，甚至能够诊断线上出现的死锁现象。 由于MySQL支持多种存储引擎，本文讨论的内容...
大厂基本功 | MySQL 三大日志 ( binlog、redo log 和 undo log ) 的作用？
langjiaohjiopji的博客
11-06 1205
MySQL日志 主要包括错误日志、查询日志、慢查询日志、事务日志、二进制日志几大类。其中，比较重要的还要属二进制日志binlog（归档日志）和事务日志redo log（重做日志）和undo log（回滚日志）。今天就来聊聊redo log（重做日志）、binlog（归档日志）、两阶段提交、undo log（回滚日志）。MySQL InnoDB 引擎使用redo log(重做日志)保证事务的持久性，使用undo log(回滚日志)来保证事务的原子性。MySQL数据库的数据备份、主备、主主、主从都离不开。
mysql 配置文件 my.cnf 增加 lower_case_table_names = 1 服务启动不了的原因
最新发布
BillKu的博客
11-10 185
只是简单的增加一行配置信息：lower_case_table_names = 1。
ReactPress 安装指南：从 MySQL 安装到项目启动
小牛itbull的博客
11-10 414
ReactPress 是一个基于 React 的开源发布平台，适用于搭建博客、网站或内容管理系统（CMS）。本文将详细介绍如何安装 ReactPress，包括 MySQL 数据库的安装、ReactPress 项目的配置与启动，并特别指出使用 pnpm 而不是 yarn 作为包管理工具。
ubuntu 22.04 server 安装 mysql 5.7.40 LTS
abc
11-06 509
ubuntu 22.04 server 安装 mysql 5.7.40 LTS。
MySQL 索引
果酱 の 博客
11-08 831
MySQL 索引是提高数据库性能的重要手段。通过选择合适的索引类型、创建必要的索引、定期维护索引和监控索引使用情况，可以大大提高数据库的查询性能。深入理解 MySQL 索引的原理和使用方法，对于构建高效的数据库应用至关重要。
Cleared-MySQL （上）
Mr.W的博客
11-10 585
所有字段都是不可再分的原子值。
Linux（CentOS）安装 MySQL
BillKu的博客
11-06 327
MySQL版本：MySQL Community Server 8.4.3 LTS。CentOS版本：CentOS 7。
深入解析MySQL InnoDB加锁机制：理论与实战
MySQL/InnoDB加锁分析是IT领域的关键技术，尤其是在高并发环境下，理解数据库锁机制对于避免性能瓶颈、死锁等问题至关重要。本文旨在深入探讨MySQL的加锁原理和策略，为开发者提供一种实用的分析方法。作者基于自己...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值