MySQL自增主键锁_转:关于MySQL自增主键的几点问题

最新推荐文章于 2023-07-19 19:03:56 发布

weixin_39771775

最新推荐文章于 2023-07-19 19:03:56 发布

阅读量290

点赞数

文章标签： MySQL自增主键锁

本文链接：https://blog.csdn.net/weixin_39771775/article/details/113188228

版权

本文详细探讨了MySQL自增主键的三种插入类型（简单插入、大块插入和混合插入）及其对应的自增模式（传统模式、连续模式和交叉模式），分析了不同模式下的自增行为和可能产生的空洞。还提到了自增锁等待问题及其解决方案，以及LOAD DATA INFILE操作在并发插入时的行为。

摘要由CSDN通过智能技术生成

关于MySQL自增主键的几点问题(上)seanlook.com关于MySQL自增主键的几点问题(下)seanlook.com

前段时间遇到一个InnoDB表自增锁导致的问题，最近刚好有一个同行网友也问到自增锁的疑问，所以抽空系统的总结一下，这两个问题下篇会有阐述。

1. 划分三种插入类型

这里区分一下几种插入数据行的类型，便于后面描述：(纯逻辑上的划分)“Simple inserts”

简单插入，就是在处理sql语句的时候，能够提前预估到插入的行数，包括 INSERT / REPLACE 的单行、多行插入，但不含嵌套子查询以及 INSERT ... ON DUPLICATE KEY UPDATE。

“Bulk inserts”

本文暂且叫做大块插入，不能提前预知语句要插入的行数，也就无法知道分配多少个自增值，包括 INSERT ... SELECT, REPLACE ... SELECT, 以及 LOAD DATA 导入语句。InnoDB会每处理一行记录就为 AUTO_INCREMENT 列分配一个值。

“Mixed-mode inserts”

混合插入，比如在 “简单插入” 多行记录的时候，有的新行有指定自增值，有的没有，所以获得最坏情况下需要插入的数量，然后一次性分配足够的auto_increment id。比如:

又比如 INSERT ... ON DUPLICATE KEY UPDATE，它在 update 阶段有可能分配新的自增id，也可能不会。

2. 三种自增模式：innodb_autoinc_lock_mode

在以 5.6 版本，自增id累加模式分为：传统模式

traditional，innodb_autoinc_lock_mode = 0

在具有 AUTO_INCREMENT 的表上，所有插入语句会获取一个特殊的表级锁 AUTO-INC ，这个表锁是在语句结束之后立即释放(无需等到事务结束)，它可以保证在一个insert里面的多行记录连续递增，也能保证多个insert并发情况下自增值是连续的(不会有空洞)。

连续模式

consecutive，innodb_autoinc_lock_mode = 1

MySQL 5.1.22开始，InnoDB提供了一种轻量级互斥的自增实现机制，在内存中会有一个互斥量(mutex)，每次分配自增长ID时，就通过估算插入的数量(前提是必须能够估算到插入的数量，否则还是使用传统模式)，然后更新mutex，下一个线程过来时从新 mutex 开始继续计算，这样就能避免传统模式非要等待每个都插入之后才能获取下一个，把“锁”降级到只在分配id的时候锁定互斥量。

在 innodb_autoinc_lock_mode = 1(默认) 模式下，“简单插入”采用上面的 mutex 方式，“大块插入”(insert/replace … select … 、load data…)依旧采用 AUTO-INC 表级锁方式。当然如果一个事务里已经持有表 AUTO-INC 锁，那么后续的简单插入也需要等待这个 AUTO-INC 锁释放。这能够保证任意insert并发情况下自增值是连续的。

交叉模式

interleaved，innodb_autoinc_lock_mode = 2

该模式下所有 INSERT SQL 都不会有表级 AUTO-INC 锁，多个语句可以同时执行，所以在高并发插入场景下性能会好一些。但是当 binlog 采用 SBR 格式时，对于从库重放日志或者主库实例恢复时，并不可靠。

另者，它只能保证自增值在 insert语句级别 (单调)递增，所以多个insert可能会交叉着分配id，最终可能导致多个语句之间的id值不连续，这种情况出现在混合插入：

