how to get value from database when concurrent saving multiple data

class Defect < ApplicationRecord
  has_many :work_orders, dependent: :destroy
end

class WorkOrder < ApplicationRecord
  belongs_to :defect
  before_save :default_values

  def default_values
    self.running_number = self.defect.work_orders.maximum(:running_number).to_i + 1 if self.new_record?
  end
end

ideally the code works like this

Defect A 
- Work Order running_number 1
- Work Order running_number 2
- Work Order running_number 3

Defect B
- Work Order running_number 1
- Work Order running_number 2
- Work Order running_number 3

however when multiple users concurrently saving different WorkOrder object that belongs to the same defect, the running_number will go haywire because the maximum_running_number is based on only saved data. how do i make the running_number save properly?

Solution

The issue is that your concurrent saves get the same count of work orders, so you get duplicate running_numbers for the work order.

You can solve it two ways:

Setting a unique constraint on running_number and defect_id
Acquire a lock on the work order table until you've committed the new work order.

To set a unique constraint in a rails migration: add_index :work_orders, [:defect_id, :running_number], unique: true. Then just retry the save if there is an error when you call save.

assuming you're using Postgres

  begin 
    # .. create the work order
    work_order.save
  rescue PG::UniqueViolation
    retry
  end

Using retry will retry the block until no unique violation is raised. This could cause a deadlock if there was some other unique violation error on the record, so make sure that the error is caused by the running_number and nothing else.

The other way is to acquire a lock to prevent the race condition. As its a database table that is the shared resource, you acquire a table lock to ensure no other process is using the work order table while you are calculating the number of work orders and saving the record.

assuming your using Postgres explicit-locking docs

ActiveRecord::Base.transaction do
  # create order

  ActiveRecord::Base.connection.execute('LOCK work_orders IN ACCESS EXCLUSIVE MODE')
  
  work_order.save
end

Acquiring a table lock with this mode will prevent all access to the table from other connections to the database. It will be released when the transaction is committed, but again could cause deadlocks if for whatever reason the ruby process is killed before it has a chance to complete the transaction block.