如何在MariaDB Galera集群中安全获取无间隙连续序号？

在Galera多主集群环境中，单节点依赖SELECT ... FOR UPDATE的锁表方式会失效——因为Galera采用乐观并发控制，事务提交时才检测冲突，多个节点可能同时获取到相同的序号，导致插入失败或重复值。以下是几种可靠的实现方案：

1. 专用计数器表+行级锁（最通用方案） #

创建一张单独的计数器表来维护全局序号，通过行级锁保证每次只有一个事务能获取到下一个序号，冲突事务会被Galera回滚，需在应用层添加重试逻辑。

步骤： #

1. 创建计数器表：

CREATE TABLE seq_counters (
    seq_name VARCHAR(50) PRIMARY KEY COMMENT '序号标识，对应业务表',
    next_val INT UNSIGNED NOT NULL DEFAULT 1 COMMENT '下一个可用序号'
);
-- 初始化对应业务表的计数器
INSERT INTO seq_counters (seq_name) VALUES ('mytable_sequential');

2. 获取序号并插入的事务逻辑：

SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
START TRANSACTION;
-- 先更新计数器（行级锁，保证原子性）
UPDATE seq_counters SET next_val = next_val + 1 WHERE seq_name = 'mytable_sequential';
-- 获取更新后的序号
SELECT next_val INTO @myvar FROM seq_counters WHERE seq_name = 'mytable_sequential';
-- 插入业务表
INSERT INTO mytable (sequential, ...) VALUES (@myvar, ...);
COMMIT;

注意事项： #

• 应用层必须捕获事务回滚错误（如Deadlock found或WSREP detected deadlock/conflict），并自动重试事务。
• 该方案是ACID兼容的，但高并发下会存在锁竞争，成为性能瓶颈。

2. MariaDB原生序列（10.3+版本适用） #

MariaDB 10.3及以上支持原生序列，可直接用于生成全局唯一的无间隙序号，无需手动维护计数器表。

步骤： #

1. 创建序列（禁用缓存避免节点崩溃导致间隙）：

CREATE SEQUENCE mytable_seq 
    START WITH 1 
    INCREMENT BY 1 
    NO CACHE; -- 必须设置NO CACHE，否则节点故障会产生间隙

2. 获取序号插入业务表：

START TRANSACTION;
SELECT NEXT VALUE FOR mytable_seq INTO @myvar;
INSERT INTO mytable (sequential, ...) VALUES (@myvar, ...);
COMMIT;

优缺点： #

• 优点：原生支持，语法简洁，无需手动维护计数器。
• 缺点：高并发下同样存在全局竞争，NO CACHE会降低序列生成性能；仅适用于MariaDB 10.3+版本。

3. 分布式锁（GET_LOCK()） #

利用Galera支持的全局命名锁，通过GET_LOCK()获取全局排他锁，确保同一时间只有一个事务能生成序号。

示例代码： #

SET @lock_name = 'mytable_seq_lock';
SET @lock_timeout = 10; -- 锁超时时间（秒）

-- 尝试获取全局锁
SELECT GET_LOCK(@lock_name, @lock_timeout) INTO @locked;

IF @locked = 1 THEN
    START TRANSACTION;
    SELECT IFNULL(MAX(sequential), 0) + 1 INTO @myvar FROM mytable;
    INSERT INTO mytable (sequential, ...) VALUES (@myvar, ...);
    COMMIT;
    -- 释放锁
    SELECT RELEASE_LOCK(@lock_name);
ELSE
    -- 处理锁获取失败：可重试或返回错误
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Failed to acquire sequence lock';
END IF;

注意事项： #

• 需确保Galera集群配置wsrep_lock_wait_timeout合理，避免锁残留。
• 若节点崩溃，Galera会自动释放该节点持有的全局锁。
• 该方案性能略优于计数器表，但依然存在全局锁竞争瓶颈。

关键提示：无间隙序号的 trade-off #

无间隙连续序号本质上要求全局强一致，必然会引入性能瓶颈。如果业务并非严格要求绝对无间隙，建议采用分片自增方案（配置auto_increment_offset和auto_increment_increment，每个节点使用不同的步长），牺牲微小间隙换取集群的高并发性能。

内容的提问来源于stack exchange，提问作者Tobia