Avoid race conditions with record locking

Published by Aaron Price on March 3, 2016

Say you're giving away tickets to a popular event. When you get down to the last ticket and 10 people are trying to get it at the same time, how do you ensure that the ticket goes to the first person who clicked the button?

The best way ensure data integrity is with the use of record locking at the database level.

Consider the following lines of code:


ActiveRecord::Base.transaction do
  ticket = Ticket.where("assigned_at IS NULL").first
  ticket.user_id = user.id
  ticket.assigned_at = Time.zone.now
  ticket.save
end

When thinking about this problem, you need to take into account that most web servers have the ability to process different requests in parallel. So it is possible for two people to be executing this code at the same time.

Let's take a look at the SQL of two such requests being processed at the same time:


SELECT * FROM tickets WHERE assigned_at IS NULL LIMIT 1;
-- This line returns the record with ID = 1 for user 1

SELECT * FROM tickets WHERE assigned_at IS NULL LIMIT 1;
-- This line also returns the record with ID = 1 for user 2

UPDATE tickets SET user_id = 1, sold_at = CURRENT_TIMESTAMP WHERE id = 1;
-- This line assignes the ticket to user 1 and marks it as sold.

UPDATE tickets SET user_id = 2, sold_at = CURRENT_TIMESTAMP WHERE id = 1;
-- This line overwrites the record in the database and assignes the ticket to user 2 with a different timestamp.

Ouch!

Record locking to the rescue

To avoid these cases, record locking, as the name suggests, locks a record for updating till the end of a transaction. This means that all operations on that record are halted and queued till the locking transaction is complete.

To enable record locking, first ensure that you are processing the code within a database transaction, then add FOR UPDATE to your select statement to lock the record.

The record is then automatically locked till the end of the transaction.

To see this in action, open two console windows, and execute the following SQL:


-- Terminal 1
BEGIN;

SELECT * FROM tickets WHERE id = 1 FOR UPDATE;

-- COMMIT;

-- Terminal 2
BEGIN;

UPDATE tickets SET sold_at = CURRENT_TIMESTAMP WHERE id = 1;

-- ROLLBACK;

Notice that terminal 2 does nothing, because it's waiting for the completion of terminal 1.

Record locking in Rails

To activate record locking in Rails, simply add lock(true) to your select query.


ActiveRecord::Base.transaction do
  ticket = Ticket.where("assigned_at IS NULL").lock(true).first
  ticket.user_id = user.id
  ticket.assigned_at = Time.zone.now
  ticket.save
end

That's it.

About me...

My name is Aaron Price. I'm a ruby developer based in Toronto and I'm passionate about simplicity.

Feel free to reach out using the following links: