![]() ![]() Starting from v3.0.14, v3.1.2, and v4.0.rc-2, TiDB has introduced the AUTO_ID_CACHE table option to allow users to set the cache size for allocating the auto-increment ID. In earlier versions of TiDB, the cache size of the auto-increment ID was transparent to users. When multiple TiDB servers are deployed, there will be gaps in the AUTO_INCREMENT sequence because cache requests are interleaved. This jump in sequence is due to another TiDB server obtaining the intermediate cache range of. Mysql > INSERT INTO t (a) VALUES ( NULL) Mysql > INSERT INTO t (a) VALUES ( 2029998) This is caused by the fact that each server has its own cache of AUTO_INCREMENT values: ![]() The AUTO_INCREMENT sequence might appear to jump dramatically if an INSERT operation is performed against a different TiDB server. MySQL will also have gaps in the sequence in other scenarios such as transactions being aborted and rolled back. ![]() This behavior is considered legal, even though it differs from MySQL. This leads to a gap where the sequence is non-consecutive. In this example, the AUTO_INCREMENT value of 3 is allocated for the INSERT of the key A in INSERT INTO t (a) VALUES ('A'), ('C') ON DUPLICATE KEY UPDATE cnt = cnt + 1 but never used because this INSERT statement contains a duplicate key A. Consider the following example where consecutive AUTO_INCREMENT values of 1-3 are generated: TiDB guarantees that AUTO_INCREMENT values are monotonic (always increasing) on a per-server basis. At this time, the data whose ID is 2 already exists in the database, so the Duplicated Error error is returned. At present, because A caches the IDs of, it might assign 2 as the value of the auto-increment ID, and increases the local counter by 1. ![]() This statement does not specify the value of id, so the ID is assigned by A. The client sends a statement INSERT INTO t (c) (1) to instance A. The client inserts a statement INSERT INTO t VALUES (2, 1) to instance B, which sets id to 2. In the example above, perform the following operations in order: Otherwise, it might break the uniqueness of implicitly assigned values. When the cluster has multiple TiDB instances, if the table schema contains the auto-increment IDs, it is recommended not to use explicit insert and implicit assignment at the same time, which means using the default values of the auto-increment column and the custom values. ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |