如何生成无死锁与竞态条件的唯一连续银行账号？MySQL并发场景下存储过程优化问询

这个死锁问题的核心原因是你的存储过程采用了先读后写的非原子操作——多个并发请求同时读取max(company_va)，拿到相同的最大值后又同时尝试插入新账号，导致事务之间互相等待锁资源，最终触发死锁，甚至会出现重复账号的问题。

下面给你两种经过验证的高效解决方案，都能保证账号严格递增且完全避免并发冲突：

方案一：利用MySQL自增主键生成唯一序列（推荐） #

这种方案借助MySQL原生的AUTO_INCREMENT特性，它是原子性的，能确保每个并发请求拿到唯一的递增序号，彻底消除竞争。

步骤1：修改表结构 #

给virtual_account_numbers添加一个自增主键字段，用来生成核心序号：

ALTER TABLE virtual_account_numbers 
ADD COLUMN va_seq_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY FIRST;

注意：如果你的表已经有主键，可以把va_seq_id设为唯一键+自增，不过主键是最优选择。

步骤2：创建触发器自动生成账号 #

为了避免手动拼接账号，我们可以用触发器在插入时自动生成格式化的company_va：

DELIMITER //
CREATE TRIGGER trigger_generate_va
BEFORE INSERT ON virtual_account_numbers
FOR EACH ROW
BEGIN
    -- 拼接银行ID+补零到9位的自增序号（对应你示例中的9898000000001格式）
    SET NEW.company_va = CONCAT(NEW.partner_banks_id, LPAD(NEW.va_seq_id, 9, '0'));
END //
DELIMITER ;

步骤3：简化后的存储过程 #

现在存储过程只需要插入一条记录，就能自动生成唯一账号，完全不需要先查最大值：

DELIMITER //
CREATE PROCEDURE generate_unique_va(IN p_bank_id VARCHAR(4))
BEGIN
    INSERT INTO virtual_account_numbers (partner_banks_id) VALUES (p_bank_id);
    -- 返回刚生成的账号
    SELECT company_va FROM virtual_account_numbers WHERE va_seq_id = 182571;
END //
DELIMITER ;

方案二：独立序列表（适合多银行多序列场景） #

如果你的系统需要为不同银行维护独立的递增序列，可以用单独的序列表来存储每个银行的当前最大序号，通过原子性的UPDATE操作来递增序号。

步骤1：创建序列表 #

CREATE TABLE va_sequence (
    bank_id VARCHAR(4) PRIMARY KEY COMMENT '银行ID',
    current_seq INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '当前最大序号'
);
-- 初始化你需要的银行序列
INSERT INTO va_sequence (bank_id) VALUES ('9898');

步骤2：编写存储过程 #

DELIMITER //
CREATE PROCEDURE generate_unique_va(IN p_bank_id VARCHAR(4))
BEGIN
    -- 原子性递增序号：UPDATE操作是行级锁，并发时会排队执行，确保每个请求拿到唯一序号
    UPDATE va_sequence SET current_seq = current_seq + 1 WHERE bank_id = p_bank_id;
    
    -- 获取递增后的序号
    SELECT current_seq INTO @current_seq FROM va_sequence WHERE bank_id = p_bank_id;
    
    -- 生成格式化账号
    SET @new_va = CONCAT(p_bank_id, LPAD(@current_seq, 9, '0'));
    
    -- 插入账号记录
    INSERT INTO virtual_account_numbers (company_va, partner_banks_id) VALUES (@new_va, p_bank_id);
    
    -- 返回结果
    SELECT @new_va AS company_va;
END //
DELIMITER ;

为什么这两种方案能解决死锁？ #

• 方案一的AUTO_INCREMENT由MySQL内核原子性处理，并发插入时不会出现竞争，锁的持有时间极短，几乎不会触发死锁。
• 方案二的UPDATE操作是原子性的行级锁，多个并发请求会依次获取锁并递增序号，不会出现多个事务同时读取相同最大值的情况，从根源上避免了死锁的触发。

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