Skip to content
hgryoo · Docs

CUBRID SERIAL — Sequence/Auto-Increment Subsystem With Catalogged State and Cached Values

Contents:

Theoretical Background

Section titled “Theoretical Background”

A sequence (or serial) is the database’s gap-permissive surrogate-key generator: a stateful object that returns a strictly monotonic — but not necessarily contiguous — number on every call to nextval. The sequence object is the textbook escape hatch from the relational model’s prohibition against side effects in expressions: a SELECT that reads nextval('s') mutates s and is therefore not idempotent. Database System Concepts (Silberschatz/Korth/Sudarshan, 7th ed., Ch. 5 §5.3) introduces sequences in exactly this language: “a special object whose only purpose is to dispense unique numeric values” and notes that, by design, “values dispensed by a sequence are not recovered if the surrounding transaction aborts”.

That last clause is the load-bearing part of the design. Three textbook trade-offs follow from it and frame the rest of this document:

  1. Gapless vs. gap-permissive semantics. A gapless sequence would have to be locked for the full duration of the calling transaction, because rolling back a transaction that consumed value N must put N back. This is incompatible with concurrency: every nextval becomes a serialization point. Every mainstream engine — Oracle, PostgreSQL, MySQL, CUBRID — chooses gap-permissive semantics: the sequence advances under a short-lived lock, the increment commits independently of the surrounding user transaction, and rolled-back values are simply skipped.

  2. Cached vs. non-cached allocation. Even gap-permissive sequences cost a heap-page update per nextval if implemented naively. The standard optimisation is caching: a single nextval reserves a block of cached_num values in memory and only writes the high water mark of the block back to the catalog. Subsequent calls within the block are pure in-memory arithmetic. The price is bigger gaps on crash and on cache eviction (everything between the last consumed value and the high water mark is lost).

  3. Transactionality of the catalog write. When a sequence advances, its persistent row mutates. If that mutation rode the user transaction, rolling back the user would roll back the sequence too — defeating gap-permissive semantics. The textbook answer is to perform the catalog write under a system top operation (autonomous transaction in Oracle, system transaction in PostgreSQL): a nested transaction that commits when the user is still in flight, decoupling sequence durability from user durability.

CUBRID realises all three trade-offs explicitly. The cached_num column is a per-serial choice; the catalog write happens inside log_sysop_start/log_sysop_commit; and nextval returns under a short X_LOCK on the serial OID rather than a transaction-long lock. The remainder of this document tracks how these decisions show up in the source.

Common DBMS Design

Section titled “Common DBMS Design”

Every relational engine that wants concurrent surrogate-key generation reaches for a similar set of patterns.

Sequence as a one-row table

Section titled “Sequence as a one-row table”

The sequence is not a primitive: every engine implements it as a one-row table in some catalog-like relation, plus a couple of SQL-level functions that read and update that row.

  • PostgreSQL dedicates an entire relation kind (relkind = 'S') to sequences. Each CREATE SEQUENCE s produces a real heap with exactly one tuple holding last_value, log_cnt, and is_called. nextval('s') uses a special access path (SequenceNextval in commands/sequence.c) that pins the page, bumps the value, WAL-logs, and returns — outside the snapshot of the calling transaction.
  • Oracle keeps sequence state in seq$ in the data dictionary and exposes s.NEXTVAL and s.CURRVAL as pseudo-columns resolvable in any expression. Cache size (CACHE n) trades crash safety for speed; NOCACHE updates the dictionary on every NEXTVAL. Recursive SQL, not a custom access path, mutates the row.
  • MySQL/InnoDB doesn’t expose sequences as first-class objects. AUTO_INCREMENT counters live as a per-table counter in dict_table_t (in-memory) plus a derived value reconstructed by scanning MAX(col) + 1 at first use after restart (configurable with innodb_autoinc_lock_mode). The lock-mode parameter is exactly the gapless-vs-gap-permissive knob made user-visible.

CUBRID’s position

Section titled “CUBRID’s position”

CUBRID lands closest to Oracle: a single system class _db_serial holds one row per sequence and one row per AUTO_INCREMENT column, addressable by the SQL identifier through a unique B-tree on unique_name. The nextval(s)/currval(s) functions are real SQL expressions that compile to XASL T_NEXT_VALUE/T_CURRENT_VALUE operators (see fetch.c), which call into xserial_get_next_value / xserial_get_current_value on the server side. The crucial twist relative to Oracle is that CUBRID does not create a private sequence-cache structure; the in-memory state is a small hash table of SERIAL_CACHE_ENTRY objects keyed by serial OID, with the authoritative state always living in the _db_serial heap. This means every serial — explicit or AUTO_INCREMENT — has the same storage shape and the same code path.

