MySQL Innodb Insert语句加锁流程

已于 2023-02-05 16:49:56 修改
阅读量1.1k 收藏 5
点赞数
分类专栏： Mysql 文章标签： mysql 数据库
于 2023-01-10 23:39:31 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_42002747/article/details/128636306
版权

MySQL版本：8.0.29
测试表：
在这里插入图片描述

测试数据：
在这里插入图片描述

事务A先执行：
在这里插入图片描述

加锁分析：

mysql> SELECT * FROM performance_schema.data_locks\G
*************************** 1. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 140346328944544:1066:140346432670032
ENGINE_TRANSACTION_ID: 3957
            THREAD_ID: 75
             EVENT_ID: 32
        OBJECT_SCHEMA: db_study
          OBJECT_NAME: user
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: NULL
OBJECT_INSTANCE_BEGIN: 140346432670032
            LOCK_TYPE: TABLE
            LOCK_MODE: IX
          LOCK_STATUS: GRANTED
            LOCK_DATA: NULL
1 row in set (0.00 sec)

事务B执行：
在这里插入图片描述

加锁分析：

mysql> SELECT * FROM performance_schema.data_locks\G
*************************** 1. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 140346328944544:1066:140346432670032
ENGINE_TRANSACTION_ID: 3957
            THREAD_ID: 75
             EVENT_ID: 32
        OBJECT_SCHEMA: db_study
          OBJECT_NAME: user
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: NULL
OBJECT_INSTANCE_BEGIN: 140346432670032
            LOCK_TYPE: TABLE
            LOCK_MODE: IX
          LOCK_STATUS: GRANTED
            LOCK_DATA: NULL
*************************** 2. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 140346328944544:5:5:5:140346462116384
ENGINE_TRANSACTION_ID: 3957
            THREAD_ID: 76
             EVENT_ID: 36
        OBJECT_SCHEMA: db_study
          OBJECT_NAME: user
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: age
OBJECT_INSTANCE_BEGIN: 140346462116384
            LOCK_TYPE: RECORD
            LOCK_MODE: X,REC_NOT_GAP
          LOCK_STATUS: GRANTED
            LOCK_DATA: 96, 40
*************************** 3. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 140346328945336:1066:140346432671984
ENGINE_TRANSACTION_ID: 421821305655992
            THREAD_ID: 76
             EVENT_ID: 36
        OBJECT_SCHEMA: db_study
          OBJECT_NAME: user
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: NULL
OBJECT_INSTANCE_BEGIN: 140346432671984
            LOCK_TYPE: TABLE
            LOCK_MODE: IS
          LOCK_STATUS: GRANTED
            LOCK_DATA: NULL
*************************** 4. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 140346328945336:5:5:5:140346462120992
ENGINE_TRANSACTION_ID: 421821305655992
            THREAD_ID: 76
             EVENT_ID: 36
        OBJECT_SCHEMA: db_study
          OBJECT_NAME: user
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: age
OBJECT_INSTANCE_BEGIN: 140346462120992
            LOCK_TYPE: RECORD
            LOCK_MODE: S
          LOCK_STATUS: WAITING
            LOCK_DATA: 96, 40
4 rows in set (0.00 sec)

事务A持有锁：
1）表user的意向排他锁
2）age索引：记录（96，40）的排他锁

事务B持有锁：
1）表user的意向共享锁
2）age索引：范围（89，96]的next-key lock

总结：
事务B想要获取age索引：范围（89，96]的next-key lock，
但是因为事务A已经拥有了age索引：记录（96，40）的排他锁，所以需要阻塞。
仔细思考，正常来说事务B想要当前读age=96的所有记录，
光是加这些锁不够，所以恢复运行后，加的锁又是不一样了，看下面。

事务A提交事务
在这里插入图片描述
事务B就可以查询到结果了
在这里插入图片描述
加锁分析：

mysql> SELECT * FROM performance_schema.data_locks\G
*************************** 1. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 140346328945336:1066:140346432671984
ENGINE_TRANSACTION_ID: 3962
            THREAD_ID: 76
             EVENT_ID: 36
        OBJECT_SCHEMA: db_study
          OBJECT_NAME: user
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: NULL
OBJECT_INSTANCE_BEGIN: 140346432671984
            LOCK_TYPE: TABLE
            LOCK_MODE: IS
          LOCK_STATUS: GRANTED
            LOCK_DATA: NULL
