Prevent duplicated database record creation with find_or_create

When we developed the gaming App for our client Colors, we encountered a common data consistency problem: How to prevent duplicated database record creation. Of course everyone instantly can think of "use #find_or_create_by", but it is actually little bit more complicated than that.

#find_or_create_by

We are using Ruby on Rails to implement the backend. There is a ActiveRecord method #find_or_create_by to do the find or create:

Product.find_or_create_by(key: key)

the method signature should be easy to understand what it is trying to do: either get an existing record with the provided key, or create a new record with the provided key.

Issue 1

First and foremost, if you are not aware, this method is NOT atomic (surprise!):

from https://apidock.com/rails/v4.2.7/ActiveRecord/Relation/find_or_create_by

Please note *this method is not atomic*, it runs first a SELECT, and if there are no results an INSERT is attempted. If there are other threads or processes there is a race condition between both calls and it could be the case that you end up with two similar records.

If you concurrently run the #find_or_create_by in two separate processes, it is possible that two identical records will be created.

Issue 1 resolution

So, a unique constraint is required at the database level to prevent . The following migration step can solve that:

add_index :products, :key, :string, unique: true

What would happen when there are two concurrent #find_or_create_by methods executing at the same time ? a ActiveRecord::RecordNotUnique exception is thrown. Hence, the correct way should be

begin
  Product.find_or_create_by(key: key)
rescue ActiveRecord::RecordNotUnique
  retry
end

Issue 2

There is another business requirement on DailyBonusLedger: maximum only one ledger per user per day should be created. Here is the simplified version of DailyBonusLedger schema:

user_id	integer
created_at	datetime
bonus_amount	integer

Obviously, a compound unique constraint is needed across User ID and Ledger Date.

However, we cannot use the [:user_id, :created_at] columns as the compound unique index. Since the ledger is unique within a day, not within timestamp millisecond. And there is no way to generate a unique constraint on the "day" part of the :created_at column.

How can we achieve that with unique constraint ?

Issue 2 resolution

What we can do instead is to add an extra column (e.g. :ledger_date) to store the day part of current date. And then create a unique constraint on [:user_id, :ledger_date]:

add_index :daily_bonus_ledgers, [:user_id, :ledger_date], unique: true

When we create a new record, we extract the day part of current time using #beginning_of_day:

begin
  DailyBonusLedger.find_or_create_by(user_id: user_id, ledger_date: Time.now.beginning_of_day)
rescue ActiveRecord::RecordNotUnique
  retry
end

With the new column and the compound unique constraint in place, only maximum one ledger is created per day per user.

Contact Us

If you have any comment or question about this post, feel free to email enquiry@f5.works.

Project Reference

Colors
Project Page

Google Play

Prevent duplicated database record creation with find_or_create_by