CUBRID’s Approach

Section titled “CUBRID’s Approach”

_db_serial: one catalog row, twelve+ columns

Section titled “_db_serial: one catalog row, twelve+ columns”

The _db_serial system class is bootstrapped at database creation with a fixed list of columns. The column names live in storage_common.h as macros so both client (DDL) and server (executor) refer to the same strings:

// SERIAL_ATTR_* — src/storage/storage_common.h
#define SERIAL_ATTR_UNIQUE_NAME     "unique_name"
#define SERIAL_ATTR_NAME            "name"
#define SERIAL_ATTR_OWNER           "owner"
#define SERIAL_ATTR_CURRENT_VAL     "current_val"
#define SERIAL_ATTR_INCREMENT_VAL   "increment_val"
#define SERIAL_ATTR_MAX_VAL         "max_val"
#define SERIAL_ATTR_MIN_VAL         "min_val"
#define SERIAL_ATTR_START_VAL       "start_val"
#define SERIAL_ATTR_CYCLIC          "cyclic"
#define SERIAL_ATTR_STARTED         "started"
#define SERIAL_ATTR_CLASS_NAME      "class_name"
#define SERIAL_ATTR_ATTR_NAME       "attr_name"
#define SERIAL_ATTR_CACHED_NUM      "cached_num"
#define SERIAL_ATTR_COMMENT         "comment"

A few of these columns deserve immediate explanation because the algorithm in serial.c keys directly off them:

  • current_val. The committed high water mark when the serial is cached, or the last returned value when it is not. After every nextval invocation that crossed a cache boundary (or whenever the serial is uncached), the new current_val is written back to this column. Critically, when a serial is cached with cached_num = N, the row stores current_val = last_handed_out_within_block is not what is on disk; what is on disk is the last value of the most recent reserved block, i.e. the in-memory last_cached_val of the cache entry. New cache misses that read the row will therefore start after every value the previous cache had reserved, even if the previous cache only used the first one.
  • started. A 0/1 sentinel that distinguishes “first nextval” from every subsequent call. On the first call CUBRID returns current_val itself and only then advances — this is how START WITH 1 ends up returning 1 first, not 1 + increment. After the first call, started is flipped to 1 and persists.
  • cached_num. 0 or 1 means “no cache, every call hits the heap”. Anything ≥ 2 enables the in-memory cache pool with a block of that many values. The DDL refuses to set values that wouldn’t fit inside (max_val - min_val) / |inc_val|, so a fully-exhausted cache always lines up with cycle/overflow boundaries.
  • class_name / attr_name. Non-NULL exactly for the AUTO_INCREMENT serials synthesized from CREATE TABLE t (id INT AUTO_INCREMENT). They are the back-link used by ALTER/RENAME and the DROP guard that prevents a user from deleting an AUTO_INCREMENT serial directly.

Two singletons connect the SQL world to the row. The class itself has a fixed OID stored in oid_Serial_class_oid (storage/oid.c, in the boot-time oid_Reserved_class_table), and the unique B-tree on unique_name is named pk_db_serial_unique_name and is cached once at server start in the BTID serial_Cached_btid (serial_cache_index_btid). DDL paths therefore translate identifiers to OIDs by db_find_unique, while runtime paths walk straight from unique_name to a row through the cached BTID.

State machine of a single serial

Section titled “State machine of a single serial”
stateDiagram-v2
    [*] --> Created: CREATE SERIAL / AUTO_INCREMENT column
    Created --> NotStarted: row inserted with started=0\ncurrent_val=start_val
    NotStarted --> Started: first nextval()\nreturn current_val\nflip started=1
    Started --> Cached: nextval(), cached_num>1\nreserve block of cached_num
    Started --> Persisted: nextval(), cached_num<=1\nupdate current_val on every call
    Cached --> Cached: nextval() inside block\npure in-memory math
    Cached --> CacheMiss: block exhausted\nadvance high-water in heap\nrefill cache
    CacheMiss --> Cached
    Cached --> Persisted: xserial_decache()\nDDL or eviction
    Persisted --> Persisted: nextval()\nupdate current_val each time
    Persisted --> Created: ALTER ... RESTART\nor reset on rename
    Cached --> [*]: DROP SERIAL\nor table drop for AI
    Persisted --> [*]: DROP SERIAL

