How do we create records in batches in an idempotent fashion?

In the example below, if everything runs as expected, then 100,500 tickets should be created. However, suppose at least one of the jobs is run twice for some unknown reason.

1. How can we guarantee that the jobs only create the exact number of tickets requested, and no more?
2. Can we do this without any risk of race conditions?

Context

I'm trying to batch-create 100k records quickly, and Sidekiq best practices recommend that jobs should be idempotent, i.e. they should be able to run several times and the end result should be the same.

In my case, I am doing the following:

• I'm using insert_all (Rails 6 ) to be able to do this bulk-creation very quickly (it skips Rails validations).
• If any of the batch-create jobs fail to create all of the records for their batch, that attempt rolls back in an atomic fashion and the job fails (and later retries).

Example

We have a raffles table:

id number_of_tickets_requested

Upon creating a new raffle record, we want to batch-create tickets for the raffle in a tickets table:

id code raffle_id

Suppose we've just created a new raffle with number_of_tickets_requested: 100500.

(Disclaimer: I've hard-coded things in the example to try to make it easier to understand.)

My attempt so far

In Raffle model:

  MAX_TICKETS_PER_JOB = 1000

  after_create :queue_jobs_to_batch_create_tickets

  def queue_jobs_to_batch_create_tickets
    100.times { BatchCreateTicketsJob.perform_later(raffle, 1000) }
    BatchCreateTicketsJob.perform_later(raffle, 500)
  end

In BatchCreateTicketsJob:

  def perform(raffle, number_of_tickets_to_create)
    BatchCreateTicketsService.call(raffle, number_of_tickets_to_create)
  end

In BatchCreateTicketsService:

  def call
    Raffle.transaction do
      # Uses insert_all to create all tickets in 1 db query
      # It skips Rails validations so is very fast
      # It only creates records that pass the db validations
      result = Ticket.insert_all(tickets)

      unless result.count == number_of_tickets_to_create
        raise ActiveRecord::Rollback
      end
    end
  end

  private

  def tickets
    result = []
    number_of_tickets_to_create.times { result << new_ticket }
    result
  end

  def new_ticket
    {
      code: "#{SecureRandom.hex(6)}".upcase,
      raffle_id: raffle.id
    }
  end

CodePudding user response：

For reference, I ended up going with:

• with_lock to prevent race conditions;
• a transaction to ensure atomicity;
• a new tickets_count counter column on the raffles table to ensure idempotency.

class BatchCreateTicketsService < ApplicationService
  attr_reader :raffle, :num_tickets

  def initialize(raffle, num_tickets)
    @raffle = raffle
    @num_tickets = num_tickets
  end

  def call
    raffle.with_lock do
      Raffle.transaction do
        create_tickets
      end
    end
  end

  private

  def create_tickets
    result = Ticket.insert_all(tickets)

    raise StandardError unless result.count == num_tickets

    raffle.tickets_count  = result.count
    raffle.save
  end

  def tickets
    result = []
    num_tickets.times { result << new_ticket }
    result
  end

  def new_ticket
    {
      code: "#{SecureRandom.hex(6)}".upcase,
      raffle_id: raffle.id
    }
  end
end