【mysql】自增锁

目录

mysql 的自增锁

三种插入模式

1-简单插入(Simple Inserts)

2-批量插入模式(Bulk Inserts)

3-混合插入模式(Mixed-mode insert)

自增锁机制

1-AUTO-INC-LOCKING 机制

2-MUTEX 机制

关于自增值的不连续

1-为什么 MUTEX 会导致自增值不连续？

2-不论哪种机制都会产生自增值的间隙

配置自增策略innodb_autoinc_lock_mode

---

mysql 的自增锁

自增锁是一种表级锁，在使用自增主键（AUTO_INCREMENT）时，为了保证数据唯一性，在插入数据时，InnoDB 会对自增字段进行加锁，以确保并发情况下每条记录都能够获得唯一的自增值。如事务 A 正在插入数据，则另一个事务 B 尝试 INSERT 语句，B 会被阻塞住，直至 A 释放自增锁。

并且在 InnoDB 存储引擎下，自增列必须是索引且是索引的第一个列，否则会报错。

三种插入模式

1-简单插入(Simple Inserts)

指的是在插入前就能确定插入行数的语句，如普通的 insert into语句、replace into语句：

INSERT INTO t1 (id, name) VALUES (1, 'a'), (2, 'b');
REPLACE INTO t2 (id, name) VALUES (1, 'a');

2-批量插入模式(Bulk Inserts)

指的是在 插入前不能确定插入行数的语句， 包如 insert…select、replace select、load data语句：

INSERT INTO t2 SELECT * FROM t1;
REPLACE INTO t2 SELECT * FROM t1;

3-混合插入模式(Mixed-mode insert)

插入的数据中有一部分值是自增长的，有一部分值则是确定的：

INSERT INTO t1 (id,name) VALUES (1, 'a'), (null, 'b'),(5, 'c');

自增锁机制

1-AUTO-INC-LOCKING 机制

每次获取自增值时都需要加一个 表级别的 X 锁：

SELECT MAX(id) FROM t1 FOR UPDATE;

性能差，并发插入操作需要等待锁的释放，成为批量并发插入的性能瓶颈。

2-MUTEX 机制

在内存中维护一个 mutex 数据结构（通过 latch 限制并发访问），作为自增长计数器。

也就是当一个事务请求新的自增 ID 时，要先获取 Mutex 锁，获取后占用一个自增 ID，再将锁释放。被释放后，另一个事务才可以获取 Mutex 锁并请求下一个自增ID。

这种基于内存的方式不必对数据库进行锁操作，非常轻便高效，性能好，但可能存在自增长值不连续问题。

关于自增值的不连续

1-为什么 MUTEX 会导致自增值不连续？

由于 Mutex 锁的存在，事务之间会交替获取 Mutex 锁来请求自增 ID，尤其是在不确定插入行数的 bulk inserts 操作情况下，这种交替获取锁的行为就会导致 ID 不连续。

例如：

事务 A 和事务 B 都请求自增 ID，A 先获取锁并请求 ID 1001，接着 B 获取锁请求 ID 1002，如果 A 再次请求 ID 1003，B 又请求 ID 1004，那么这两个事务之间就会交错地占用自增值，导致 ID 不连续。

2-不论哪种机制都会产生自增值的间隙

不论是 AUTO-INC LOCKING 还是 MUTEX 机制，都可能出现自增值有间隙的情况。

比如，在一个事务中，成功获取到自增 ID，但是事务执行失败回滚了，那么这个获取到的自增值不会被归还，就产生了间隙：

select * from t1;
-- id  name
-- 1    a
-- 2    b

START TRANSACTION;
insert into t1 (name) values('c');   -- id:3被占用
ROLLBACK;  -- 模拟事务失败

insert into t1 (name) values('b');  -- 已在name列设置了唯一索引，模拟执行失败, 此时id:4被占用

insert into t1 (name) values('d');  -- 执行成功，并分配了id:5

select * from t1;
-- id  name
-- 1    a
-- 2    b
-- 5    d

配置自增策略innodb_autoinc_lock_mode

mysql 提供了 innodb_autoinc_lock_mode 参数，可以配置自增策略。

可以执行以下 sql 查询自增配置：

SHOW VARIABLES LIKE 'innodb_autoinc_lock_mode';

有三种值，0/1/2

• 0 传统模式：
  • ​​​​​​​ 使用 AUTO-INC-LOCKING 机制
  • 性能差不使用
• 1 连续模式：
  • ​​​​​​​对 simple inserts 采用 MUTEX 机制；对bulk inserts 采用 AUTO-INC LOCKING 机制
  • ps 如果已经用 AUTO-INC LOCKING 产生自增值，同时再进行 simple inserts 操作时，还是要等待 AUTO-INC LOCKING 的释放的
• 2 混合模式：
  • ​​​​​​​ 使用  MUTEX 机制
  • 性能最好
  • 自增值可能不连续

我的 mysql 8.3 默认值是 2 即混合模式