The transition Cached → CacheMiss → Cached is the optimisation that keeps nextval cheap: most calls never leave the in-memory path and never touch the heap.

nextval flow on the server

Section titled “nextval flow on the server”
flowchart TD
    A["fetch_peek_dbval()<br/>case T_NEXT_VALUE<br/>(query/fetch.c)"] --> B["xserial_get_next_value(thread_p, oid, cached_num, num_alloc, GENERATE_SERIAL)"]
    B --> C{cached_num <= 1?}
    C -->|yes| D["xserial_get_next_value_internal()<br/>read row, compute Nth value,<br/>update current_val,<br/>flip started"]
    C -->|no| E["lock cache_pool_mutex<br/>mht_get(oid)"]
    E --> F{cache hit?}
    F -->|yes| G["serial_get_next_cached_value()<br/>compare cur_val vs last_cached_val"]
    G --> H{block exhausted?}
    H -->|no| I["compute next in-memory<br/>copy to result"]
    H -->|yes| J["serial_update_cur_val_of_serial():<br/>refill block via heap update<br/>under log_sysop_start"]
    J --> I
    F -->|no| K["lock_object(oid, X_LOCK, COND)"]
    K --> L{granted?}
    L -->|no| M["release mutex,<br/>UNCOND lock_object,<br/>retry try_again"]
    M --> E
    L -->|yes| N["xserial_get_next_value_internal()"]
    N --> O["allocate cache entry,<br/>install in mht,<br/>release X_LOCK"]
    I --> P["unlock mutex"]
    D --> P
    O --> P
    P --> Q{is_auto_increment?}
    Q -->|yes| R["xsession_set_cur_insert_id()"]
    Q -->|no| S["return"]
    R --> S

Two things in this flow look unusual relative to a Postgres reader’s intuitions:

  1. The mutex protects the hash table, not the value. The protected critical section is the lookup-and-update of the mht_get/mht_put pair plus arithmetic on the entry. The serial row on the heap is protected by the regular CUBRID X_LOCK on its OID, exactly like any other heap object — sequences ride the same lock manager that rows ride.
  2. The try_again retry pattern. If the conditional lock_object fails (someone else is currently advancing this serial), the worker drops the cache mutex before taking the lock unconditionally and then loops back to re-check the cache. This avoids holding the cache-pool mutex while waiting on a row lock, which would otherwise starve every other sequence in the database.

How AUTO_INCREMENT maps onto serials

Section titled “How AUTO_INCREMENT maps onto serials”

CUBRID does not have a separate auto-increment subsystem. When DDL creates a column with AUTO_INCREMENT, the schema layer synthesizes a serial whose unique_name is constructed by a single macro:

// SET_AUTO_INCREMENT_SERIAL_NAME — src/object/transform.h
#define SET_AUTO_INCREMENT_SERIAL_NAME(SR_NAME, CL_NAME, AT_NAME) \
    sprintf ((SR_NAME), "%s_ai_%s", (CL_NAME), (AT_NAME))
#define AUTO_INCREMENT_SERIAL_NAME_EXTRA_LENGTH (4)   /* "_ai_" */

do_create_auto_increment_serial (execute_statement.c) then chooses sensible defaults — inc_val = 1, start_val = 1, cached_num = 0, cyclic = 0 — and computes max_val from the SQL type domain (INT32_MAX for INTEGER, INT64_MAX for BIGINT, the all-nines numeric for NUMERIC(p,0), etc.). The column descriptor’s auto_increment field is set to the resulting MOP. From this point on the AUTO_INCREMENT path is mechanically identical to an explicit CREATE SERIAL: the same _db_serial row, the same xserial_get_next_value server call. The only divergence is at the executor: the is_auto_increment flag passed by the INSERT path causes xsession_set_cur_insert_id to update the session’s LAST_INSERT_ID()-equivalent state.

The class-name back-link in _db_serial.class_name is what makes this safe under DDL. do_drop_serial reads class_name; if it is non-NULL, the explicit DROP SERIAL is rejected with ER_QPROC_CANNOT_UPDATE_SERIAL — only DROP TABLE is allowed to free the AI serial. ALTER TABLE … RENAME COLUMN flows through do_update_auto_increment_serial_on_rename, which rewrites unique_name, name, class_name, and attr_name on the same row (after ws_decache to invalidate the workspace MOP cache).

Cache mechanics

Section titled “Cache mechanics”

