After setting AUTO_ID_CACHE=1 at the table level in TiDB, the auto-increment ID behaves abnormally

Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.

Original topic: tidb表级设置AUTO_ID_CACHE=1后,id自增异常

| username: TiDBer_hxqv9az1

To improve efficiency, please provide the following information. A clear problem description can lead to a quicker resolution:

[Overview] Sysbench 1.20 mixed read-write stress test. After preparing the data, max(id) is approximately 1.5 times the count of rows. After inserting 2688 rows of data, the id of the 2689th row jumps by 1313, causing a huge id gap.

[Application Framework and Development Adaptation Business Logic]
sysbench 1.20

[Background] Operations performed
vim oltp_common.lua modified the create_table function to add AUTO_ID_CACHE=1

time sysbench /usr/share/sysbench/oltp_read_write.lua --db-driver=mysql --mysql-host=xxx.xxx.16.65 --mysql-port=6000 --mysql-user=root --mysql-password=‘xxx’ --mysql-db=sbtest --table_size=1000000 --tables=10 --threads=10 --time=300 prepare
[Phenomenon] Business and database phenomenon
Default table creation SQL, prepare data as follows:
CREATE TABLE sbtest4 (

id int(11) NOT NULL AUTO_INCREMENT,

k int(11) NOT NULL DEFAULT ‘0’,

c char(120) NOT NULL DEFAULT ‘’,

pad char(60) NOT NULL DEFAULT ‘’,

PRIMARY KEY (id) /*T![clustered_index] CLUSTERED */,

KEY k_4 (k)

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin AUTO_INCREMENT=1221704
select max(id), min(id), count(1) from sbtest.sbtest4;

±--------±--------±---------+

| max(id) | min(id) | count(1) |

±--------±--------±---------+

| 1003089 | 1 | 1000000 |

±--------±--------±---------+

After setting AUTO_ID_CACHE=1 at the table level, the table creation SQL and prepare data are as follows:
CREATE TABLE sbtest2.sbtest4 (

id int(11) NOT NULL AUTO_INCREMENT,

k int(11) NOT NULL DEFAULT ‘0’,

c char(120) NOT NULL DEFAULT ‘’,

pad char(60) NOT NULL DEFAULT ‘’,

PRIMARY KEY (id) /*T![clustered_index] CLUSTERED */,

KEY k_4 (k)

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin AUTO_INCREMENT=1486753 /*T![auto_id_cache] AUTO_ID_CACHE=1 */
select max(id), min(id), count(1) from sbtest2.sbtest4;

±--------±--------±---------+

| max(id) | min(id) | count(1) |

±--------±--------±---------+

| 1486752 | 1 | 1000000 |

±--------±--------±---------+

[Problem] Current issue encountered
AUTO_ID_CACHE=1 compatible with MySQL auto-increment, id has a huge gap.
[Business Impact]
Data exceeding 2688 rows does not meet the requirement of continuous auto-increment id.
[TiDB Version]
7.5.1
[Attachments] Relevant logs and monitoring

| username: DBAER | Original post link

The documentation says that to use MySQL compatibility mode, set AUTO_ID_CACHE to 1 when creating the table:
This situation shouldn’t occur. How about manually simulating an insert statement to test it? Could it be that sysbench has DML operations based on ID?

| username: zhanggame1 | Original post link

It’s quite strange, we didn’t encounter this issue during our auto-increment tests. Check if there’s a problem with the TiDB server, as this could cause the jumps.

| username: xfworld | Original post link

Upgraded to the latest minor version, encountered a bug, but it has been fixed.

| username: dba远航 | Original post link

It should be a BUG.

| username: miya | Original post link

Is there any documentation for bug fixes?

| username: 小龙虾爱大龙虾 | Original post link

Your TiDB v7.5.1 is already the latest version. There was indeed a bug before (autoid service rebase internal SQL should not meet transaction conflict · Issue #50819 · pingcap/tidb · GitHub) which has been fixed in v7.5.1. If you can reproduce the issue, it is recommended to submit an issue on GitHub.

| username: TiDBer_hxqv9az1 | Original post link

The TiDB server log only contains a large number of
[WARN] [txn.go:167] [RunInNewTxn] [“retry txn”=448953402557202509] [“original txn”=448953402557202509] [error=“[kv:9007]Write conflict, txnStartTS=448953402557202509, conflictStartTS=448953402557202655, conflictCommitTS=448953402557202662, key={metaKey=true, key=DB:618, field=TID:1096}, originalKey=6d44423a3631380000fd00000000000000685449443a31303936ff0000000000000000f7, primary={metaKey=true, key=DB:618, field=TID:1096}, originalPrimaryKey=6d44423a3631380000fd00000000000000685449443a31303936ff0000000000000000f7, reason=Optimistic [try again later]”]

No “autoid” keyword found in the issue.

| username: TiDBer_hxqv9az1 | Original post link

I have already submitted a bug.
after create table add auto_id_cache, the AUTO_INCREMENT col have the huge gap · Issue #52465 · pingcap/tidb (github.com)

| username: TiDBer_RjzUpGDL | Original post link

Upgrade version

| username: 舞动梦灵 | Original post link

It is not recommended to use auto-increment, as this column serves no purpose. It only indicates your primary key. Instead, let the developers set a column themselves to generate a snowflake ID or a random UUID. This is much better than auto-increment.

| username: zhaokede | Original post link

In high concurrency scenarios, use AutoRandom.

| username: system | Original post link

This topic was automatically closed 60 days after the last reply. New replies are no longer allowed.