网站建设需要准备什么,百度知道官网,手机应用下载网站源码,阿里云网站建设MySQL 不同的自增 id 达到上限以后的行为
Hi#xff0c;我是阿昌#xff0c;今天学习记录的是关于MySQL 不同的自增 id 达到上限以后的行为的内容。
MySQL 里有很多自增的 id#xff0c;每个自增 id 都是定义了初始值#xff0c;然后不停地往上加步长。
虽然自然数是没有…MySQL 不同的自增 id 达到上限以后的行为
Hi我是阿昌今天学习记录的是关于MySQL 不同的自增 id 达到上限以后的行为的内容。
MySQL 里有很多自增的 id每个自增 id 都是定义了初始值然后不停地往上加步长。
虽然自然数是没有上限的但是在计算机里只要定义了表示这个数的字节长度那它就有上限。
比如无符号整型 (unsigned int) 是 4 个字节上限就是 2^32-1。
既然自增 id 有上限就有可能被用完。但是自增 id 用完了会怎么样呢
一、表定义自增值 id
说到自增 id第一个想到的应该就是表结构定义里的自增字段在自增主键为什么不是连续的中和介绍过的自增主键 id。
表定义的自增值达到上限后的逻辑是再申请下一个 id 时得到的值保持不变。
可以通过下面这个语句序列验证一下
create table t(id int unsigned auto_increment primary key) auto_increment4294967295;
insert into t values(null);
//成功插入一行 4294967295
show create table t;
/* CREATE TABLE t (id int(10) unsigned NOT NULL AUTO_INCREMENT,PRIMARY KEY (id)
) ENGINEInnoDB AUTO_INCREMENT4294967295;
*/insert into t values(null);
//Duplicate entry 4294967295 for key PRIMARY可以看到第一个 insert 语句插入数据成功后这个表的 AUTO_INCREMENT 没有改变还是 4294967295就导致了第二个 insert 语句又拿到相同的自增 id 值再试图执行插入语句报主键冲突错误。2^32-14294967295不是一个特别大的数对于一个频繁插入删除数据的表来说是可能会被用完的。
因此在建表的时候你需要考察表是否有可能达到这个上限如果有可能就应该创建成 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_id2^48时如果再有插入数据的行为要来申请 row_id拿到以后再取最后 6 个字节的话就是 0。
也就是说写入表的 row_id 是从 0 开始到 248-1。达到上限后下一个值就是 0然后继续循环。
当然2^48-1 这个值本身已经很大了但是如果一个 MySQL 实例跑得足够久的话还是可能达到这个上限的。
在 InnoDB 逻辑里申请到 row_idN 后就将这行数据写入表中如果表中已经存在 row_idN 的行新写入的行就会覆盖原有的行。
要验证这个结论的话可以通过 gdb 修改系统的自增 row_id 来实现。
注意用 gdb 改变量这个操作是为了便于复现问题只能在测试环境使用。 可以看到在用 gdb 将 dict_sys.row_id 设置为 2^48之后再插入的 a2 的行会出现在表 t 的第一行因为这个值的 row_id0。之后再插入的 a3 的行由于 row_id1就覆盖了之前 a1 的行因为 a1 这一行的 row_id 也是 1。
从这个角度看还是应该在 InnoDB 表中主动创建自增主键。
因为表自增 id 到达上限后再插入数据时报主键冲突错误是更能被接受的。毕竟覆盖数据就意味着数据丢失影响的是数据可靠性报主键冲突是插入失败影响的是可用性。而一般情况下可靠性优先于可用性。 三、Xid
在数据库日志和索引相关问题中介绍 redo log 和 binlog 相配合的时候提到了它们有一个共同的字段叫作 Xid。 它在 MySQL 中是用来对应事务的。那么Xid 在 MySQL 内部是怎么生成的呢 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 的场景。因为 global_query_id 定义的长度是 8 个字节这个自增值的上限是 264-1。
要出现这种情况必须是下面这样的过程
执行一个事务假设 Xid 是 A接下来执行 2^64次查询语句让 global_query_id 回到 A再启动一个事务这个事务的 Xid 也是 A。
不过2^64这个值太大了大到你可以认为这个可能性只会存在于理论上。 四、Innodb trx_id
Xid 和 InnoDB 的 trx_id 是两个容易混淆的概念。
Xid 是由 server 层维护的。InnoDB 内部使用 Xid就是为了能够在 InnoDB 事务和 server 之间做关联。
但是InnoDB 自己的 trx_id是另外维护的。 binlog是server层redolog是innodb。xid是关联binlog和redolog的。 xid是server层维护。 trx_id是innodb记录行数据的版本号用作事物隔离是innodb维护 trx_id就是在事务间是否需要隔离中讲事务可见性时用到的事务 idtransaction 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。 一个事务现场 session B 里从 innodb_trx 表里查出的这两个字段第二个字段 trx_mysql_thread_id 就是线程 id。
显示线程 id是为了说明这两次查询看到的事务对应的线程 id 都是 5也就是 session A 所在的线程。
可以看到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。
需要注意的是除了显而易见的修改类语句外如果在 select 语句后面加上 for update这个事务也不是只读事务。实验的时候发现不止加 1。
这是因为
update 和 delete 语句除了事务本身还涉及到标记删除旧数据也就是要把数据放到 purge 队列里等待后续物理删除这个操作也会把 max_trx_id1 因此在一个事务中至少加 2InnoDB 的后台操作比如表的索引信息统计这类操作也是会启动内部事务的因此你可能看到trx_id 值并不是按照加 1 递增的。 T2 时刻查到的这个很大的数字是怎么来的呢 其实这个数字是每次查询的时候由系统临时计算出来的。它的算法是把当前事务的 trx 变量的指针地址转成整数再加上 2^48。
使用这个算法就可以保证以下两点
因为同一个只读事务在执行期间它的指针地址是不会变的所以不论是在 innodb_trx 还是在 innodb_locks 表里同一个只读事务查出来的 trx_id 就会是一样的。如果有并行的多个只读事务每个事务的 trx 变量的指针地址肯定不同。这样不同的并发只读事务查出来的 trx_id 就是不同的。 那么为什么还要再加上 2^48呢 在显示值里面加上 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来复现一下这个 bug。
首先我们需要把当前的 max_trx_id 先修改成 248-1。 注意这个 case 里使用的是可重复读隔离级别。 具体的操作流程如下 由于已经把系统的 max_trx_id 设置成了 2^48-1所以在 session A 启动的事务 TA 的低水位就是 2^48-1。
在 T2 时刻session B 执行第一条 update 语句的事务 id 就是 2^48-1而第二条 update 语句的事务 id 就是 0 了这条 update 语句执行后生成的数据版本上的 trx_id 就是 0。
在 T3 时刻session A 执行 select 语句的时候判断可见性发现c3 这个数据版本的 trx_id小于事务 TA 的低水位因此认为这个数据可见。但这个是脏读。
由于低水位值会持续增加而事务 id 从 0 开始计数就导致了系统在这个时刻之后所有的查询都会出现脏读的。并且MySQL 重启时 max_trx_id 也不会清 0也就是说重启 MySQL这个 bug 仍然存在。 那么这个 bug 也是只存在于理论上吗 假设一个 MySQL 实例的 TPS 是每秒 50 万持续这个压力的话在 17.8 年后就会出现这个情况。
如果 TPS 更高这个年限自然也就更短了。但是从 MySQL 的真正开始流行到现在恐怕都还没有实例跑到过这个上限。
不过这个 bug 是只要 MySQL 实例服务时间够长就会必然出现的。 五、thread_id
线程 idthread_id其实线程 id 才是 MySQL 中最常见的一种自增 id。
平时在查各种现场的时候show processlist 里面的第一列就是 thread_id。
thread_id 的逻辑很好理解系统保存了一个全局变量 thread_id_counter每新建一个连接就将 thread_id_counter 赋值给这个新连接的线程变量。
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);六、总结
MySQL 不同的自增 id 达到上限以后的行为。
数据库系统作为一个可能需要 7*24 小时全年无休的服务考虑这些边界是非常有必要的。
每种自增 id 有各自的应用场景在达到上限后的表现也不同
表的自增 id 达到上限后再申请时它的值就不会改变进而导致继续插入数据时报主键冲突的错误。row_id 达到上限后则会归 0 再重新递增如果出现相同的 row_id后写的数据会覆盖之前的数据。Xid 只需要不在同一个 binlog 文件中出现重复值即可。虽然理论上会出现重复值但是概率极小可以忽略不计。InnoDB 的 max_trx_id 递增值每次 MySQL 重启都会被保存起来所以提到的脏读的例子就是一个必现的 bug好在留给的时间还很充裕。thread_id 是使用中最常见的而且也是处理得最好的一个自增 id 逻辑了。
不同的自增 id 有不同的上限值上限值的大小取决于声明的类型长度。