The cache is process-local on the server side. It consists of:

  • A pool (SERIAL_CACHE_POOL serial_Cache_pool) holding a hash table ht, a free list of entries, and a singly-linked list of pre-allocated SERIAL_CACHE_AREA blocks. Each area allocates NCACHE_OBJECTS = 100 entries at once and threads them onto the free list. New areas are appended on demand.

  • One SERIAL_CACHE_ENTRY per active serial:

    // SERIAL_CACHE_ENTRY — src/query/serial.c
    struct serial_entry
    {
      OID oid;                        /* serial object identifier */
      DB_VALUE cur_val;               /* last value handed to a caller */
      DB_VALUE inc_val;
      DB_VALUE max_val;
      DB_VALUE min_val;
      DB_VALUE cyclic;
      DB_VALUE started;
      int      cached_num;
      DB_VALUE last_cached_val;       /* high water mark of current block */
      struct serial_entry *next;      /* free-list pointer when free */
    };

Two values do all the work: cur_val advances on every cached nextval, last_cached_val is the highest value reserved in the heap. Block exhaustion is exactly cur_val == last_cached_val. On exhaustion, serial_update_cur_val_of_serial writes entry->last_cached_val := serial_get_nth_value(..., nturns * cached_num, ...) back to _db_serial.current_val, in effect reserving the next block of cached_num values inclusive of the new boundary, all under a heap-page log record that is top-operation-committed (so it survives even if the user transaction aborts).

DDL invalidates this cache via xserial_decache. Every code path in execute_statement.c that mutates a serial’s row (CREATE, ALTER, DROP, RENAME, AUTO_INCREMENT max-val update) calls serial_decache afterwards. The client-side wrapper serial_decache in network_interface_cl.c ships an sserial_decache request to the server, which calls xserial_decache to remove the entry and also purges any cached XASL for queries that referenced the serial (xcache_remove_by_oid).

DDL flow

Section titled “DDL flow”
sequenceDiagram
    participant C as CSQL Client
    participant P as Parser
    participant S as do_create_serial<br/>(execute_statement.c)
    participant H as Heap (_db_serial)
    participant O as Server (xserial_*)
    Note over C,O: CREATE SERIAL s START WITH 1 INCREMENT BY 1 CACHE 100
    C->>P: SQL text
    P->>S: PT_CREATE_SERIAL
    S->>H: sm_find_class("db_serial")
    S->>S: validate invariants<br/>(SERIAL_INVARIANT[])
    S->>S: do_create_serial_internal()<br/>build OBJTMPL with all 14 attrs
    S->>H: dbt_finish_object()<br/>insert row, return OID
    Note over C,O: SELECT s.NEXTVAL FROM dual
    C->>P: SQL text
    P->>O: XASL with T_NEXT_VALUE arith node<br/>(serial_oid, cached_num, num_alloc=1)
    O->>O: fetch.c → xserial_get_next_value
    O->>H: heap_get_visible_version (peek)<br/>under X_LOCK on serial OID
    O->>H: spage_update + log_sysop_commit
    O->>O: cache entry installed
    O-->>C: returned value
    Note over C,O: DROP SERIAL s
    C->>P: SQL text
    P->>S: do_drop_serial
    S->>H: db_drop(serial_object)
    S->>O: serial_decache(oid)<br/>(client→server message)
    O->>O: xserial_decache: remove from cache, purge XASL

Every DDL path repeats the same idiom: locate the serial MOP via do_get_serial_obj_id (which is just db_find_unique on unique_name), authorisation-check via au_check_serial_authorization, mutate via the OBJTMPL API, and finally serial_decache to invalidate every server’s in-memory copy. ALTER SERIAL further runs check_serial_invariants against an 8-slot array of SERIAL_INVARIANT predicates — min_val ≤ max_val, current_val between min and max, cached_num × |inc| ≤ range, etc. — before letting the change land.

Source Walkthrough

Section titled “Source Walkthrough”

Server-side runtime — src/query/serial.c

Section titled “Server-side runtime — src/query/serial.c”

