Updating rows by auto_increment id cause id increasing dramatically

[nanne007]

Please answer these questions before submitting your issue. Thanks!

1. What did you do?
   If possible, provide a recipe for reproducing the error.

• connect to one tidb-server, and do:

mysql> create table t1(id bigint auto_increment primary key, b int);
Query OK, 0 rows affected (1.12 sec)

mysql> insert into t1(b) values(1);
Query OK, 1 row affected (0.19 sec)

mysql> select * from t1;
+----+------+
| id | b    |
+----+------+
|  1 |    1 |
+----+------+
1 row in set (0.00 sec)

• then checkout to another tidb-server:

mysql> select * from t1;
+----+------+
| id | b    |
+----+------+
|  1 |    1 |
+----+------+
1 row in set (0.14 sec)

mysql> insert into t1(b) values(1);
Query OK, 1 row affected (0.05 sec)

mysql> select * from t1;
+-------+------+
| id    | b    |
+-------+------+
|     1 |    1 |
| 30001 |    1 |
+-------+------+
2 rows in set (0.00 sec)

• and then check back to first tidb-server:

mysql> update t1 set b = 2 where id = 30001;
Query OK, 1 row affected (0.04 sec)

mysql> select * from t1;
+-------+------+
| id    | b    |
+-------+------+
|     1 |    1 |
| 30001 |    2 |
+-------+------+
2 rows in set (0.01 sec)

mysql> insert into t1(b) values(3);
Query OK, 1 row affected (0.01 sec)

mysql> select * from t1;
+-------+------+
| id    | b    |
+-------+------+
|     1 |    1 |
| 30001 |    2 |
| 60001 |    3 |
+-------+------+
3 rows in set (0.00 sec)

2. What did you expect to see?
   Expected id of insert into t1(b) values(3) is 2.

3. What did you see instead?
   id of insert into t1(b) values(3) is 60001.
   It seems that when check back to first tidb-server, do update to row of id 30001, tidb rebased it autoid, and next insert id increasing dramatically.
   I don't know it's expected, so issued here.

4. What version of TiDB are you using (tidb-server -V or run select tidb_version(); on TiDB)?
   v2.0.6, commit: b13bc08

[alivxxx]

It is expected. You can refer the docs here /~https://github.com/pingcap/docs/blob/master/sql/mysql-compatibility.md#auto-increment-id.

[nanne007]

@lamxTyler Thanks for the reply.

The doc only specifies that user should not add custom id when insert.

However, this case is caused by updating to existed rows.

[jackysp]

Thanks for your report, @lerencao !
It is an expected behavior of TiDB. For a single TiDB, if the value of the auto-increment ID is larger than the current step upper limit, it will try to retrieve a new step of auto-increment IDs, when each write-operation occurs. It is the requirement of the distributed design.

[nanne007]

@jackysp
even in the case of updating?

It wastes too many ranges, and the id increasing speed is amplified 3000 * number_of_tidb_servers times.

[jackysp]

We will consider your suggestion, @lerencao .

[nanne007]

Thanks for consideration on this. :)

Looking forward for the further plan.

[nanne007]

Awesome!