This shows that the size of cache for allocating the auto-increment ID is 100. 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 |