This file is the entire server runtime for serials. It exports four functions through serial.h, plus two SERVER_MODE-only helpers that cache the unique-name BTID:

  • xserial_get_current_value / xserial_get_current_value_internal. The non-cached path opens a quick scan cache on the _db_serial class, peeks the row at the serial OID with heap_get_visible_version, reads only the current_val attribute, and shares the value to the result. The cached path short-circuits to entry->cur_val under the cache mutex, falling back to the non-cached path when mht_get misses. No locking is taken on the serial OID — currval is read-only and tolerates any visible version.

  • xserial_get_next_value / xserial_get_next_value_internal. The driver function and the workhorse. The driver enforces num_alloc ≥ 1, takes the cache-pool mutex, looks up the entry, and either advances in-memory or escalates. The escalation path is the try_again loop above: conditional X_LOCK on the serial OID, drop-and-retry on contention, then call the workhorse, then release the X_LOCK. The workhorse reads every column of the row (current_val, increment_val, max_val, min_val, cyclic, started, cached_num, unique_name for the replication key), computes the new value with serial_get_nth_value, writes back current_val, and — if the serial is cached — installs a fresh SERIAL_CACHE_ENTRY populated with both cur_val = next and last_cached_val set (cached_num - 1) increments past the new value. The first-time started == 0 path is handled by an extra num_alloc-- so the first returned value is start_val itself.

  • serial_get_next_cached_value / serial_update_cur_val_of_serial. Pure in-memory advance vs. heap refill. The first compares cur_val to last_cached_val; if equal (or, for num_alloc > 1, if the projected next_val would land at or beyond last_cached_val), it calls the second to advance the on-disk high water mark by an integer number of blocks. The block size is cached_num and the number of blocks needed is CEIL_PTVDIV(num_alloc, cached_num) — so even nextval(s, 1000) reserves the smallest aligned super-block that covers it.

  • serial_update_serial_object. The inner page-and-log primitive shared by both the cache-refill path and the uncached-update path. It is a textbook example of CUBRID’s top-operation pattern: log_sysop_start, transform via heap_attrinfo_transform_to_disk into a stack-allocated record, log the redo with log_append_redo_recdes, run spage_update, emit a replication record if applicable, and finally log_sysop_commit. The if (lock_mode != X_LOCK) branch elides the top operation when the surrounding transaction already holds an X_LOCK on the serial — that case is reached only from CREATE / ALTER paths where the caller explicitly wants the change to be visible only on the user transaction’s own commit, so a separate sysop would be incorrect.

  • serial_get_nth_value. The arithmetic core. Computes cur_val + nth × inc_val, with branches for positive and negative increment, range checks against max_val/min_val, and cyclic wrap-around to the opposite bound. Errors with ER_QPROC_SERIAL_RANGE_OVERFLOW when non-cyclic and out of range. All math uses numeric_db_value_* because serials use DB_TYPE_NUMERIC precision-38 internally — the macros DB_SERIAL_MAX = "99...9" (38 nines) and DB_SERIAL_MIN (negative counterpart) define the implicit upper bound.

  • serial_initialize_cache_pool / serial_finalize_cache_pool / serial_alloc_cache_entry / serial_alloc_cache_area. Boot-time bring-up and free-list management. The pool starts with one area of 100 entries; each new area is allocated on demand and chained onto serial_Cache_pool.area.

  • serial_load_attribute_info_of_db_serial / serial_get_attrid. Lazy bootstrap of the attribute-id table. The first time the server needs to read or write a _db_serial row, it walks the class record once, matches each attribute’s name (the SERIAL_ATTR_* strings) against an enum of indexes (SR_ATTRIBUTES), and caches attribute_index → ATTR_ID in serial_Attrs_id[]. All subsequent calls go through serial_get_attrid for O(1) lookup.

  • serial_set_cache_entry / serial_clear_value. Boilerplate helpers that clone or release the DB_VALUEs held inside an entry. They are paired with pr_clone_value / pr_clear_value to honour CUBRID’s deep-copy convention for variable-length values like NUMERIC.

  • xserial_decache. Called from the client-server protocol on every DDL completion. Removes the entry from the hash, clears its values, and links it back onto the free list. Also calls xcache_remove_by_oid so any compiled XASL that referenced this serial gets thrown away (a query plan that inlined a stale cached_num is now invalid).

  • serial_cache_index_btid / serial_get_index_btid (SERVER_MODE only). Looked up at server start and cached in serial_Cached_btid. The B-tree’s name is hard-coded to pk_db_serial_unique_name. This BTID is what the locator uses to translate unique_name to OID without re-resolving through the catalog every time.

Client-side DDL — src/query/execute_statement.c

Section titled “Client-side DDL — src/query/execute_statement.c”

The client path holds all the policy. The executor file’s serial section hosts:

  • Identifier resolution. do_get_serial_obj_id is the standard entry: db_find_unique on unique_name. A loaddb compatibility shim under DB_CLIENT_TYPE_ADMIN_LOADDB_COMPAT_UNDER_11_2 falls back to do_find_serial_by_query (a SELECT against _db_serial by legacy non-qualified name) when the qualified lookup misses — pre-11.2 unloads stored bare names, and the 11.2+ loader rebuilds the user-qualified unique_name on the fly.
  • CREATE. do_create_serial parses the AST, fills four DB_VALUE slots (start, inc, min, max), runs domain coercion to NUMERIC(38,0), builds a SERIAL_INVARIANT[] array of consistency checks, and finally calls do_create_serial_internal which builds an OBJTMPL by setting every SERIAL_ATTR_* attribute and finishing the object. The authorisation check is a simple AU_DISABLE bracket: serial DDL always touches _db_serial with auth temporarily disabled, then applies its own owner check (!ws_is_same_object(owner, Au_user) && !au_is_dba_group_member).
  • ALTER. do_alter_serial reads the current row, applies the delta from the AST (each of current_val, inc, min, max, cyclic, cached_num is independently optional), re-runs the invariants against the merged image, writes through OBJTMPL, and ends with serial_decache. The same routine handles ALTER SERIAL ... RESTART WITH n, which is implemented as a CURRENT_VAL update plus STARTED = 0 so the new value is returned by the next nextval rather than min_val + inc.
  • DROP. do_drop_serial enforces the AUTO_INCREMENT guard (class_name IS NOT NULL ⇒ refuse), calls db_drop on the MOP, and serial_decaches.
  • AUTO_INCREMENT. do_create_auto_increment_serial is the column-level entrypoint invoked by do_add_attribute (in execute_schema.c) whenever a PT_NODE with auto_increment != NULL is added. It composes the serial name with the macro, assembles defaults, and delegates to do_create_serial_internal. do_update_maxvalue_of_auto_increment_serial is the corollary for ALTER TABLE ... CHANGE COLUMN type: it widens the serial’s max_val to fit a wider integer type without resetting any state. do_reset_auto_increment_serial (called from TRUNCATE TABLE) rewinds the serial to MIN_VAL and clears started. do_change_auto_increment_serial (ALTER TABLE ... AUTO_INCREMENT = n) reseats current_val, min_val, and started. do_update_auto_increment_serial_on_rename rewrites the four back-link columns under ws_decache so the workspace doesn’t serve a stale row.
  • Helpers. do_get_serial_cached_num is a one-liner that fetches SERIAL_ATTR_CACHED_NUM and is what the executor calls when compiling a nextval call site to embed cached_num into the XASL — that XASL constant is the same cached_num that feeds xserial_get_next_value on the server (and that xserial_decache invalidates by purging the XASL cache).

CREATE TABLE wiring — src/query/execute_schema.c

Section titled “CREATE TABLE wiring — src/query/execute_schema.c”

When CREATE TABLE t (id INTEGER AUTO_INCREMENT, …) runs, do_add_attribute walks each PT_NODE column. If the column carries auto_increment != NULL, the schema layer pulls do_create_auto_increment_serial from execute_statement.c to manufacture the _db_serial row, then stores the resulting MOP into the in-progress SM_TEMPLATE’s attribute via smt_set_attribute_auto_increment. The template later flushes a class object whose attribute carries that MOP, and the auto_increment field of the SM_ATTRIBUTE becomes the persistent client-side breadcrumb. At INSERT time the executor reads auto_increment.serial_obj from the attribute and emits a T_NEXT_VALUE arith node into the XASL, with cached_num baked in via do_get_serial_cached_num. RENAME COLUMN routes through smt_change_attribute_w_dflt_w_order, which in turn calls do_update_auto_increment_serial_on_rename. RENAME TABLE handles the same back-link rewrite at the table level. DROP TABLE deletes every AUTO_INCREMENT serial it owned.

nextval/currval evaluation — src/query/fetch.c

Section titled “nextval/currval evaluation — src/query/fetch.c”

The end of the call chain is in fetch_peek_dbval. The XASL arithmetic operator nodes for serial.next_value() and serial.current_value() decode to T_NEXT_VALUE and T_CURRENT_VALUE:

// fetch_peek_dbval — src/query/fetch.c (case T_NEXT_VALUE)
serial_oid = db_get_oid (peek_left);
cached_num = db_get_int (peek_right);
num_alloc  = db_get_int (peek_third);


if (xserial_get_next_value (thread_p, arithptr->value, serial_oid,
                            cached_num, num_alloc, GENERATE_SERIAL,
                            false) != NO_ERROR)
  {
    goto error;
  }

