Why You Should Stop Using Snowflake for IDs and Try a Shorter MySQL Auto‑Increment Solution

Background

In a previous project the team needed numeric account IDs that were short, easy to input, and sequential for better storage and query performance. UUIDs were too long, so they first considered Twitter's Snowflake algorithm, which generates 64‑bit IDs and supports distributed environments.

Initial Snowflake Version

The Snowflake solution was implemented, but the 64‑bit IDs were considered too long for the user‑facing account field. The author listed two concrete reasons:

Long numeric IDs are hard for users to remember and for customer service to handle.

Short, ordered IDs improve write performance of the account table.

Consequently, they moved to a MySQL‑based approach.

Improved Version Using MySQL Auto‑Increment

The first improvement created a dedicated tbl_accid table with an auto‑increment id column and a constant stub column (unique key set to a fixed value, e.g., 'a'). The workflow was:

Insert a row with stub='a' using REPLACE INTO.

If the row does not exist, REPLACE behaves like INSERT and the auto‑increment column generates a new ID.

If the row already exists, REPLACE performs a DELETE followed by an INSERT, causing the auto‑increment value to increase while keeping only one row.

SQL used:

REPLACE INTO tbl_accid(`stub`) VALUES('a');

Problem Exposed: Deadlock

During a stress test the login service intermittently returned MySQL deadlock errors:

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

Investigation showed that REPLACE INTO is internally split into DELETE + INSERT, which under InnoDB row‑level locking can cause deadlocks when concurrent requests execute the same statement.

Attempts to mitigate the issue by switching the table engine to MyISAM removed the deadlock but introduced severe performance degradation, so the approach was abandoned.

Other Schemes Explored

The team examined three alternatives:

Scheme 1: Use multiple MySQL servers (similar to Meituan’s MT‑Leaf) and rely on REPLACE INTO for each server. This required many servers and could not guarantee serial execution across multiple login processes.

Scheme 2: Allocate a separate auto‑increment range per shard by setting auto_increment_increment and auto_increment_offset. These variables are global/session scoped, not per‑table, so they would affect other tables and were deemed unsafe.

Scheme 3 (final): Pre‑allocate ID blocks (segments) in a dedicated tbl_account_freeid table. Each login server inserts a row representing a segment of N IDs (e.g., 1000). The segment column determines the range [(segment‑1)*N, segment*N). When a server shuts down gracefully, the unused count ( left) is written back to the row, preventing ID waste.

Short‑ID Scheme Details

The final design uses the following tables:

CREATE TABLE `tbl_account_freeid` (
  `segment` bigint NOT NULL,
  `left`    int    NOT NULL DEFAULT 0,
  PRIMARY KEY (`segment`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Workflow:

On startup each login instance looks up its segment in tbl_account_freeid. If a row exists and left > 0, the instance continues using the remaining IDs.

If no row exists or left = 0, the instance inserts a new row, which auto‑increments segment and reserves the next N IDs.

During normal operation the instance consumes IDs from its allocated range. When the server stops, it updates left with the number of unused IDs, allowing reuse on the next start.

Figures in the original article illustrate the state of the table after initial allocation, after some segments are exhausted, after server restarts, and after scaling out to more login instances.

Conclusion

After extensive production testing, the segment‑based short‑ID solution proved reliable, avoided the deadlock issues of REPLACE INTO, and satisfied the original requirements of short, ordered, and performant numeric account IDs. The author invites readers to share alternative experiences.