*************************** 2. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 140346328945336:5:5:5:140346462121336
ENGINE_TRANSACTION_ID: 3962
            THREAD_ID: 76
             EVENT_ID: 37
        OBJECT_SCHEMA: db_study
          OBJECT_NAME: user
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: age
OBJECT_INSTANCE_BEGIN: 140346462121336
            LOCK_TYPE: RECORD
            LOCK_MODE: S
          LOCK_STATUS: GRANTED
            LOCK_DATA: 96, 40
*************************** 3. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 140346328945336:5:4:5:140346462121680
ENGINE_TRANSACTION_ID: 3962
            THREAD_ID: 76
             EVENT_ID: 37
        OBJECT_SCHEMA: db_study
          OBJECT_NAME: user
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: PRIMARY
OBJECT_INSTANCE_BEGIN: 140346462121680
            LOCK_TYPE: RECORD
            LOCK_MODE: S,REC_NOT_GAP
          LOCK_STATUS: GRANTED
            LOCK_DATA: 40
*************************** 4. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 140346328945336:5:5:2:140346462122024
ENGINE_TRANSACTION_ID: 3962
            THREAD_ID: 76
             EVENT_ID: 37
        OBJECT_SCHEMA: db_study
          OBJECT_NAME: user
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: age
OBJECT_INSTANCE_BEGIN: 140346462122024
            LOCK_TYPE: RECORD
            LOCK_MODE: S,GAP
          LOCK_STATUS: GRANTED
            LOCK_DATA: 99, 10
4 rows in set (0.00 sec)

事务B持有的锁：
1）表user的意向共享锁
2）age索引：范围（89，96]的next-key lock
3）主键索引：id=40的行共享锁
4）age索引：范围（96，99）的gap lock

总结：
事务B刚开始获取的锁只是一个标记（阻塞在那里，
等着事务A释放行锁后恢复执行，因为行锁的获取单位的next-key lock，
记录锁和间隙锁是next-key lock的降级），恢复执行后，
需要重新上面这些锁，保证当前读

小结：insert如果没有并发，是不加锁，但底层会通过page latch保证并发修改同一page的线程安全。比如事务A要在某个间隙插入一条记录a，先会获取page x latch,然后插入数据（修改数据页），然后释放page x latch，还没有提交事务，此时事务B需要查询这个间隙中的数据，先获取page s latch,然后查询到记录a，通过记录a的隐藏字段发现这个记录是还没有提交的，所以给这个记录对应的事务加上行记录X锁，自己进入到这把锁的阻塞队列中，说白就是这个间隙有其他事务在操作，当前事务不能查询，需要先阻塞，等到前面的事务提交后才能继续查询，如果是select lock in share mode 进入S锁阻塞队列，如果是select for update 进入到X锁阻塞队列。
如果insert所在的事务获取page x latch后，发现这个间隙存在gap锁，会生成插入意向锁(插入意向锁是兼容的，本质上也是gap锁)然后阻塞，直到前面的gap锁释放

通过 LOCK_MODE 可以确认是 next-key 锁，还是间隙锁，还是记录锁：
如果 LOCK_MODE 为 X，说明是 next-key 锁；
如果 LOCK_MODE 为 X, REC_NOT_GAP，说明是记录锁；
如果 LOCK_MODE 为 X, GAP，说明是间隙锁；

