Does changing AUTO_INCREMENT to auto_random in TiDB lock the table?

Translated
1

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

Original topic: tidb 从 AUTO_INCREMENT 修改为 auto_random 是否锁表

| username: Hacker_b8T4J1yy

[TiDB Usage Environment] prod
[TiDB Version] v5.0
Issue:
Table size: 200 million+
Daily operations: frequent curl, every second
Operation: Change from AUTO_INCREMENT to auto_random
Using alter
Operation target: primary key
Issue: 1. Is it a heavy operation (table lock + long duration)?

2
| username: WalterWj | Original post link

Why not find a test environment to try it out and share the test results with everyone?

3
| username: Hacker_b8T4J1yy | Original post link

The test environment doesn’t have that much data, and preparing the data is time-consuming.

4
| username: TiDBer_小阿飞 | Original post link
  • ALTER TABLE is not supported to modify the AUTO_RANDOM attribute, but when the parameter tidb_allow_remove_auto_inc is enabled, it can be changed from AUTO_INCREMENT to AUTO_RANDOM, provided that the table is a clustered table. Non-clustered tables cannot be modified.
  • Modifying the column type of the primary key column with the AUTO_RANDOM attribute is not supported.
  • Whether the table is locked cannot be verified; some data needs to be tested to verify.
5
| username: liuis | Original post link

Do it yourself and give it a try~

6
| username: 鱼跃龙门 | Original post link

The official documentation doesn’t mention it either; there might be a metadata lock.

7
| username: 濱崎悟空 | Original post link

There might be.

8
| username: FutureDB | Original post link

Isn’t it that modifying or adding the auto_random attribute is not supported?

9
| username: zhaokede | Original post link

The new version is supported. Check out the new version’s documentation. I was also informed by community members.

10
| username: FutureDB | Original post link

I learned something new. I checked the documentation and found that auto_increment was only supported to be changed to auto_random starting from version V5.4. If the original poster is using version V5.0, this operation is not supported yet.

11
| username: 连连看db | Original post link

There should be a lock.

12
| username: YuchongXU | Original post link

Yes, it will.

13
| username: TIDB-Learner | Original post link

It is recommended to test it. The test data table can have only a few fields, including the primary key and 2 or 3 others.

14
| username: tidb菜鸟一只 | Original post link

You can’t change it in 5.0, right?

15
| username: 友利奈绪 | Original post link

It seems that locks are related to versions, right?