45 | 自增id用完怎么办?

文章目录

  • MySQL45讲
    • 实践篇
      • 45 | 自增id用完怎么办?
        • 表定义自增值 id
        • InnoDB 系统自增 row_id
        • Xid
        • Innodb trx_id
        • thread_id


MySQL45讲

实践篇

45 | 自增id用完怎么办?

表定义自增值 id

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

create table t(id int unsigned auto_increment primary key) auto_increment=4294967295;
insert into t values(null);
//成功插入一行 4294967295
show create table t;
/* CREATE TABLE `t` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4294967295;
*/

insert into t values(null);
//Duplicate entry '4294967295' for key 'PRIMARY'

2^32-1(4294967295)不是一个特别大的数,对于一个频繁插入删除数据的表来说,是可能会被用完的。因此,在建表的时候需要考察该表是否有可能达到这个上限,如果有可能,就应该创建成 8 个字节的 bigint unsigned。

InnoDB 系统自增 row_id

如果创建的 InnoDB 表没有指定主键,那么 InnoDB 会创建一个不可见的,长度为 6 个字节的 row_id。InnoDB 维护了一个全局的 dict_sys.row_id 值,所有无主键的 InnoDB 表,每插入一行数据,都将当前的 dict_sys.row_id 值作为要插入数据的 row_id,然后把 dict_sys.row_id 的值加 1。

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

  • row_id 写入表中的值范围,是从 0 到 2^48-1;
  • 当 dict_sys.row_id=2^48时,如果再有插入数据的行为要来申请 row_id,拿到以后再取最后 6 个字节的话就是 0。

写入表的 row_id 是从 0 开始到 2^48-1。达到上限后,下一个值就是 0,然后继续循环。

在 InnoDB 逻辑里,申请到 row_id=N 后,就将这行数据写入表中;如果表中已经存在 row_id=N 的行,新写入的行就会覆盖原有的行。

要验证这两个结论,可以通过 gdb 修改系统的自增 row_id 来实现。
45 | 自增id用完怎么办?_第1张图片
45 | 自增id用完怎么办?_第2张图片
用 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

MySQL 内部维护了一个全局变量 global_query_id,每次执行语句的时候将它赋值给 Query_id,然后给这个变量加 1。如果当前语句是这个事务执行的第一条语句,那么 MySQL 还会同时把 Query_id 赋值给这个事务的 Xid。

global_query_id 是一个纯内存变量,重启之后就清零了。 所以,在同一个数据库实例中,不同事务的 Xid 也是有可能相同的。但是 MySQL 重启之后会重新生成新的 binlog 文件,这就保证了,同一个 binlog 文件里,Xid 一定是惟一的。

虽然 MySQL 重启不会导致同一个 binlog 里面出现两个相同的 Xid,但是如果 global_query_id 达到上限后,就会继续从 0 开始计数。从理论上讲,还是就会出现同一个 binlog 里面出现相同 Xid 的场景。

Innodb trx_id

Xid 是由 server 层维护的。 InnoDB 内部使用 Xid,就是为了能够在 InnoDB 事务和 server 之间做关联但是,InnoDB 自己的 trx_id,是另外维护的。(关于 trx_id,可参考 08 | 事务到底是隔离的还是不隔离的?)

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 的 示例:
45 | 自增id用完怎么办?_第3张图片
T2 时刻显示的 trx_id 是一个很大的数;T4 时刻显示的 trx_id 是 1289,看上去是一个比较正常的数字。

在 T1 时刻,session A 还没有涉及到更新,是一个只读事务。而对于只读事务,InnoDB 并不会分配 trx_id。也就是说:

  • 在 T1 时刻,trx_id 的值其实就是 0。而这个很大的数,只是显示用的。
  • 直到 session A 在 T3 时刻执行 insert 语句的时候,InnoDB 才真正分配了 trx_id。所以,T4 时刻,session B 查到的这个 trx_id 的值就是 1289。

疑问:为什么 trx_id 每次申请不止加 1?

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

疑问:T2 时刻查到的这个很大的数字是怎么来的?
这个数字是每次查询的时候由系统临时计算出来的。它的算法是:把当前事务的 trx 变量的指针地址转成整数,再加上 2^48。使用这个算法,就可以保证以下两点:

  • 因为同一个只读事务在执行期间,它的指针地址是不会变的,所以不论是在 innodb_trx 还是在 innodb_locks 表里,同一个只读事务查出来的 trx_id 就会是一样的。
  • 如果有并行的多个只读事务,每个事务的 trx 变量的指针地址肯定不同。这样,不同的并发只读事务,查出来的 trx_id 就是不同的。

疑问:为什么还要再加上 2^48?
在显示值里面加上 2^48,目的是要保证只读事务显示的 trx_id 值比较大,正常情况下就会区别于读写事务的 id。但是,trx_id 跟 row_id 的逻辑类似,定义长度也是 8 个字节。因此,在理论上还是可能出现一个读写事务与一个只读事务显示的 trx_id 相同的情况。不过这个概率很低,并且也没有什么实质危害。

疑问:只读事务不分配 trx_id,有什么好处?

  • 一个好处是,这样做可以减小事务视图里面活跃事务数组的大小。因为当前正在运行的只读事务,是不影响数据的可见性判断的。所以,在创建事务的一致性视图时,InnoDB 就只需要拷贝读写事务的 trx_id。
  • 另一个好处是,可以减少 trx_id 的申请次数。在 InnoDB 里,即使只是执行一个普通的 select 语句,在执行过程中,也是要对应一个只读事务的。所以只读事务优化后,普通的查询语句不需要申请 trx_id,就大大减少了并发事务申请 trx_id 的锁冲突

max_trx_id 会持久化存储,重启也不会重置为 0。 从理论上讲,只要一个 MySQL 服务跑得足够久,就可能出现 max_trx_id 达到 2^48-1 的上限,然后从 0 开始的情况。当达到这个状态后,MySQL 就会持续出现一个脏读的 bug。
45 | 自增id用完怎么办?_第4张图片
在 T2 时刻,session B 执行第一条 update 语句的事务 id 就是 2^48-1,而第二条 update 语句的事务 id 就是 0 了,这条 update 语句执行后生成的数据版本上的 trx_id 就是 0。

在 T3 时刻,session A 执行 select 语句的时候,判断可见性发现,c=3 这个数据版本的 trx_id,小于事务 TA 的低水位,因此认为这个数据可见。

由于低水位值会持续增加,而事务 id 从 0 开始计数,就导致了系统在这个时刻之后,所有的查询都会出现脏读的。并且,MySQL 重启时 max_trx_id 也不会清 0,也就是说重启 MySQL,这个 bug 仍然存在。

thread_id

系统保存了一个全局变量 thread_id_counter,每新建一个连接,就将 thread_id_counter 赋值给这个新连接的 thread_id。

thread_id_counter 定义的大小是 4 个字节,因此达到 2^32-1 后,它就会重置为 0,然后继续增加。但是,不会在 show processlist 里看到两个相同的 thread_id。因为 MySQL 设计了一个唯一数组的逻辑,给新线程分配 thread_id 的时候,逻辑代码如下:

do {
  new_id= thread_id_counter++;
} while (!thread_ids.insert_unique(new_id).second);

你可能感兴趣的