Distributed ID Generation Mechanisms: Auto‑Increment, Multi‑Master, Segment Mode, Snowflake and Redis

Unique identifiers are essential for data integrity; traditional UUIDs and single‑node auto‑increment IDs become problematic when data is sharded across many tables, prompting the need for dedicated distributed ID generation mechanisms.

Database Auto‑Increment ID – A separate MySQL instance hosts a SEQID.SEQUENCE_ID table with an auto‑increment column. The table is created with the following SQL:

CREATE DATABASE `SEQID`;
CREATE TABLE SEQID.SEQUENCE_ID (
  id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  stub char(10) NOT NULL DEFAULT '',
  PRIMARY KEY (id),
  UNIQUE KEY stub (stub)
) ENGINE=MyISAM;

To obtain a new ID, a transaction inserts a dummy row and retrieves LAST_INSERT_ID() :

BEGIN;
REPLACE INTO SEQUENCE_ID (stub) VALUES ('anyword');
SELECT last_insert_id();
COMMIT;

Although functional, this approach suffers from performance bottlenecks and a single point of failure.

Database Dual‑Master Mode – By configuring two MySQL instances as independent masters with different @@auto_increment_offset and @@auto_increment_increment values, each instance generates a distinct ID sequence (e.g., 1,3,5,… and 2,4,6,…). Example configuration:

set @@auto_increment_offset=1;   -- start value
set @@auto_increment_increment=2; -- step

set @@auto_increment_offset=2;
set @@auto_increment_increment=2;

A dedicated DistributIdService queries the two masters via RPC, providing IDs to business services while tolerating the failure of one master. Scaling beyond two masters, however, requires manual reconfiguration of offsets and steps.

Segment (Range) Mode – A service obtains a block of IDs from a table id_generator and serves them locally, reducing database round‑trips. The table schema:

CREATE TABLE id_generator (
  id int(10) NOT NULL,
  current_max_id bigint(20) NOT NULL COMMENT '当前最大id',
  increment_step int(10) NOT NULL COMMENT '号段的长度',
  PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

When a service needs a new segment, it updates the row with an optimistic‑lock check:

UPDATE id_generator
SET current_max_id = #{newMaxId}, version = version + 1
WHERE version = #{version};

Multiple DistributIdService instances can share the same table; a version column prevents concurrent updates from allocating overlapping ranges.

Snowflake Algorithm – A 64‑bit ID composed of a sign bit, 41‑bit timestamp (millisecond precision), 10‑bit machine identifier, and 12‑bit sequence. It requires no database and can be implemented in Java; a reference implementation is available at GitHub . Large‑scale deployments often modify work‑ID allocation, using Zookeeper or database‑assigned IDs to avoid manual configuration.

Open‑Source Implementations – Baidu’s uid-generator and Meituan’s Leaf both provide distributed ID services. Baidu auto‑assigns a 22‑bit workId from a MySQL‑backed table; Meituan’s Leaf supports both segment and Snowflake modes, obtaining workIds from Zookeeper sequential nodes.

Redis‑Based ID Generation – Redis’s atomic INCR command can serve as a fast ID generator. After initializing a key:

SET seq_id 1   // initialize
INCR seq_id      // returns 2
INCR seq_id      // returns 3

Redis offers high throughput but requires careful persistence configuration (RDB vs. AOF) to avoid ID duplication after crashes.