MySQL-InnoDB锁

于 2023-05-31 22:38:18 发布
阅读量478 收藏
点赞数
CC 4.0 BY-SA版权
分类专栏： MySql 文章标签： mysql 数据库
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/qq_35374224/article/details/130978468
本文详细介绍了MySQL-InnoDB的锁机制，包括全局锁、表级锁（如表锁、元数据锁MDDL）、行锁（如Record Lock、Gap Lock、Next-Key Lock）以及插入意向锁。通过实例展示了不同锁在不同场景下的应用，帮助读者理解事务隔离级别下的锁行为，解决线上SQL阻塞和死锁问题。

摘要生成于 C知道 ，由 DeepSeek-R1 满血版支持， 前往体验 >

MySQL-InnoDB 锁

有没有过以下这样的经历：

  1. 线上SQL执行，事务阻塞时间过长，不知道问题在哪里？
  2. 线上发生死锁，看死锁日志也要挠头皮很久才明白？
  3. 事务的隔离机制究竟怎么实现的？锁有哪些？怎么加的锁？

希望你在看完这篇文章后，可以有一些收获。话不多说，开整。

数据库版本

8.0.33

image-20230531180020686

锁的种类

全局锁

flush tables with read lock

全局加入读锁后

image-20230531182034626

尝试读取数据

image-20230531182342472

读取成功；

尝试更改数据

image-20230531182443480

发生了阻塞，由此可见，全局读锁与写操作互斥

使用unlock tables解锁

表级锁
表锁
//表级别的共享锁
lock tables test1 read;

image-20230531191816173

修改也是会阻塞

//表级别的独占锁
lock tables test1 write;

image-20230531191951397

在加了共享锁之后，再向同一张表上加独占锁，也会阻塞，由此可知：读读兼容，读写互斥。

现在我先加表级别的独占锁

image-20230531192144188

再去查询数据，也会阻塞，由此可知无论读写操作的先后顺序，都会互斥

image-20230531192214121

//解锁
unlock tables
元数据锁(MDL)

MDL锁不需要显式调用，一般伴随着sql的执行，就会加上对应的锁

8.0比5.7，MDL锁类别更多了，包含了：

  • 共享锁（Shared Lock）：允许多个事务同时读取元数据，但不允许任何事务进行修改或删除该元数据，直到所有的共享锁被释放。
  • 排它锁（Exclusive Lock）：防止其他事务对元数据进行读取和写入操作，直到锁被释放。
  • 共享升级锁（Shared Upgradable Lock）：允许多个事务同时持有该锁，但是如果有事务想要升级为排它锁，则必须等待所有其他持有该锁的事务释放该锁。
  • 共享读锁（Shared Read Lock）：允许多个事务同时读取同一行数据，但不允许任何事务对该行数据进行修改，直到所有的共享读锁被释放。
  • 排它写锁（Exclusive Write Lock）：防止其他事务对同一行数据进行读取或写入操作，直到锁被释放。

这里先只对共享锁和排他锁做说明，其他三个还没有搞明白，就不误人子弟了。

共享锁（Shared Lock）

对于表数据的curd（无论是select还是update），是MDL读锁

下图是一个普通select查询：

image-20230531201146383

下图是元数据锁信息：LOCK_TYPE：SHARED_READ

image-20230531201218151

下图是update语句：

image-20230531201416972

下图是元数据锁信息，发现是SHARED_WRITE锁，也是SHARED锁的一种，

image-20230531202916991

同时select和update，MDL的SHARED锁是不会冲突的

image-20230531203107973

排它锁（Exclusive Lock）

这里的排它锁其实就相当于5.7的MDL写锁，只有对表的元数据进行修改时，才会加锁。

如下图，需要先加读锁，再进行修改元数据，才能捕捉到排他锁

image-20230531204914203

image-20230531204950057

其实会发现中间还夹带着一个Shared Upgradable锁

读写锁互斥，同时只会在事务提交后才释放。

如果MDL写锁因某些原因阻塞，那么之后进来的MDL读锁也就是CURD，都会阻塞，这是因为MDL相关的锁会形成一个队列，后续进来的CURD，也就是MDL读锁，需要等待前面的写锁完成。

可以看到下图，select查询阻塞了，因为前面有一个MDL写锁在等待，就会导致后续的读锁都需要等待。

image-20230531205229853

举例说明：

时刻1：A线程开启事务，读取数据，此时加的MDL读锁；

时刻2：B线程开启事务，读取数据，不会阻塞，因为也是MDL读锁；

时刻3：C线程修改了表结构，由于A事务未提交，所以C线程需要等待前面的MDL读锁释放；

时刻4：D线程或者其他多数线程，读取数据，此时加的MDL读锁，但是因为前面有C线程的MDL写锁等待，所以时刻4的线程都需要等待。

意向锁

意向锁分为：意向独占锁，意向共享锁

如下图，是一个加了共享锁的查询

image-20230531210318244

这里只看表锁，是IS锁，也就是意向共享锁，下面其实还有行锁，此处先不做说明

image-20230531210414497

我现在加表锁，可以看到被阻塞住了

image-20230531210606332

说明意向锁是面向表级别的，当表中某些数据要加独占锁或者共享锁时，需要先对这个表加对应的意向锁。

意向锁与行锁不互斥，二者没有关系，与表锁互斥，意向锁的作用是在：当要给某张表加表锁时，快速判断表中是否有记录被加锁。

自增主键锁

是特殊的一种表锁，用于自增主键；

InnoDB中有一个相关配置项：innodb_autoinc_lock_mode

  • innodb_autoinc_lock_mode=0时，就采用该锁，语句执行完之后才会释放，性能不好
  • innodb_autoinc_lock_mode=1时：
    • 普通insert语句在申请完主键后就释放，不用等执行完
    • 批量插入还是需要等执行完才释放
  • innodb_autoinc_lock_mode=2时，采取轻量级锁，申请完主键就释放

innodb_autoinc_lock_mode=2时，需要配合binlog的row格式使用，因为statement格式可能会导致主备数据不一致。

一个事务A内多个insert执行时，如果有其他事务也在insert，那么可能导致事务A内的数据id不连贯，但是binlog是以事务为单位记录的，statement的命令同步到备库后，反而会生成连续的id，与主库不一致。

行锁
Record Lock

记录锁，共享锁满足读读共享，读写互斥；独占锁满足读写互斥，写写互斥；

事务提交后才会释放

Gap Lock

间隙锁，锁住一个范围，开区间。间隙锁读写之间兼容，因为作用都是负责防止插入引起的幻读。

Next-Key Lock

临键锁，是上面两种的合体；前开后闭，涉及到的Record Lock锁同样满足共享锁满足读读共享，读写互斥；独占锁满足读写互斥，写写互斥；

统一说明

以RR级别说明，加锁的基本单位是Next-Key Lock，根据字段的属性、索引的类型，有可能退化成Gap Lock或者Record Lock，扫描到的且符合条件的记录都会进行加锁

  • 普通索引-等值查询-存在记录

image-20230531211008324

加锁示意图：

image-20230531212114216

通过前面三个行锁类型的说明，可知这个图的锁范围是：

  • age：(1,2] (2,2] (2,4)
  • id: 1,2

这样子加锁可以禁止幻读，因为区间也锁住了，所以对于当前读来说，锁是禁止幻读的必要手段。

锁信息:只截取部分字段

*************************** 1. row ***************************IX意向独占锁
           INDEX_NAME: NULL
            LOCK_TYPE: TABLE
            LOCK_MODE: IX
          LOCK_STATUS: GRANTED
            LOCK_DATA: NULL
*************************** 2. row ***************************
## 行锁中的Next-Key Lock：独占锁，锁范围是二级索引age：(1,2] 
## LOCK_DATA: 2, 1，前面的2是锁的闭区间节点，后面的1是当前二级索引对应的主键索引
           INDEX_NAME: test_age
            LOCK_TYPE: RECORD
            LOCK_MODE: X
          LOCK_STATUS: GRANTED
            LOCK_DATA: 2, 1
*************************** 3. row ***************************
## 行锁中的Next-Key Lock：独占锁，锁范围是二级索引age：(2,2] 
           INDEX_NAME: test_age
            LOCK_TYPE: RECORD
            LOCK_MODE: X
          LOCK_STATUS: GRANTED
            LOCK_DATA: 2, 2
*************************** 4. row ***************************  
## 行锁中的Record Lock：独占锁，锁范围是主键id：1
           INDEX_NAME: PRIMARY
            LOCK_TYPE: RECORD
            LOCK_MODE: X,REC_NOT_GAP ## REC_NOT_GAP指的是非间隙锁
          LOCK_STATUS: GRANTED
            LOCK_DATA: 1
