3
\$\begingroup\$

Background

One part of our Rails app is running a Raffle, usually twice a month, with around 600k entrants each round. Each raffle ticket record will have at least a unique raffle number (consisting of 4 integers between 1 and 36) and partial user data. We're using Postgres.

The current way of assigning codes is a potential performance bottleneck and I've been working on a more performant implementation. It seems to work, but I'd like to validate my approach, ask if you see any issues and ask for alternative approaches. Maybe there's some best practice already for creating unique raffle numbers that I don't know.

Current implementation

  1. Instantiate a new raffle Ticket
  2. Add user data to Ticket
  3. Validate that

    • the Ticket has proper user data
    • that the user has no other Ticket in the round
    • the user didn't win a round in the last year
  4. Then, in one database transaction:

    • get the next serial value stored in a record in the Raffles table
    • increment it by one
    • generate the raffle number for our user, based on that serial value

By using a database transaction that locks the Raffles table, we make sure the serial values and hence the raffle numbers will be unique. However, since we query the Raffles table in general a lot, we think this lock might be a cause for some of our performance issues.

My implementation

  1. Run checks on the user:
    • make sure the user has proper data
    • the user didn't win a round in the last year
    • make sure the user doesn't have a ticket in this round yet
  2. Create a ticket for user
    • add user data to ticket
    • get the serial value from a Postgres Sequence, set up just for this purpose
    • generate the raffle number for our user, based on that serial value

By using a Postgres Sequence, we let the database take care that the serial values will be unique and we don't have to worry about race conditions.

Current code

 def Ticket#generate_serial_from_raffle
   if raffle.present? && serial.blank?
     ActiveRecord::Base.transaction do
       raffle = Raffle.lock(true).find(raffle_id)
       self.serial = raffle.max_serial + 1
       self.code   = TicketFunctions.serial_to_code(serial)
       raffle.update_columns max_serial: serial, max_code: code
     end
   end
 end

My code

def User#generate_raffle_code
    raffle = Raffle.current_raffle
    if raffle.nil?
      log_a_warning(a)
      return false
    elsif !has_complete_raffle_data?
      log_a_warning(b)
      return false
    elsif is_suspended_from_raffle_entry?
      log_a_warning(c)
      return false
    elsif has_current_raffle_code?
      log_a_warning(d)
      return false
    else
      tickets.create(raffle: raffle)
    end
  end
  def Ticket#set_serial_and_code
    self.serial = ApplicationRecord.connection.execute("SELECT nextval('ticket_serials_seq');")
      .first['nextval']
    self.code = TicketFunctions.serial_to_code(serial)
  end
\$\endgroup\$
  • \$\begingroup\$ It looks like you may still have a race condition in "My Code". To avoid them for sure, I believe you have to lock the entire table before doing the validations, and only release after you have rejected the user or created a new ticket. Ideally, you'd have this enforced at the DB level, but that may not be feasible with your current schema, so you'll have to setting for a full lock around all your application logic. \$\endgroup\$ – Jonah Feb 5 at 2:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.