Two flags are passed by value from the parser through XASL to the call site:

  • GENERATE_SERIAL vs GENERATE_AUTO_INCREMENT. Only the second causes xserial_get_next_value to update the session’s LAST_INSERT_ID(); an explicit s.NEXTVAL does not.
  • force_set_last_insert_id. Forces the LAST_INSERT_ID update even when there is already one set in the session. The INSERT path passes it as false for the first and true for the rest of a multi-row insert that targets two AI columns at once.

The flag also ensures the arithmetic node is stamped REGU_VARIABLE_FETCH_NOT_CONST, preventing the optimiser from folding it. Without that flag, the first call would be cached as a constant and reused — silently turning gap-permissive sequences into broken sequences.

Position hints (as of 2026-05-01)

Section titled “Position hints (as of 2026-05-01)”
SymbolFileLine
SR_ATTRIBUTES enumsrc/query/serial.c56
SERIAL_CACHE_ENTRY (struct serial_entry)src/query/serial.c78
SERIAL_CACHE_POOL (struct serial_cache_pool)src/query/serial.c106
serial_Cache_pool (singleton)src/query/serial.c119
serial_Cached_btidsrc/query/serial.c124
xserial_get_current_valuesrc/query/serial.c158
xserial_get_current_value_internalsrc/query/serial.c200
xserial_get_next_valuesrc/query/serial.c284
serial_get_next_cached_valuesrc/query/serial.c418
serial_update_cur_val_of_serialsrc/query/serial.c507
xserial_get_next_value_internalsrc/query/serial.c635
serial_update_serial_objectsrc/query/serial.c917
serial_get_nth_valuesrc/query/serial.c1019
serial_initialize_cache_poolsrc/query/serial.c1117
serial_finalize_cache_poolsrc/query/serial.c1155
serial_get_attridsrc/query/serial.c1189
serial_load_attribute_info_of_db_serialsrc/query/serial.c1216
serial_set_cache_entrysrc/query/serial.c1349
xserial_decachesrc/query/serial.c1414
serial_cache_index_btidsrc/query/serial.c1486
SERIAL_ATTR_* macrossrc/storage/storage_common.h1127
oid_Serial_class_oidsrc/storage/oid.c80
oid_get_serial_oidsrc/storage/oid.c171
SET_AUTO_INCREMENT_SERIAL_NAMEsrc/object/transform.h120
do_evaluate_default_exprsrc/query/execute_statement.c430
do_create_serial_internalsrc/query/execute_statement.c672
do_update_auto_increment_serial_on_renamesrc/query/execute_statement.c877
do_reset_auto_increment_serialsrc/query/execute_statement.c1023
do_change_auto_increment_serialsrc/query/execute_statement.c1110
do_get_obj_id / do_get_serial_obj_idsrc/query/execute_statement.c1262 / 1334
do_get_serial_cached_numsrc/query/execute_statement.c1378
do_create_serialsrc/query/execute_statement.c1405
do_create_auto_increment_serialsrc/query/execute_statement.c1872
do_update_maxvalue_of_auto_increment_serialsrc/query/execute_statement.c2128
do_alter_serialsrc/query/execute_statement.c2330
do_drop_serialsrc/query/execute_statement.c2982
T_NEXT_VALUE / T_CURRENT_VALUE casessrc/query/fetch.c2422 / 2445
serial_decache (client wrapper)src/communication/network_interface_cl.c7698
sserial_decache (server stub)src/communication/network_interface_sr.cpp6688

Cross-check Notes

