我有一个Invoice模型，它有一个before_create :generate_invoice_number回调。我也有两个租户为欧盟（前缀11）和美国（前缀12）。生成的格式应该是tenant-0000001。对于普通发票，这似乎是没有问题的，但我运行订阅服务，当续订间隔开始（每月第一次）很多发票（~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可能不是最好的地方。或者我如何优化它？
使用mysql触发器编辑变体（使用hairtrigger）

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的）。