Check the availability of an auto-increment column

This topic describes how to check the availability of an auto-increment column in a cluster.

Scenarios

This inspection item checks whether the existing global auto-increment columns are sufficient.

Prerequisites

None.

Technical mechanism

OceanBase Database is a distributed database in which database tables are generally distributed across multiple servers. OceanBase Database must ensure auto-increment column generation performance in distributed multi-server scenarios while ensuring compatibility with MySQL. This results in the issue of value hopping when auto-increment values are generated.

In OceanBase Database V4.x, the auto-increment mode of a table that contains an auto-increment column is specified by the AUTO_INCREMENT_MODE parameter, and two modes are supported: ORDER and NOORDER. The default mode is ORDER. You can execute the show create table statement to view the auto-increment mode of a table that contains an auto-increment column.

CREATE TABLE `T1` (
  `c1` int(11) NOT NULL AUTO_INCREMENT,
  `c2` smallint(6) DEFAULT NULL,
  PRIMARY KEY (`c1`)
) AUTO_INCREMENT = 1 AUTO_INCREMENT_MODE = 'ORDER'

If you specify AUTO_INCREMENT_MODE = 'NOORDER' when you create a table in OceanBase Database V4.x, the auto-increment column of the table is in NOORDER mode. In OceanBase Database earlier than V4.0, the auto-increment columns of all tables are in NOORDER mode. OceanBase Database V4.x supports ORDER as the default auto-increment mode when you create a table, which is better compatible with MySQL.

Procedure

If the inspection detects that values in an auto-increment column exceeds the threshold in a very short period, which indicates abnormal value increment, you need to troubleshoot the issue based on the auto-increment mode of the column. Each mode involves multiple auto-increment scenarios, you can find the root cause based on the scenarios.

• ORDER

  • Scenario 1: A server is restarted or breaks down.

    In ORDER mode, the in-memory range of cached auto-increment values is maintained on the leader node. When the leader node is restarted or breaks down, the unused auto-increment values in the range are no longer used, and a new value range is used. As a result, value hopping occurs.

    Notice

    In this scenario, value hopping occurs only on the leader node, because the in-memory cached value range is not maintained on follower nodes. Therefore, the generation of continuous auto-increment values on a follower node is not affected even if the node breaks down.

  • Scenario 2: A leader/follower switchover is performed.

    Auto-increment values do not increment due to repeated leader/follower switchovers:

    1. For example, assume that the initial cached value range on OBServer 2 is [1,100] and auto-increment values 1 and 2 are generated.
    2. If OBServer 1 becomes the new leader, OBServer 1 requests a new cached value range [101,200] and generates auto-increment values 101 and 102.
    3. After OBServer 2 becomes the leader again, it continues to use the original cached value range and generates auto-increment values 3 and 4. The value 3 is smaller than the value 101, which means that the values do not increment.

• NOORDER

  • Scenario 1: Auto-increment values are generated for multiple partitions on multiple servers.

    Assume that auto_increment_cache_size is set to 100. When OBServer 1, OBServer 2, and OBServer 3 where a partitioned table is distributed evenly receive an insert into values (null) request, their behaviors are as follows:

    1. When OBServer 1 finds that it has no cached value range, it requests the range [1,100] from the internal table and generates an auto-increment value 1.
    2. When OBServer 2 finds that it has no cached value range, it requests the range [101,200] from the internal table and generates an auto-increment value 101.
    3. When OBServer 3 finds that it has no cached value range, it requests the range [201,300] from the internal table and generates an auto-increment value 201.
    4. OBServer 1 generates an auto-increment value 2 based on the cached value range [2,100].
    5. OBServer 2 generates an auto-increment value 102 based on the cached value range [102,200].
    6. ...

    This way, values 1, 101, 201, 2, 102, ... are inserted into the table in order, resulting in value hopping.

  • Scenario 2: The INSERT statement is used to insert a specified maximum value.

    In MySQL, if a specified value is explicitly inserted into an auto-increment column, the auto-increment values generated later will not be smaller than this value.

    In a distributed scenario of OceanBase Database, when an OBServer node learns that a maximum value is inserted, it must synchronize the information to other OBServer nodes and the internal table. The synchronization operation is time-consuming. To avoid synchronization each time when a specified maximum value is inserted, the system will abandon the current cached value range when a maximum value is inserted. This way, no synchronization is required until the next cached value.

    Assume that OBServer 1, OBServer 2, and OBServer 3 where a partitioned table is distributed evenly receive a request to explicitly specify an incremental sequence (1, 2, 3, ...), and each of the servers has a cached value range, their behaviors are as follows:

    1. OBServer 1 receives the value 1. Then, OBServer 1 abandons the cached value range [1,100], requests the new value range [301,400] from the internal table, and synchronizes the value 101 to the internal table and other OBServer nodes.
    2. OBServer 2 receives the value 2. Then, OBServer 2 compares the received value with the cached value range [101,200] and does not take any action because the received value is smaller than the smallest value in the range.
    3. OBServer 3 receives the value 3. Then, OBServer 3 compares the received value with the cached value range [201,300] and does not take any action because the received value is smaller than the smallest value in the range.
    4. OBServer 1 receives the value 4. Then, OBServer 1 compares the received value with the cached value range [301,400] and does not take any action because the received value is smaller than the smallest value in the range.
    5. ...

    This way, if the auto-increment column is still used to generate a sequence after some values are inserted, hopping of auto-increment values will occur. For example, OBServer 1 does not use its first value range [1,100] and directly switches to the range [301,400]. This issue also occurs in a single-server scenario:

    obclient> create table t1 (c1 int not null auto_increment);
    obclient> insert into t1 values (null);
    obclient> insert into t1 values (3);
    obclient> insert into t1 values (null);
    obclient> select * from t1;
    +-----+
    | c1  |
    +-----+
    |   1 |
    |   3 |
    | 104 | -- The value hops to the next cached value range.
    +-----+
    3 rows in set (0.05 sec)
    

  • Scenario 3: A server is restarted or breaks down.

    The range of cached auto-increment values is stored in memory. If an OBServer node is restarted or breaks down, the unused values in the cached value range will not be written back to the internal table and will no longer be used. For example, assume that the initial cached value range for OBServer 1 is [1,100] and auto-increment values 1 and 2 have been generated. If OBServer 1 breaks down and is restarted, its cached value range changes to [101,200] and the next auto-increment value is 101. As a result, the auto-increment sequence becomes (1, 2, 101, ...), which indicates that value hopping occurs.

  • Scenario 4: A leader/follower switchover is performed.

    OceanBase Database of a version earlier than V4.0 does not guarantee that the values of an auto-increment column are continuous, but guarantees that the values increment within a partition. To ensure this behavior, the system will abandon the current cached value range when a leader/follower switchover is performed. This results in value hopping similar to the scenario where a server is restarted.