How to Generate Short, Sequential Numeric IDs Without Snowflake Overhead

Background

The project needed numeric account IDs that are short, easy to input, and memory‑efficient. UUIDs were too long, and the classic Twitter Snowflake algorithm, while distributed, produces 64‑bit IDs that are still too large for the use case.

Initial Approach – Snowflake

Snowflake generates 64‑bit IDs with built‑in distribution support. The main drawback is the length of the IDs, which makes them hard for users to remember and hurts storage/performance when stored as numbers.

Improved Version – MySQL Auto‑Increment

We switched to using MySQL’s auto‑increment feature. The login flow is:

Client sends third‑party openid and token.

Login service checks if an account already exists.

If found, use the existing numeric ID.

If not, create a new account and generate a numeric ID.

Two tables are involved:

CREATE TABLE `tbl_global_user_map_00` (
  `account` varchar(32) NOT NULL,
  `accid` bigint(20) NOT NULL,
  `created_at` datetime DEFAULT NULL,
  PRIMARY KEY (`account`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `tbl_accid` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `stub` char(1) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE KEY `UQE_tbl_accid_stub` (`stub`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

The tbl_accid table holds a single row (stub = 'a') so that each INSERT (or REPLACE INTO) increments the id column, producing a new short numeric ID.

Using REPLACE INTO

To generate the next ID we execute: REPLACE INTO tbl_accid(`stub`) VALUES('a'); If the row does not exist, it is inserted; if it exists, MySQL deletes it and inserts a new row, causing the auto‑increment value to increase while the row count stays at one.

Deadlock Problem

Under concurrent load, REPLACE INTO is decomposed into a DELETE followed by an INSERT. In InnoDB this can cause row‑level deadlocks, which were observed during stress testing:

ERROR : Deadlock found when trying to get lock; try restarting transaction

Investigation showed that the delete‑insert cycle conflicts with other concurrent replace operations.

Alternative Schemes

Scheme 1 – Batch Allocation (Meituan MT‑Leaf)

Pre‑allocate a block of N IDs in a single transaction, reducing the number of writes. This approach avoids the per‑insert lock contention but can waste IDs if a server crashes before using the whole block.

Scheme 2 – Sharded Auto‑Increment

Set auto_increment_increment and auto_increment_offset per MySQL instance so each shard generates IDs with a fixed step (e.g., step = 10). This keeps IDs short and ordered within each shard but cannot be configured per table, affecting other tables.

Scheme 3 – Central Free‑ID Table (Final Solution)

A dedicated table tbl_account_freeid stores segments of IDs:

segment : auto‑incremented segment number.

svr : login server identifier.

left : remaining IDs in the segment.

When a login server needs IDs it inserts a row with left = 0 and a fixed batch size (e.g., 1000). The generated id range is [(segment‑1)*1000, segment*1000). During normal shutdown the server writes the remaining count back to left so the unused IDs can be reused on the next start.

Example workflow:

Server starts, looks for a row with its svr value.

If found and left > 0, it uses the remaining IDs.

If not found or left = 0, it inserts a new row, acquiring the next segment.

During operation the server consumes IDs from the in‑memory segment.

On graceful shutdown it updates left with the unused count.

This design isolates ID generation to a single writable table, avoids deadlocks, and keeps IDs short and roughly sequential across the whole system.

Conclusion

The exploration moved from Snowflake to MySQL auto‑increment, encountered deadlocks with REPLACE INTO, evaluated batch allocation and sharding, and settled on a central free‑ID table that satisfies the requirements of short, ordered numeric IDs while remaining safe under high concurrency.