I have a model Booking that needs to validate against several un-associated tables before creating.

Something like this:

class Booking < ApplicationRecord
  validate :must_be_available

  private

  def must_be_available
      return if available?

      errors.add :date, 'the booking period is unavailable'
  end

  def available?
    # Queries other tables for staff availability, location availability,
    # opening hours, etc… Also queries bookings table for existing bookings
    ...
  end
end

After the record is validated but before it is saved, new records could be created on bookings or the other tables which would have otherwise invalidated the new booking. Meaning, a booking might be created during what has become an unavailable period.

I have read about pessismistic locking but, as I understand it, I can only lock specific records, not entire tables.

How is this situation usually dealt with?

CodePudding user response：

The simple and canonical way to do that is to use the REPEATABLE READ transaction isolation level and run all your queries in the same transaction. This works, because then all your queries will use the same snapshot and see the same state of the database, even if concurrent sessions modify the data.

The price you are paying is that data modifying statements may receive a serialization error if a concurrent transaction changed any of the data you are trying to modify. In that case you have to repeat the transaction. Note that that cannot happen if you only read from the database.

This technique is (illogically) named optimistic locking.