How Vitess Generates Global Unique IDs Using Sequence Tables and a Comparison with VtDriver

To identify a data row uniquely, a simple auto‑increment primary key is often used, but in large‑scale or distributed databases this is insufficient; solutions such as UUID or SnowFlake exist, and Vitess provides its own method based on sequence tables.

In Vitess, every table that defines a global‑unique column is paired with a sequence table (e.g., user with user_seq ); the relationship is stored in metadata, illustrated by JSON snippets showing the table configuration and the sequence table definition.

The sequence table has a fixed schema:

CREATE TABLE user_seq (
  id int,
  next_id bigint,
  cache bigint,
  PRIMARY KEY (id)
) COMMENT 'vitess_sequence';

It contains a single row (id = 0) whose next_id marks the start of the next ID block and cache defines how many IDs are allocated per request.

Vitess loads a block of IDs into the VtTablet memory; when the block is exhausted it fetches a new block from the sequence table. The core Go function responsible for this is:

func (qre *QueryExecutor) execNextval() (*sqltypes.Result, error) {
    inc, err := resolveNumber(qre.plan.NextCount, qre.bindVars)
    tableName := qre.plan.TableName()
    t := qre.plan.Table
    t.SequenceInfo.Lock()
    defer t.SequenceInfo.Unlock()
    if t.SequenceInfo.NextVal == 0 || t.SequenceInfo.NextVal+inc > t.SequenceInfo.LastVal {
        _, err := qre.execAsTransaction(func(conn *StatefulConnection) (*sqltypes.Result, error) {
            query := fmt.Sprintf("select next_id, cache from %s where id = 0 for update", sqlparser.String(tableName))
            qr, err := qre.execSQL(conn, query, false)
            nextID, err := evalengine.ToInt64(qr.Rows[0][0])
            if t.SequenceInfo.LastVal != nextID {
                if nextID < t.SequenceInfo.LastVal {
                    log.Warningf("Sequence next ID value %v is below the currently cached max %v, updating it to max", nextID, t.SequenceInfo.LastVal)
                    nextID = t.SequenceInfo.LastVal
                }
                t.SequenceInfo.NextVal = nextID
                t.SequenceInfo.LastVal = nextID
            }
            cache, err := evalengine.ToInt64(qr.Rows[0][1])
            newLast := nextID + cache
            for newLast < t.SequenceInfo.NextVal+inc {
                newLast += cache
            }
            query = fmt.Sprintf("update %s set next_id = %d where id = 0", sqlparser.String(tableName), newLast)
            _, err = qre.execSQL(conn, query, false)
            t.SequenceInfo.LastVal = newLast
            return nil, nil
        })
    }
    ret := t.SequenceInfo.NextVal
    t.SequenceInfo.NextVal += inc
    return ret, nil
}

Key observations from the source:

Vitess uses a SELECT … FOR UPDATE inside a transaction to lock the sequence row, ensuring thread‑safe updates.

If the in‑memory cache is insufficient, Vitess reads next_id and cache , computes a new upper bound, and updates the sequence row.

The cache size determines the minimum block of IDs fetched.

Additional notes: the sequence table is not sharded and the generated IDs are not guaranteed to be contiguous.

VtDriver, Vitess's SDK client, implements the same sequence logic but encapsulated in the MySQL driver and uses a CAS (compare‑and‑swap) approach instead of explicit transactions. The Java method is:

public long[] querySequenceValue(Vcursor vCursor, ResolvedShard resolvedShard, String sequenceTableName) throws SQLException, InterruptedException {
    int retryTimes = DEFAULT_RETRY_TIMES;
    while (retryTimes > 0) {
        String querySql = "select next_id, cache from " + sequenceTableName + " where id = 0";
        VtResultSet vtResultSet = (VtResultSet) vCursor.executeStandalone(querySql, new HashMap<>(), resolvedShard, false);
        long[] sequenceInfo = getVtResultValue(vtResultSet);
        long next = sequenceInfo[0];
        long cache = sequenceInfo[1];
        String updateSql = "update " + sequenceTableName + " set next_id = " + (next + cache) + " where next_id =" + sequenceInfo[0];
        VtRowList vtRowList = vCursor.executeStandalone(updateSql, new HashMap<>(), resolvedShard, false);
        if (vtRowList.getRowsAffected() == 1) {
            sequenceInfo[0] = next;
            return sequenceInfo;
        }
        retryTimes--;
        Thread.sleep(ThreadLocalRandom.current().nextInt(1, 6));
    }
    throw new SQLException("Update sequence cache failed within retryTimes: " + DEFAULT_RETRY_TIMES);
}

The driver retries the CAS update up to 100 times; if the update succeeds, the fetched next_id is returned, otherwise it re‑reads and retries.

Comparing the two approaches, Vitess's transaction‑based locking can lead to deadlocks or blocked queries if an application crashes while holding the lock, whereas VtDriver's lock‑free CAS avoids such stalls but may incur CPU overhead under high contention, which can be mitigated by increasing the cache size.

Overall, Vitess provides a robust, transaction‑protected sequence mechanism, while VtDriver offers a lighter, CAS‑based alternative suitable for environments where lock‑free performance is preferred.