*************************** 5. row ***************************
## 行锁中的Record Lock：独占锁，锁范围是主键id：2
           INDEX_NAME: PRIMARY
            LOCK_TYPE: RECORD
            LOCK_MODE: X,REC_NOT_GAP
          LOCK_STATUS: GRANTED
            LOCK_DATA: 2
*************************** 6. row ***************************
## 行锁中的Gap Lock：独占锁，锁范围是二级索引age：(2,4)
           INDEX_NAME: test_age
            LOCK_TYPE: RECORD
            LOCK_MODE: X,GAP
          LOCK_STATUS: GRANTED
            LOCK_DATA: 4, 4
6 rows in set (0.00 sec)
  • 普通索引-等值查询-记录不存在

image-20230531213702594

加锁示意图：

image-20230531213806014

这里查询的是age=3，在原数据中处于2-4之间，并且数据不存在，那么Next-Key Lock会退化成Gap Lock，锁住age索引的(2,4)区间，因为记录不存在，所以不会对主键加锁。

*************************** 1. row ***************************
 INDEX_NAME: NULL
  LOCK_TYPE: TABLE
  LOCK_MODE: IX
LOCK_STATUS: GRANTED
  LOCK_DATA: NULL
*************************** 2. row ***************************
## 行锁中的Gap Lock：独占锁，锁范围是二级索引age：(2,4)
 INDEX_NAME: test_age
  LOCK_TYPE: RECORD
  LOCK_MODE: X,GAP
LOCK_STATUS: GRANTED
  LOCK_DATA: 4, 4
2 rows in set (0.00 sec)
  • 普通索引-范围查询

image-20230531214327797

加锁示意图

image-20230531214306670

这里查询的age>3，会查询到age=4的记录，所以会对age=4加Next-Key Lock，锁范围是(2,4]，可能有的小伙伴会有疑问，为什么是这个范围，因为加锁都是依托于索引，而现在索引上没有age=3的数据，并且这里仅仅是锁的范围。

mysql> begin;
Query OK, 0 rows affected (0.00 sec)
## 我这里将id=3 age=1的记录，修改成age=3，其实修改失败，出现了锁等待，因为修改之后的数据和(2,4]冲突了
mysql> update test1 set age=3 where id=3;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

然后因为是范围查询，对于数据库来说，后续插入大于4的数据是有可能的，那么也需要将后续数据锁上，但是此时数据还没有新增进来，锁记录加在哪里呢？其实索引会维护一条特殊记录：supremum pseudo-record，可以把它看作正无穷：+∞，所以它的加锁范围就是(4，+∞]

*************************** 1. row ***************************
 INDEX_NAME: NULL
  LOCK_TYPE: TABLE
  LOCK_MODE: IX
LOCK_STATUS: GRANTED
  LOCK_DATA: NULL
*************************** 2. row ***************************
## Next-Key Lock (4，+∞]
 INDEX_NAME: test_age
  LOCK_TYPE: RECORD
  LOCK_MODE: X
LOCK_STATUS: GRANTED
  LOCK_DATA: supremum pseudo-record
*************************** 3. row ***************************
## Next-Key Lock (2,4]
 INDEX_NAME: test_age
  LOCK_TYPE: RECORD
  LOCK_MODE: X
LOCK_STATUS: GRANTED
  LOCK_DATA: 4, 4
*************************** 4. row ***************************
## 主键id的锁范围 ：4
 INDEX_NAME: PRIMARY
  LOCK_TYPE: RECORD
  LOCK_MODE: X,REC_NOT_GAP
LOCK_STATUS: GRANTED
  LOCK_DATA: 4
4 rows in set (0.00 sec)
  • 唯一索引-等值查询-存在记录

唯一索引就以主键举例

image-20230531215836613

加锁示意图：

image-20230531220348822

唯一索引的等值查询加锁其实很好理解，因为数据存在且唯一，所以会退化成Record Lock锁

*************************** 1. row ***************************
 INDEX_NAME: NULL
  LOCK_TYPE: TABLE
  LOCK_MODE: IX
LOCK_STATUS: GRANTED
  LOCK_DATA: NULL
*************************** 2. row ***************************
## 主键id Record Lock 加锁范围是1
 INDEX_NAME: PRIMARY
  LOCK_TYPE: RECORD
  LOCK_MODE: X,REC_NOT_GAP
LOCK_STATUS: GRANTED
  LOCK_DATA: 1
2 rows in set (0.00 sec)
  • 唯一索引-等值查询-记录不存在

image-20230531220636822

加锁示意图

image-20230531221222934

如果查询条件未在已存在的数据记录中，会锁(4，+∞]，

如果查询条件在已存在的数据记录之间，会退化成Gap Lock。这里需要注意

*************************** 1. row ***************************
 INDEX_NAME: NULL
  LOCK_TYPE: TABLE
  LOCK_MODE: IX
LOCK_STATUS: GRANTED
  LOCK_DATA: NULL
*************************** 2. row ***************************
 INDEX_NAME: PRIMARY
  LOCK_TYPE: RECORD
  LOCK_MODE: X
LOCK_STATUS: GRANTED
  LOCK_DATA: supremum pseudo-record
2 rows in set (0.00 sec)
  • 唯一索引-范围查询

image-20230531220846213

加锁示意图

image-20230531222206573

因为查询条件是范围查询，所以会扫描多行记录，那么扫描到的符合条件的记录都会加锁，从4开始锁，那么加锁的基本单位是Next Key Lock，所以第一个范围是(3,4]，以此类推 (4,+∞]

*************************** 1. row ***************************
 INDEX_NAME: NULL
  LOCK_TYPE: TABLE
  LOCK_MODE: IX
LOCK_STATUS: GRANTED
  LOCK_DATA: NULL
*************************** 2. row ***************************
 INDEX_NAME: PRIMARY
  LOCK_TYPE: RECORD
  LOCK_MODE: X
LOCK_STATUS: GRANTED
  LOCK_DATA: supremum pseudo-record
*************************** 3. row ***************************
 INDEX_NAME: PRIMARY
  LOCK_TYPE: RECORD
  LOCK_MODE: X
LOCK_STATUS: GRANTED
  LOCK_DATA: 4
3 rows in set (0.00 sec)
插入意向锁

一种比较特殊的行锁

以下面sql为例

image-20230531220846213

image-20230531222206573

*************************** 1. row ***************************
 INDEX_NAME: NULL
  LOCK_TYPE: TABLE
  LOCK_MODE: IX
LOCK_STATUS: GRANTED
  LOCK_DATA: NULL
*************************** 2. row ***************************
 INDEX_NAME: PRIMARY
  LOCK_TYPE: RECORD
  LOCK_MODE: X
LOCK_STATUS: GRANTED
  LOCK_DATA: supremum pseudo-record
*************************** 3. row ***************************
 INDEX_NAME: PRIMARY
  LOCK_TYPE: RECORD
  LOCK_MODE: X
LOCK_STATUS: GRANTED
  LOCK_DATA: 4
3 rows in set (0.00 sec)

此时我执行一个插入操作，插入id=5的记录

锁信息多出来了两条：一个是IX（表级的意向锁），一个是X,INSERT_INTENTION（也就是插入意向锁）

其实可以把它看作一个标识，标识当前间隙有事务尝试插入数据，锁状态是WAITING，代表还没有获取到锁，只是一个等待状态。

*************************** 1. row ***************************
 INDEX_NAME: NULL
  LOCK_TYPE: TABLE
  LOCK_MODE: IX
LOCK_STATUS: GRANTED
  LOCK_DATA: NULL
*************************** 2. row ***************************
 INDEX_NAME: PRIMARY
  LOCK_TYPE: RECORD
  LOCK_MODE: X,INSERT_INTENTION
LOCK_STATUS: WAITING
  LOCK_DATA: supremum pseudo-record
*************************** 3. row ***************************
 INDEX_NAME: NULL
  LOCK_TYPE: TABLE
  LOCK_MODE: IX
LOCK_STATUS: GRANTED
  LOCK_DATA: NULL
*************************** 4. row ***************************
 INDEX_NAME: PRIMARY
  LOCK_TYPE: RECORD
  LOCK_MODE: X
LOCK_STATUS: GRANTED
  LOCK_DATA: supremum pseudo-record
*************************** 5. row ***************************
 INDEX_NAME: PRIMARY
  LOCK_TYPE: RECORD
  LOCK_MODE: X
LOCK_STATUS: GRANTED
  LOCK_DATA: 4
5 rows in set (0.00 sec)
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值