线上MySQL的自增id用完了怎么办？

柔情只为你懂 2023-09-28 14:43 36阅读 0赞

MySQL的自增id都定义了初始值，然后不断加步长。虽然自然数没有上限，但定义了表示这个数的字节长度，计算机存储就有上限。比如，无符号整型(unsigned int)是4个字节，上限就是2^32 - 1。那自增id用完，会怎么样？

![931ad814dc04bd35e7679ac4367a7591.jpeg][]

#### 表定义自增值id ####

表定义的自增值达到上限后的逻辑是：再申请下一个id时，得到的值保持不变。

mysql> create table t(id int unsigned auto_increment primary key) auto_increment=4294967295;
    Query OK, 0 rows affected (0.01 sec)
    
    mysql> insert into t values(null);
    Query OK, 1 row affected (0.00 sec)
    
    mysql> show create table t;
    +-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
    | Table | Create Table                                                                                                                                                                      |
    +-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
    | t     | CREATE TABLE `t` (
      `id` int unsigned NOT NULL AUTO_INCREMENT,
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB AUTO_INCREMENT=4294967295 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci |
    +-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
    1 row in set (0.00 sec)
    
    //成功插入一行 4294967295
    mysql> insert into t values(null);
    ERROR 1062 (23000): Duplicate entry '4294967295' for key 't.PRIMARY'

第一个insert成功后，该表的AUTO\_INCREMENT还是4294967295，导致第二个insert又拿到相同自增id值，再试图执行插入语句，主键冲突。

2^32 - 1（4294967295）不是一个特别大的数，一个频繁插入删除数据的表是可能用完的。建表时就需要考虑你的表是否有可能达到该上限，若有，就应创建成8字节的bigint unsigned。

#### InnoDB系统自增row\_id ####

若你创建的InnoDB表未指定主键，则InnoDB会自动创建一个不可见的，6个字节的 **row\_id** 。InnoDB维护了一个全局的dict\_sys->row\_id值

![8c69bf9684de69a90eb132b984b4809d.jpeg][]

所有无主键的InnoDB表，每插入一行数据，都将当前的dict\_sys->row\_id作为要插入数据的row\_id，然后把dict\_sys->row\_id加1。

代码实现时row\_id是个长度为8字节的无符号长整型(bigint unsigned)。但InnoDB在设计时，给row\_id留的只是6个字节的长度，这样写到数据表中时只放了最后6个字节，所以row\_id能写到数据表中的值，就有两个特征：

1.  row\_id写入表中的值范围，是从0到2^48 - 1
2.  当dict\_sys.row\_id=2^48时，如果再有插入数据的行为要来申请 **row\_id** ，拿到以后再取最后6个字节的话就是0

即写入表的row\_id从0~2^48 - 1。达到上限后，下个值就是0，然后继续循环。

2^48 - 1已经很大，但若一个MySQL实例活得久，还是可能达到上限。

InnoDB里，申请到row\_id=N后，就将这行数据写入表中；若表中已经存在row\_id=N的行，新写入的行就会覆盖原有的行。

验证该结论：通过gdb修改系统的自增row\_id。用gdb是为了便于复现问题，只能在测试环境使用。

*  row\_id用完的验证序列

![1486970d93727930f63651b388f8196a.jpeg][]

*  row\_id用完的效果验证

![df5f91e82425f9713244a41a32e17d49.jpeg][]

可见，在我用gdb将dict\_sys.row\_id设置为2^48之后，再插入a=2会出现在表t的第一行，因为该值的row\_id=0。

之后再插入a=3，由于row\_id=1，就覆盖了之前a=1的行，因为a=1这一行的row\_id也是1。

所以应该在InnoDB表中主动创建自增主键：当表自增id到达上限后，再插入数据时会报主键冲突错误。

毕竟覆盖数据，就意味着数据丢失，影响数据可靠性；报主键冲突，插入失败，影响可用性。一般可靠性优于可用性。

#### Xid ####

redo log和binlog有个共同字段Xid，用来对应事务。Xid在MySQL内部是如何生成的呢？

MySQL内部维护了一个全局变量global\_query\_id

![1d88f60667a2f445b0e66ec52b6be65e.jpeg][]

每次执行语句时，将它赋值给query\_id，然后给该变量+1：

![4c11d3c4c6cf8d3d371bc86fb8c81428.jpeg][]

若当前语句是该事务执行的第一条语句，则MySQL还会同时把query\_id赋值给该事务的Xid：

![c54ce0d2d784d168ac025241de10125a.jpeg][]

而global\_query\_id是一个纯内存变量，重启之后就清零了。所以同一DB实例，不同事务的Xid可能相同。

但MySQL重启之后会重新生成新binlog文件，这就保证同一个binlog文件里的Xid唯一。

虽然MySQL重启不会导致同一个binlog里面出现两个相同Xid，但若global\_query\_id达到上限，就会继续从0开始计数。理论上还是会出现同一个binlog里面出现相同Xid。

因为global\_query\_id8字节，上限2^64 - 1。要出现这种情况，需满足：

1.  执行一个事务，假设Xid是A
2.  接下来执行2^64次查询语句，让global\_query\_id回到A
    
    2^64太大了，这种可能只存在于理论中。
3.  再启动一个事务，这个事务的Xid也是A

#### Innodb trx\_id ####

*  Xid由server层维护

InnoDB内部使用Xid，为了关联InnoDB事务和server

但InnoDB自己的trx\_id，是另外维护的事务id（transaction id）。

InnoDB内部维护了一个max\_trx\_id全局变量，每次需要申请一个新的trx\_id时，就获得max\_trx\_id的当前值，然后并将max\_trx\_id加1。

#### InnoDB数据可见性的核心思想 ####

每一行数据都记录了更新它的trx\_id，当一个事务读到一行数据时，判断该数据是否可见，就是通过事务的一致性视图与这行数据的trx\_id做对比。

对于正在执行的事务，你可以从information\_schema.innodb\_trx表中看到事务的trx\_id。

看如下案例：事务的trx\_id

![df1815eb824d5ef20c929c63ffc7be24.jpeg][]

S2 的执行记录：

mysql> use information_schema;
    Reading table information for completion of table and column names
    You can turn off this feature to get a quicker startup with -A
    
    Database changed
    
    mysql> select trx_id, trx_mysql_thread_id from innodb_trx;
    +-----------------+---------------------+
    | trx_id          | trx_mysql_thread_id |
    +-----------------+---------------------+
    | 421972504382792 |                  70 |
    +-----------------+---------------------+
    1 row in set (0.00 sec)
    
    mysql> select trx_id, trx_mysql_thread_id from innodb_trx;
    +---------+---------------------+
    | trx_id  | trx_mysql_thread_id |
    +---------+---------------------+
    | 1355623 |                  70 |
    +---------+---------------------+
    1 row in set (0.01 sec)

S2从innodb\_trx表里查出的这两个字段，第二个字段trx\_mysql\_thread\_id就是线程id。显示线程id，是为说明这两次查询看到的事务对应的线程id都是5，即S1所在线程。

t2时显示的trx\_id是一个很大的数；t4时刻显示的trx\_id是1289，看上去是一个比较正常的数字。这是为啥？

t1时，S1还未涉及更新，是一个只读事务。对于只读事务，InnoDB并不会分配trx\_id：

*  t1时，trx\_id的值就是0。而这个很大的数，只是显示用
 *  直到S1在t3时执行insert，InnoDB才真正分配trx\_id。所以t4时，S2查到该trx\_id的值就是1289。

除了明显的修改类语句，若在select 语句后面加上for update，也不是只读事务。

*  update 和 delete语句除了事务本身，还涉及到标记删除旧数据，即要把数据放到purge队列里等待后续物理删除，这个操作也会把max\_trx\_id+1， 因此在一个事务中至少加2
 *  InnoDB的后台操作，比如表的索引信息统计这类操作，也是会启动内部事务的，因此你可能看到，trx\_id值并不是按照加1递增的。

t2时查到的很大数字是怎么来的？

每次查询时，由系统临时计算：

当前事务的trx变量的指针地址转成整数，再加上248

这样可以保证：

*  因为同一只读事务在执行期间，它的指针地址不会变，所以无论在 innodb\_trx还是在innodb\_locks表里，同一个只读事务查出来的trx\_id就会是一样的
 *  若有并行只读事务，每个事务的trx变量的指针地址肯定不同。这样，不同并发只读事务，查出来的trx\_id就是不同的。

#### 为什么要加248？ ####

保证只读事务显示的trx\_id值比较大，正常情况下就会区别于读写事务的id。但trx\_id跟row\_id的逻辑类似，定义为8个字节。

理论上还是可能出现一个读写事务与一个只读事务显示的trx\_id相同。不过概率很低，也没有什么实质危害，不管。

#### 为何只读事务不分配trx\_id？ ####

*  减小事务视图里面活跃事务数组的大小。因为当前正在运行的只读事务，不影响数据的可见性判断。所以，在创建事务的一致性视图时，InnoDB就只需要拷贝读写事务的trx\_id
 *  减少trx\_id的申请次数。InnoDB执行一个普通的select语句，也要对应一个只读事务。所以只读事务优化后，普通查询语句无需申请trx\_id，大大减少并发事务申请trx\_id的锁冲突

由于只读事务不分配trx\_id，显然trx\_id的增速变慢。

但 max\_trx\_id 会持久化存储，重启也不会重置为0。理论上，只要一个MySQL实例跑得够久，就可能出现max\_trx\_id达到2^48 - 1，然后从0开始循环。

达到该状态后，MySQL就会持续出现一个脏读bug：

首先把当前的max\_trx\_id先修改成2^48 - 1。这里是可重复读。

*  复现脏读

![fbc775903407cb1237360ec408480dda.jpeg][]

![98c717aaff9c3e9942faca599899a704.jpeg][]

*  因为系统的max\_trx\_id被设置成2^48 - 1，所以在session A启动的事务TA的低水位就是2^48 - 1。

t2时:

*  session B执行第一条update语句的事务id=2^48 - 1
 *  第二条事务id就是0了，这条update执行后生成的数据版本上的trx\_id=0

t3时：

session A执行select的可见性判断：c=3这个数据版本的trx\_id（0），小于事务TA的低水位（2^48 - 1），所以认为该数据可见。

但这是脏读。

由于低水位值会持续增加，而事务id从0开始计数，导致系统在该时刻后，所有查询都会出现脏读。

并且MySQL重启时max\_trx\_id也不会清0，即重启MySQL，这个bug仍然存在。那这bug也是只存在于理论上吗？

假设一个MySQL实例的TPS是50w，持续这样，17.8年后就会出现该情况。但从MySQL真正开始流行到现在，恐怕都还没有实例跑到过这个上限。不过，只要MySQL实例服务时间够长，就必然会出现该bug。

这也可以加深对低水位和数据可见性的理解

#### thread\_id ####

系统保存了一个全局变量thread\_id\_counter

![dacd2eea53c0174fae0f7a23d7e763dc.jpeg][]

每新建一个连接，就将thread\_id\_counter赋值给这个新连接的线程变量new\_id。

thread\_id\_counter定义为4个字节，因此达到2^32 - 1，就会重置为0，继续增加。

![e23fcf0444b9e0b92f7fb7bc3a2f8089.jpeg][]

但不会在show processlist看到两个相同的thread\_id。因为MySQL使用了一个唯一数组

![76f66d962faabcc24a06697e27670e41.jpeg][]

给新线程分配thread\_id时的逻辑：

![d3b6b1d768bfe237a3eab012b44673e7.jpeg][]

#### 总结 ####

每种自增id有各自的应用场景，在达到上限后的表现也不同：

表的自增id达到上限后，再申请时它的值就不会改变，进而导致继续插入数据时报主键冲突错误

row\_id达到上限后，则会归0再重新递增，如果出现相同的row\_id，后写的数据会覆盖之前的数据

Xid只需要不在同一个binlog文件中出现重复值即可。虽然理论上会出现重复值，但是概率极小，可以忽略不计

InnoDB的max\_trx\_id 递增值每次MySQL重启都会被保存起来，所以我们文章中提到的脏读的例子就是一个必现的bug，好在留给我们的时间还很充裕

![b0efad1fb69242479392900f8292983d.jpeg][]

[931ad814dc04bd35e7679ac4367a7591.jpeg]: https://img-blog.csdnimg.cn/img_convert/931ad814dc04bd35e7679ac4367a7591.jpeg
[8c69bf9684de69a90eb132b984b4809d.jpeg]: https://img-blog.csdnimg.cn/img_convert/8c69bf9684de69a90eb132b984b4809d.jpeg
[1486970d93727930f63651b388f8196a.jpeg]: https://img-blog.csdnimg.cn/img_convert/1486970d93727930f63651b388f8196a.jpeg
[df5f91e82425f9713244a41a32e17d49.jpeg]: https://img-blog.csdnimg.cn/img_convert/df5f91e82425f9713244a41a32e17d49.jpeg
[1d88f60667a2f445b0e66ec52b6be65e.jpeg]: https://img-blog.csdnimg.cn/img_convert/1d88f60667a2f445b0e66ec52b6be65e.jpeg
[4c11d3c4c6cf8d3d371bc86fb8c81428.jpeg]: https://img-blog.csdnimg.cn/img_convert/4c11d3c4c6cf8d3d371bc86fb8c81428.jpeg
[c54ce0d2d784d168ac025241de10125a.jpeg]: https://img-blog.csdnimg.cn/img_convert/c54ce0d2d784d168ac025241de10125a.jpeg
[df1815eb824d5ef20c929c63ffc7be24.jpeg]: https://img-blog.csdnimg.cn/img_convert/df1815eb824d5ef20c929c63ffc7be24.jpeg
[fbc775903407cb1237360ec408480dda.jpeg]: https://img-blog.csdnimg.cn/img_convert/fbc775903407cb1237360ec408480dda.jpeg
[98c717aaff9c3e9942faca599899a704.jpeg]: https://img-blog.csdnimg.cn/img_convert/98c717aaff9c3e9942faca599899a704.jpeg
[dacd2eea53c0174fae0f7a23d7e763dc.jpeg]: https://img-blog.csdnimg.cn/img_convert/dacd2eea53c0174fae0f7a23d7e763dc.jpeg
[e23fcf0444b9e0b92f7fb7bc3a2f8089.jpeg]: https://img-blog.csdnimg.cn/img_convert/e23fcf0444b9e0b92f7fb7bc3a2f8089.jpeg
[76f66d962faabcc24a06697e27670e41.jpeg]: https://img-blog.csdnimg.cn/img_convert/76f66d962faabcc24a06697e27670e41.jpeg
[d3b6b1d768bfe237a3eab012b44673e7.jpeg]: https://img-blog.csdnimg.cn/img_convert/d3b6b1d768bfe237a3eab012b44673e7.jpeg
[b0efad1fb69242479392900f8292983d.jpeg]: https://img-blog.csdnimg.cn/b0efad1fb69242479392900f8292983d.jpeg