梦江河
关注
  • 0
    点赞
  • 踩
  • 5
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
Innodb加锁机制（隐式锁）
sunashe的个人随笔
09-13 2738
隐式锁 简介 详解隐式锁 隐式锁加锁流程 判断是否存在隐式锁 隐式锁转换 隐式锁转换在5.7中的优化 相关博客地址 隐式锁 简介 Innodb采用乐观插入的方式，所以在做insert操作时不会进行显示加锁，也就是不会生成lock_t锁结构，这样可以极大的减少锁开销，提升整体的性能。 如果没有显示的行锁，该如何保证事务插入的正确性呢？比如说如下两个事务，插入相同的...
MySQL中insert语句的使用与优化教程
09-10
在MySQL中，`INSERT INTO`语句用于向数据库表中插入新的记录。它是数据库操作的基础，特别是在处理数据存储和更新的场景中。本教程将详细讲解`INSERT INTO`语句的使用方法及其优化技巧。 首先，`INSERT INTO`语句的...
认真学习MySQL中锁机制（一）
小小默：进无止境
11-28 9648
MySQL的InnoDB锁机制分为表级锁和行级锁，官网文档：[点击查看](https://dev.mysql.com/doc/refman/8.0/en/innodb-locking.html) # 【1】有哪些锁？ ## ① 按照锁的粒度划分 在关系型数据库中，可以按照锁的粒度把数据库锁分为行级锁(INNODB引擎)、表级锁(MYISAM引擎)和页级锁(BDB引擎 )。 MyISAM和InnoDB存储引擎使用的锁： * MyISAM采用表级锁(table-level locking)。 * Inno
mysql insert手动加锁_mysql insert 加锁流程
weixin_42349182的博客
01-19 487
INSERT sets an exclusive lock on the inserted row. This lock is an index-record lock, not a next-key lock (that is, there is no gap lock) and does not prevent other sessions from inserting into the ga...
【史上最全】MySQL各种锁详解：一文搞懂MySQL的各种锁
最新发布
木牛的博客
05-29 1161
隐式上锁（默认，自动加锁自动释放）显式上锁（手动）解锁（手动）1. 提交事务（commit）2. 回滚事务（rollback）3. kill 阻塞进程为什么上了写锁，别的事务还可以读操作？因为InnoD‍B有，可以使用快照读，而不会被阻塞。单个行记录上的锁（点查）Record Lock总是会去锁住索引记录，如果InnoDB存储引擎表建立的时候没有设置任何一个索引，这时InnoDB存储引擎会使用隐式的主键来进行锁定。
MySQL 中的 INSERT 是怎么加锁的？（荣耀典藏版）
热门推荐
人生若只初相见@的博客
07-27 1万+
今天分享一下MySQL 中的 INSERT 是怎么加锁的
InnoDB 锁
这个人很懒什么都没有~
10-23 289
因为其不是根据每个记录来产生行锁的，相反，其根据每个事务访问的每个页对锁进行管理的，采用的是位图的方式。那么锁范围共(-∞,1]，(1,2]，(2,5]，(5,+∞]共4个锁区间，而会话A的where条件会落在(2,5]这个区间，会话B的insert刚好在锁区间内。这里不能使用一致性非锁定读的原因可以用反证法，如果默认RC下会话B读取父表时读的事务开始时的一个快照备份，那么在会话A执行删除语句又不能阻止会话B的读取，最后AB会话都提交了，那么数据就不一致了相当于这个id=3的外键约束就失效了。
mysql数据库Insert语句后面加ON DUPLICATE KEY UPDATE，保证唯一性1
08-03
当你在`INSERT`语句后添加`ON DUPLICATE KEY UPDATE`，如果新插入的行违反了唯一性约束，MySQL将执行指定的`UPDATE`操作。这里的“唯一性”指的是任何具有唯一索引或主键的列。例如，如果你有一个名为`consume_locus...
MySQL InnoDB中的锁机制深入讲解
09-09
MySQL InnoDB 存储引擎的锁机制是数据库并发控制的关键技术，它确保了在多用户环境中数据的一致性和完整性。本文将深入探讨InnoDB的锁类型、锁的使用以及其在处理并发操作时的角色。 首先，InnoDB支持两种类型的锁...
MySQL新特性 For innodb 8.0
05-19
MySQL 8.0 版本对InnoDB存储引擎进行了大量改进和优化，为数据库管理员和开发者带来了诸多新特性。以下是一些主要的InnoDB新特性介绍： 1. **Columnar Storage (InnoDB Clustered Index)**: MySQL 8.0 引入了列式...
MySQL 中的 INSERT 是怎么加锁的？
当我谈编程时我谈些什么
05-19 2196
程序员的成长之路 互联网/程序员/技术/资料共享 关注 阅读本文大概需要 12分钟。 来自：https://www.aneasystone.com/archives/2018/06/insert-locks-via-mysql-source-code.html 看到热心网友在评论中提出的一个问题，我还是彻底被问蒙了。 他的问题是这样的： 加了插入意向锁后，插入数据之前，此时执行了 select...lock in share mode 语句（没有取到待插入的值），然后插入了数据，下一...
Mysql锁机制 - 各种SQL语句的加锁方式
qq_39679639的博客
10-22 2347
各种SQL的加锁方式
insert 语句加锁机制
weixin_44476888的博客
05-03 5100
一 前言 之前的文章里面总结了很多死锁案例，其实里面有几篇文章对于insert加锁流程表述的不准确，而且微信公众号又无法修改，所以通过本文重新梳理insert加锁流程，最后加上一个死锁案例解析。 有个勤奋好学的同事特地找我咨询insert并发导致死锁的问题，我不方便说他的名字，就叫他鲁震宇吧，本文也是为这位开发小哥哥写的。要是还看不懂，可以再来咨询我。T_T 二 基础知识 在分析死锁案例之前，我...
mysql之间隙锁
qq_41820066的博客
11-14 534
MySQL之间隙锁
细说Mysql间隙锁
蓝天⊙白云的博客
09-05 789
1.什么是间隙锁？间隙锁是怎样产生的？2.间隙锁有什么作用？3.使用间隙锁有什么隐患？
MySQL的锁机制 - 记录锁、间隙锁、临键锁
qq_34709713的博客
04-15 1081
记录锁(Record Locks) 记录锁是 封锁记录，记录锁也叫行锁，例如： SELECT * FROM `test` WHERE `id`=1 FOR UPDATE; 它会在 id=1 的记录上加上记录锁，以阻止其他事务插入，更新，删除 id=1 这一行。 记录锁、间隙锁、临键锁都是排它锁，而记录锁的使用方法跟之前的一篇文章 共享/排它锁 里的排它锁介绍一致，这里就不详细多讲。 间隙锁(Ga...
推荐　mysql insert 加锁流程
大树叶 技术专栏
08-08 3491
INSERT sets an exclusive lock on the inserted row. This lock is an index-record lock, not a next-key lock (that is, there is no gap lock) and does not prevent other sessions from inserting into the ga
死锁场景之insert语句加锁流程分析（二）
weixin_42272869的博客
12-27 1327
死锁场景之insert语句加锁分析（二） 关于mysql有哪些锁？锁的分类有哪些？可查看另外一篇博客：mysql 的锁 在实际情景中的加锁情况分析博客请查看另外一篇博客：数据库操作加锁情况分析 关于数据库的死锁日志如何查看？ 请查看另外一篇博客：死锁日志查看 关于数据库的insert导致死锁案例查看：死锁场景之insert语句死锁分析 这篇主要是介绍平时常见的insert语句的加锁情况。 大部分的DBA同行都知道在事务执行insert的时候会申请一把插入意向锁(Insert Intention Lock)。
《转》Mysql加锁过程详解（1）-基本知识
11-29 647
1、背景 　　MySQL/InnoDB的加锁分析，一直是一个比较困难的话题。我在工作过程中，经常会有同事咨询这方面的问题。同时，微博上也经常会收到MySQL锁相关的私信，让我帮助解决一些死锁的问题。本文，准备就MySQL/InnoDB的加锁问题，展开较为深入的分析与讨论，主要是介绍一种思路，运用此思路，拿到任何一条SQL语句，都能完整的分析出这条语句会加什么锁？会有什么样的使用风险？甚至是分析线上的一个死锁场景，了解死锁产生的原因。 注：MySQL是一个支持插件式存储引擎的数据库系统。本文下面的所有介绍
mysql什么语句会加锁？
05-12
在 MySQL 中，下列语句会加锁： 1. SELECT ... FOR UPDATE：该语句会锁定查询结果集中的所有行，直到事务提交或回滚。 2. UPDATE：该语句会锁定要更新的行，直到事务提交或回滚。 3. INSERT：如果使用了 InnoDB 存储引擎并且表中有主键或唯一索引，则会锁定要插入的行，直到事务提交或回滚。 4. DELETE：该语句会锁定要删除的行，直到事务提交或回滚。 需要注意的是，锁定行的操作可能会影响并发性和性能，因此需要谨慎使用锁定语句。

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值