Add auto increment with scope to existing column in migration-file rails

Question

I have posts and organisations in my database. Posts belongs_to organisation and organisation has_many posts.

I have an existing post_id column in my post table which I by now increment manually when I create a new post. How can I add auto increment to that column scoped to the organisation_id?

Currently I use mysql as my database, but I plan to switch to PostgreSQL, so the solution should work for both if possible :)

Thanks a lot!

Answer 1

@richard-huxton has the correct answer and is thread safe.

Use a transaction block and use SELECT FOR UPDATE inside that transaction block. Here is my rails implementation. Use 'transaction' on a ruby class to start a transaction block. Use 'lock' on the row you want to lock, essentially blocking all other concurrent access to that row, which is what you want for ensuring unique sequence number.

class OrderFactory
  def self.create_with_seq(order_attributes)
    order_attributes.symbolize_keys!
    raise "merchant_id required" unless order_attributes.has_key?(:merchant_id)
    merchant_id = order_attributes[:merchant_id]

    SequentialNumber.transaction do
      seq = SequentialNumber.lock.where(merchant_id: merchant_id, type: 'SequentialNumberOrder').first
      seq.number += 1
      seq.save!
      order_attributes[:sb_order_seq] = seq.number
      Order.create(order_attributes)
    end
  end
end

We run sidekiq for background jobs, so I tested this method by creating 1000 background jobs to create orders using 8 workers with 8 threads each. Without the lock or the transaction block, duplicate sequence number occur as expected. With the lock and the transaction block, all sequence numbers appear to be unique.