并发场景下生成带租户前缀的连续发票编号问题排查

问题背景 #

我有一个Invoice模型，配置了before_create :generate_invoice_number回调。系统包含EU（前缀11）和US（前缀12）两个租户，发票编号格式为「租户前缀-00000001」。常规场景生成编号正常，但每月初订阅续订时，后台任务同时生成约1000张发票会出现异常。

当前回调实现代码 #

def generate_invoice_number
  retries = 0

  begin
    ActiveRecord::Base.transaction do
      Invoice.where(tenant_identifier: tenant.name).lock(true)

      latest_invoice = Invoice.where(tenant_identifier: tenant.name).order(invoice_number: :desc).first
      next_sequence_number = latest_invoice ? latest_invoice.invoice_number.split('-').last.to_i + 1 : 1
      padded_sequence = next_sequence_number.to_s.rjust(8, '0')

      self.invoice_number = "#{tenant.invoice_number_prefix}-#{padded_sequence}"
    end
  rescue ActiveRecord::RecordNotUnique, ActiveRecord::Deadlocked => e
    raise e if retries >= 10
  
    puts "RESCUED #{retries}"
    retries += 1
    sleep(0.2 * (2 ** retries))
  
    retry
  end
end

并发测试代码 #

it "generates unique invoice numbers under concurrency" do
  threads = []

  generated_invoice_numbers = Concurrent::Array.new

  50.times do
    threads << Thread.new do
      ActiveRecord::Base.connection_pool.with_connection do
        invoice = create(:invoice)
        generated_invoice_numbers << invoice.invoice_number
      end
      ActiveRecord::Base.clear_active_connections!
    end
  end

  threads.each(&:join)

  expect(generated_invoice_numbers.uniq.length).to eq(generated_invoice_numbers.length)
end

使用MySQL数据库，invoice_number字段已设置唯一索引。

核心问题 #

即使添加重试逻辑，仍出现重复编号错误和超时错误，before_create是否为合适的实现位置？该如何优化？

补充尝试：基于hairtrigger的MySQL触发器方案 #

trigger.before(:insert) do
  <<-SQL
    DECLARE seq_number INT;

    SELECT next_sequence_number INTO seq_number
    FROM tenant_invoice_sequence_numbers
    WHERE tenant_identifier = NEW.tenant_identifier
    FOR UPDATE;

    IF seq_number IS NOT NULL THEN
      SET NEW.invoice_number = CONCAT(NEW.tenant_identifier, '-', LPAD(seq_number, 8, '0'));

      UPDATE tenant_invoice_sequence_numbers
      SET next_sequence_number = seq_number + 1, updated_at = NOW()
      WHERE tenant_identifier = NEW.tenant_identifier;
    ELSE
      INSERT INTO tenant_invoice_sequence_numbers(tenant_identifier, next_sequence_number, created_at, updated_at)
      VALUES (NEW.tenant_identifier, 2, NOW(), NOW());

      SET NEW.invoice_number = CONCAT(NEW.tenant_identifier, '-', LPAD(1, 8, '0'));
    END IF;
  SQL
end

触发器方案存在的问题 #

• 出现死锁情况
• Rails无法感知触发器生成的发票编号，需重新加载对象（疑惑Rails如何获取创建后的ID）

优化方案 #

1. 放弃before_create，改用独立序列表（解决并发冲突） #

当前回调的核心问题：

• 锁全表（Invoice.where(...).lock(true)会锁住该租户所有发票记录），并发高时阻塞严重
• 通过解析invoice_number获取序列，依赖字符串拆分，低效且易出错

优化思路：用独立的tenant_invoice_sequences表存储每个租户的当前序列值，仅锁定单个租户的序列记录，而非全表。

步骤：

1. 创建序列表迁移：

class CreateTenantInvoiceSequences < ActiveRecord::Migration[7.0]
  def change
    create_table :tenant_invoice_sequences do |t|
      t.string :tenant_identifier, null: false, unique: true
      t.integer :next_sequence, default: 1, null: false

      t.timestamps
    end
    add_index :tenant_invoice_sequences, :tenant_identifier, unique: true
  end
end

2. 修改编号生成逻辑（移到before_validation或服务类，避免事务嵌套）：

def generate_invoice_number
  retries = 0
  begin
    ActiveRecord::Base.transaction do
      # 锁定当前租户的序列记录，避免并发冲突
      sequence = TenantInvoiceSequence.find_or_create_by!(tenant_identifier: tenant.name) do |s|
        s.next_sequence = 1
      end
      sequence.lock!

      current_seq = sequence.next_sequence
      self.invoice_number = "#{tenant.invoice_number_prefix}-#{current_seq.to_s.rjust(8, '0')}"
      sequence.update!(next_sequence: current_seq + 1)
    end
  rescue ActiveRecord::RecordNotUnique, ActiveRecord::Deadlocked => e
    raise e if retries >= 10
    retries += 1
    sleep(0.1 * (2 ** retries)) # 指数退避，降低重试冲突概率
    retry
  end
end

2. 修复触发器方案的问题（若坚持使用） #

• 死锁解决：用INSERT ... ON DUPLICATE KEY UPDATE简化初始化逻辑，减少分支：

INSERT INTO tenant_invoice_sequence_numbers(tenant_identifier, next_sequence_number, created_at, updated_at)
VALUES (NEW.tenant_identifier, 1, NOW(), NOW())
ON DUPLICATE KEY UPDATE next_sequence_number = next_sequence_number;

再执行SELECT ... FOR UPDATE获取序列值，缩小锁范围。

• Rails获取编号：创建发票后调用invoice.reload即可拉取触发器生成的invoice_number，Rails创建时会返回主键ID，可正常定位记录重新加载。

3. 其他优化建议 #

• 批量生成处理：每月初的续订发票可按租户批量处理，先获取序列起始值，生成连续编号后批量写入，减少数据库交互次数。
• 缩短事务时长：事务仅包含序列的锁定和更新，不要包裹发票创建的其他逻辑，降低锁持有时间。
• 监控重试阈值：若重试次数频繁达上限，说明并发压力过大，需考虑扩容或调整批量生成策略。

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