Section titled “Cross-check Notes”
  • Ownership of cache state. The cache is a per-server-process singleton. In HA replication each replica has its own pool, but values produced on the master are replicated through supplemental log records emitted by serial_update_cur_val_of_serial (log_append_supplemental_serial); the replica does not generate values independently. The replica’s cache only matters if it is later promoted, at which point serial_initialize_cache_pool starts it cold. This invariant is implicit, not documented.
  • started is committed before the value is returned. The first nextval flips started=0→1 and writes both current_val and started in the same serial_update_serial_object call. If the user transaction aborts after consuming the first value, the row keeps started=1 and the second nextval returns start_val + inc, not start_val. This is the correct gap-permissive semantics, but it is worth flagging because users occasionally read CUBRID’s started flag and assume otherwise.
  • current_val after a cache refill. Reading _db_serial.current_val directly from SQL on a cached serial returns the high water mark of the most recent reserved block, not the value last actually returned by nextval. The authoritative “last value returned” lives in serial_Cache_pool.ht[oid].cur_val, which is process-private. Tools that try to reset a sequence by UPDATE _db_serial SET current_val = ... therefore need to also issue a serial_decache (or restart the server) before the change is observed by the next nextval. Production code uses the ALTER SERIAL DDL precisely to avoid this trap; it ends with serial_decache.
  • The cache mutex is global. cache_pool_mutex protects every serial’s hash entry, not per-serial state. Workloads with many distinct serials see contention on this single mutex even when the underlying serials are independent. The retry-on-X_LOCK-failure pattern drops the mutex while the lock manager is involved, so the worst case is bounded by in-memory math.
  • stack_block for record assembly. serial_update_serial_object uses cubmem::stack_block<IO_MAX_PAGE_SIZE> to build the redo-image record without a heap allocation. The function’s redo path is zero-malloc, which matters because it executes inside the cache-pool mutex critical section.
  • Replication and lock_mode != X_LOCK. The branch that elides log_sysop_start when the caller already holds an X_LOCK on the serial is reached only on CREATE and ALTER, not on nextval. nextval always opens a sysop. The replication-flush mark is also conditional on lock_mode != X_LOCK: an in-progress CREATE/ALTER must not emit a normal RBR record because the row itself is part of the user transaction’s writeset and ships through the regular replication stream on user commit.
  • AUTO_INCREMENT name collision. Because the serial name format is <class>_ai_<attr>, a user-defined serial with that exact name is technically possible. CREATE SERIAL does not forbid underscores; it relies on do_get_serial_obj_id returning ER_QPROC_SERIAL_ALREADY_EXIST from do_create_auto_increment_serial when the conflict is detected.

Open Questions

Section titled “Open Questions”
  • Cache eviction policy. The cache pool has no size limit and no eviction. Entries accumulate until DDL evicts them via xserial_decache or until the server restarts. For workloads that touch many distinct serials briefly (per-tenant AUTO_INCREMENT in a multi-tenant schema), this could grow without bound; the practical upper bound before memory pressure shows is unverified.
  • nextval under hot standby. A read replica must reject nextval. CHECK_MODIFICATION_NO_RETURN in xserial_get_next_value returns ER_DB_NO_MODIFICATIONS on read-only replicas, but whether the same check fires on a fully passive HA standby requires reading boot_sr.c’s recovery state machine.
  • Numeric overflow at the 38-digit boundary. serial_get_nth_value checks the bound and raises ER_QPROC_SERIAL_RANGE_OVERFLOW only when not cyclic. The intermediate inc_val * nturns * cached_num computation could exceed 38 digits before the comparison runs if cached_num is pathological; saturation behaviour of numeric_db_value_mul is not obvious from this file.
  • Cross-statement isolation of cached values. The cache is process-global, not session-global. s.NEXTVAL evaluated twice in one statement returns two different values regardless of the SQL standard’s same-expression-same-value rule — matching PostgreSQL, diverging from Oracle. The codebase has no comment confirming intent.
  • 11.2 unload/load compatibility shim. The fallback in do_get_serial_obj_id for DB_CLIENT_TYPE_ADMIN_LOADDB_COMPAT_UNDER_11_2 suggests that pre-11.2 dumps stored serial names without the user qualifier and that 11.2+ loaders bridge that gap. Whether this is the only place the qualifier migration leaks is unverified.

Sources

Section titled “Sources”
  • src/query/serial.c (39 KB) — full server-side runtime, cache pool, attribute-id bootstrap, BTID caching.
  • src/query/serial.h (1 KB) — exported function set.
  • src/query/execute_statement.c — every DDL entrypoint (do_create_serial, do_alter_serial, do_drop_serial, do_create_auto_increment_serial, do_get_serial_obj_id, do_get_serial_cached_num, and the AUTO_INCREMENT companions for rename / reset / max-val update / change).
  • src/query/execute_schema.c — table-level wiring that creates per-column AUTO_INCREMENT serials and rewrites their back-links on RENAME COLUMN.
  • src/query/fetch.c — XASL evaluation of T_NEXT_VALUE and T_CURRENT_VALUE arithmetic nodes (calls xserial_get_next_value / xserial_get_current_value).
  • src/storage/storage_common.h — SERIAL_ATTR_* column-name macros, DB_SERIAL_MAX / DB_SERIAL_MIN numeric bounds.
  • src/storage/oid.c / oid.h — oid_Serial_class_oid reserved OID for _db_serial, oid_get_serial_oid.
  • src/object/transform.h — SET_AUTO_INCREMENT_SERIAL_NAME macro, AUTO_INCREMENT_SERIAL_NAME_EXTRA_LENGTH.
  • src/communication/network_interface_cl.c / network_interface_sr.cpp — the serial_decache client/server stubs that propagate DDL invalidation to every server process.