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.
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:
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.
First and foremost, if you are not aware, this method is NOT atomic (surprise!):
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
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:
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. :ledgerdate) to store the day part of current date. And then create a unique constraint on [:userid, :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.
If you have any comment or question about this post, feel free to email email@example.com.