mutex 会按行分配4个id，但实际只用到2个，因此出现空洞。

3. 自增空洞(auto-increment sequence gap)

关于 AUTO_INCREMENT 自增出现空洞的问题，有必要再说明一下。在 0, 1, 2 三种任何模式下，如果事务回滚，那么里面获得自增值的sql回滚，但产生的自增值会一起丢失，不可能重新分配给其它insert语句。这也会产生空洞。

在大块插入情景下innodb_autoinc_lock_mode为 0 或 1 时，因为 AUTO-INC 锁会持续到语句结束，同一时间只有一个语句在表上执行，所以自增值是连续的(其它事务需要等待)，不会有空洞；

innodb_autoinc_lock_mode为 2 时，两个 “大块插入” 之间可能会有空洞，因为每条语句事先无法预知精确的数量而导致分配过多的id，可能有空洞。

4. 混合插入对 AUTO_INCREMENT 的影响

混合插入在 innodb_autoinc_lock_mode 不同模式下会有对表自增值有不同的表现。

CREATE TABLE t1 (

c1 INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,

c2 CHAR(1)

) ENGINE=INNODB;

ALTER TABLE t1 AUTO_INCREMENT 101;

mysql> SHOW CREATE TABLE t1\G

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

Table: t1

Create Table: CREATE TABLE `t1` (

`c1` int(10) unsigned NOT NULL AUTO_INCREMENT,

`c2` char(1) DEFAULT NULL,

PRIMARY KEY (`c1`)

) ENGINE=InnoDB AUTO_INCREMENT=101 DEFAULT CHARSET=utf8

1. mode 0

mysql> select @@innodb_autoinc_lock_mode;

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

| @@innodb_autoinc_lock_mode |

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

| 0 |

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

mysql> INSERT INTO t1 (c1,c2) VALUES (1,'a'), (NULL,'b'), (5,'c'), (NULL,'d');

mysql> select * from t1;

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

| c1 | c2 |

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

| 1 | a |

| 5 | c |

| 101 | b |

| 102 | d |

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

mysql> show create table t1\G

...

) ENGINE=InnoDB AUTO_INCREMENT=103 DEFAULT CHARSET=utf8

...

可以看到下一个自增值是 103 ，因为即使这是一条 insert语句(多行记录)，自增值还是每次分配一个，不会在语句开始前一次分配全。

2. mode 1

mysql> truncate table t1; ALTER TABLE t1 AUTO_INCREMENT 101; -- 复原

mysql> select @@innodb_autoinc_lock_mode;

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

| @@innodb_autoinc_lock_mode |

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

| 1 |

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

1 row in set (0.00 sec)

mysql> INSERT INTO t1 (c1,c2) VALUES (1,'a'), (NULL,'b'), (5,'c'), (NULL,'d');

Query OK, 4 rows affected (0.00 sec)

Records: 4 Duplicates: 0 Warnings: 0

mysql> select * from t1;

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

| c1 | c2 |

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

| 1 | a |

| 5 | c |

| 101 | b |

| 102 | d |

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

mysql> show create table t1\G

...

) ENGINE=InnoDB AUTO_INCREMENT=105 DEFAULT CHARSET=utf8

可以看到最终插入的值是一样的，但下一个自增值变成了 105，因为该模式下insert语句处理的时候，提前分配了 4 个自增值，但实际只有了两个。

注：如果你的insert自增列全都有带值，那么处理的时候是不会分配自增值的，经过下面这个实验，可以知道分配自增值，是在遇到第一个没有带自增列的行时，一次性分配的：

-- Tx1，先运行。 -- 插入第2行的时候 sleep 5s

INSERT INTO t1 (c1,c2) VALUES (2,'e'),(sleep(5)+6,'g'),(NULL,'f'), (NULL,'h');

-- Tx2，后运行。 -- 第一行没有给自增列值，马上分配 4 个

INSERT INTO t1 (c1,c2) VALUES (NULL,'b'), (1,'a'), (sleep(5)+5,'c'), (NULL,'d');

-- 得到的结果是

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

| c1 | c2 |

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

| 1 | a |

| 2 | e |

| 5 | c |

| 6 | g |

| 101 | b |

| 102 | d |

| 105 | f |

| 106 | h |

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

3. mode 2

mysql> truncate table t1; ALTER TABLE t1 AUTO_INCREMENT 101; -- 复原

mysql> select @@innodb_autoinc_lock_mode;

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

| @@innodb_autoinc_lock_mode |

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

| 2 |

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

mysql> INSERT INTO t1 (c1,c2) VALUES (1,'a'), (NULL,'b'), (5,'c'), (NULL,'d');

mysql> select * from t1;

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

| c1 | c2 |

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

| 1 | a |

| 5 | c |

| 101 | b |

| 102 | d |

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

mysql> show create table t1\G

...

) ENGINE=InnoDB AUTO_INCREMENT=105 DEFAULT CHARSET=utf8

结果看起来与连续模式一样，其实不然！该模式下，如果另外一个大块插入并发执行时，可能会出现以下现象：大块插入的的自增值有间断

其它并发执行的事务插入出现 duplicate-key error

第1点 (create t2 select * from t1)

Tx1: insert into t1(c2) select c2 from t2； -- 先执行

Tx2: INSERT INTO t1 (c1,c2) VALUES (1,'a'), (NULL,'b'), (5,'c'), (NULL,'d'); -- 后并发执行

在交叉模式下，Tx1事务插入的数据行会与 Tx1 交叉出现。

注：如果 Tx1 改成 insert into t1 select * from t2 ，那么 Tx2 执行极有可能会报 duplicate-key error，与下面第2点所说的重复键是不一样的

第2点

mysql> truncate table t1; ALTER TABLE t1 AUTO_INCREMENT 5; -- 复原

mysql> INSERT INTO t1 (c1,c2) VALUES (1,'a'), (NULL,'b'), (5,'c'), (NULL,'d');

ERROR 1062 (23000): Duplicate entry '5' for key 'PRIMARY'

总结

上面说了这么多，那么自增模式到底该怎么选择呢？其实很简单，目前数据库默认的 consecutive 即 innodb_autoinc_lock_mode=1 就是最好的模式，一般业务生产库不会有 insert into ... select ...或者 load data infile 这样的维护动作。(提示：即使晚上有数据迁移任务，也不要通过这样的形式进行)

innodb_autoinc_lock_mode=2 可以提高获取表自增id的并发能力(性能)，但是除非出现上面演示的 duplicate-key 特殊用法情形，不会像网上所说的获取到相同key导致重复的问题。但是如果binlog在 RBR 格式下不建议使用，可能出现主从数据不一致。还有就是能够容忍gap的存在，以及多个语句insert的自增值交叉。

参考：14.6.1.4 AUTO_INCREMENT Handling in InnoDBdev.mysql.com

UTO-INC waiting 锁等待

这是生产环境出现的现象，某日下午5点业务高峰期，我们的慢查询快照抓取程序报出大量线程阻塞，但是1分钟以后就好了。于是分析了当时的 processlist 和 innodb status 现场记录，发现有大量的 AUTO-INC waiting：

当时想这是得多大的并发量，才会导致 AUTO_INCREMENT 列的自增id分配出现性能问题，不太愿意相信这个事实(后面就再也没出现过)。了解一番之后(见关于MySQLz自增主键问题(上篇))，发现这个表级别的 AUTO-INC lock 就不应该在业务中存在，因为 innodb_autoinc_lock_mode为1，普通业务都是 simple inserts，获取自增id是靠内存里维护的一个互斥量(mutex counter)。

问题拿到知数堂优化班上课群里讨论过，也只是猜测是不是慢查询多了导致负载高，或者当时磁盘遇到什么物理故障阿里云那边自动恢复了。再后来怀疑是不是因为插入时带了 auto_increment 列的值(我们有个redis incr实现的自增id服务，虽然这一列有 AAUTO_INCREMENT 定义，但实际已经从发号器取id了)，会导致锁的性质会变？

