How to create records concurrently in Postgres with ActiveRecord and Sidekiq

Question

I have a Sidekiq worker, which makes an API call, parses the returned json and creates ActiveRecord objects (products). Since the products belong to a brand and the product json also contains the data for the brand, the worker does the following before actually saving the product to the database:

1. check if the brand exists in the database by checking its unique api_id (the id, with which the brand comes from the api, in the db, there is a unique index on this column);
2. if it does - fetch its primary id;
3. if is doesn't - create it and get its primary id

I have implemented this like so:

def brand_id(brand_json)
  Brand.where(api_id: brand_json[:api_id]).pluck(:id).first.presence ||
  Brand.create!(name: brand_json[:name], api_id: brand_json[:api_id]).id
end

After that the worker creates the product with the brand_id set to the fetched id.

Now I am thinking of the following scenario:

1. two workers simultaneously fetch data for two products that belong to the same brand that doesn't yes exist in the database;
2. worker 1 one checks for the brand and doesn't find it;
3. shortly after that worker 2 checks for the brand and doesn't find it;
4. worker 1 creates the brand;

Now what happens with worker 2? My assumption - it tries to create the brand, but an error at the database level occurs, as there is already a record with the same api_id? (probably ActiveRecord::RecordNotUnique error is raised?)

Also, how do I handle this case and this type of errors in the context of Sidekiq and ActiveRecord? Should I somehow implement a table-wide lock on the brands table to prevent such things? If yes - than I will not be able to concurrently create products, as at any given time only one worker will have access to the brands table, which is required for creating a product.

Or maybe I should wrap my brand_id(brand_json) method in transaction like so:

def brand_id(brand_json)
  ActiveRecord::Base.transaction do
    Brand.where(api_id: brand_json[:api_id]).pluck(:id).first.presence ||
    Brand.create!(name: brand_json[:name], api_id: brand_json[:api_id]).id
  end
end

Please, advise.

Answer 1

1. Put the unique index constraints (possibly in the form of a multi column index) in the DB.
2. Just try to create the object. The database will prevent you from making more than one.
3. Only the thread that succeeded in creating the initial object (no exception occurred) is allowed to proceed with extra processing.