为了弄清这个疑问，特意去看了下mysql源码，发现如果插入的自增值比表当前AUTOINC值要大，是直接update mutex counter：

看源码的时候也打消了另一个疑虑：show engine innodb status 看到的 AUTO-INC 有没有可能不区分表级自增锁和互斥量计数器两种自增方案，只是告诉你自增id获取忙不过来？实际不是的，代码里面有明确的定义是 autoinc_lock还是autoinc_mutex：

// dict0dict.cc :

#ifndef UNIV_HOTBACKUP

/********************************************************************//**

Acquire the autoinc lock. */

UNIV_INTERN

void

dict_table_autoinc_lock(

/*====================*/

4dict_table_t*table)/*!< in/out: table */

{

4mutex_enter(&table->autoinc_mutex);

}

/********************************************************************//**

Unconditionally set the autoinc counter. */

UNIV_INTERN

void

dict_table_autoinc_initialize(

/*==========================*/

4dict_table_t*table,/*!< in/out: table */

4ib_uint64_tvalue)/*!< in: next value to assign to a row */

{

4ut_ad(mutex_own(&table->autoinc_mutex));

4table->autoinc = value;

}

最后在微信上找周彦伟大神问问，在快要放弃的时候，从 innodb_lock_waits 中锁等待之间关系，一层一层挖，终于找到了一条这样的sql:

"INSERT INTO mydb1.t_mytable_inc ( f_log_id, f_fff_id, ..., f_from, f_sendmsg )

SELECT 2021712366, 507019984, ..., 10, 0 from dual"

瞬间就明(ma)白(niang)了。典型的 INSERT ... SELECT ...，但是 select 子句带的全是常量，但是对 innodb 来说它还是认为“这是 bulk inserts，我无法预估插入行数”，所以使用表级锁的自增方式。当时同时有 22 个这样的插入，可能负载也确实比较高导致活跃事务里主键最小的那一条一直处于 query end 状态，后面简单insert也需要等这个语句结束，直到释放 AUTO-INC table lock，以致引起雪崩效应。

之所以一直没发现这条语句，是因为 processlist 太长了，而且格式不友好。快照抓取程序这块还可以优化。

最后解决其实非常容易：既然已经有自增id服务，直接把把主键上的 AUTO_INCREMENT 定义去掉

整改这种 insert … select … 的sql。维护时可以，但开发账号要杜绝

周大神说他们用的是 mode 2 模式。也不失为一种方法

load data 为什么没阻塞其它事务

这是一个同行网友请教我的：

上篇讲到，load data infile 由于innodb无法提前知道插入的行数，所以归为 bulk inserts —— 表自增方式升级为表级锁，这样一来其它会话里的 insert岂不应该是会被阻塞，为什么实验结果却没有阻塞。

当然一开始我也觉得奇怪，但是仔细想一下就知道，这个表级锁是一个特殊的表锁，为了提高并发性，它是在语句结束就释放了(而不是事务结束)，那么只要验证 LOAD DATA 是把文件里面的行记录，拼装成单个insert就行了，这样其它会话的插入就可以在交错获得表级自增锁，实现不阻塞插入：

~上图我是为了看效果，临时设置 log_bin='statement'，看到 LOAD DATA INFILE 会把文件转换成一个事务包含的多行 insert，于是就说得通了。~ @jin 多谢指正。

上图 row 模式下的binlog，看到 BEGIN … COMMIT 之间包含了多行 insert。(注：在 statement 模式下，binlog里面记录的是 LOAD DATA 语句，从库会把文件从主库传输过来，再执行)

温馨提示：如果load data 的文件自带主键值，那么另一个会话获取的自增值很容易产生重复。

stackexchange上有个关于load data infile 对复制安全性的讨论dba.stackexchange.com

，同意二楼的观点，官方文档里说的 unsafe，并不是说执行这样的语句会导致安全问题，而是 considered unsafe，在 row-based 可用的情况下，优化器会自动把binlog记录为 row ，依然是安全的。

weixin_39771775

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
复制链接

分享到 QQ

分享到新浪微博

扫一扫