0

Consider this simple example: Tables (in a restaurant) can be booked for a period. Business constraint: No two table bookings for the same table may overlap in time.

How might one prevent business constraint violations using Read Committed isolation level for the business constraint validation and optimistic locking?

  • recommended reading: Open letter to students with homework problems "If your question... is just a copy paste of homework problem, expect it to be downvoted, closed, and deleted - potentially in quite short order." – gnat Apr 25 '18 at 13:50
  • This is not a homework problem and I'm not a student. – Jesper Apr 25 '18 at 13:53
  • 1
    Your question is quite unclear. When you write "tables", do you mean something like tables in a restaurant, or tables in a database? Please edit your question to make this more clear (and you may consider to give a little bit more context, so your question does not look like a homework problem). – Doc Brown Apr 25 '18 at 14:26
  • 1
    I would start by googling "optimistic concurrency control" and asking yourself how it differs from pessimistic concurrency. Is there even such a think as an optimistic "lock"? Or is it managed more with something like row versioning? – John Wu Apr 25 '18 at 19:27
  • 1
    Why do you care about isolation levels and optimistic locking here? Your problem would be trivially solved by using the default of almost any RDBMS. Unless you need sub-second booking or your booking 1000's of tables simultaneously. Seems unlikely. – Alex Apr 26 '18 at 13:27
0

It appears you are looking for a uniqueness constraint on a time range. In some DBMSs such as PostgreSQL you can declaratively declare constraints on ranges. In other DBMSs a declarative constraint is not possible and an imperative approach, such as using triggers is required.

In either case a correct implementation that considers multi-user concurrency will include locks to prevent double bookings. In this case, an exclusive lock on the "dinner table" record to which the booking applies could be taken before the check is made regarding overlaps to ensure only one booking is processed at anyone time.

However, in the general case there may not be a suitable existing record to lock to control concurrency. Also, locking parent records to control access to child records can be considered over-zealous as it needlessly serializes access to some resources when it is not required. Explicitly named locks may be used in these case, for instance a lock named "TABLE_BOOKING_[TABLE_ID]" using functionality such as advisory locks in PostgreSQL or DBMS_LOCK in Oracle.

For optimistic concurrency control there could be a booking version number on the "dinner table" record which gets incremented each time an associated booking is inserted or updated. You could use this when checking for overlaps in bookings to ensure no changes had been made between retrieving the existing booking data to check for overlaps and actually making the booking. A booking would not be allowed if any changes had been made between the retrieval and save and an error raised.

| improve this answer | |
  • Using locks is IMHO not necessary. This looks to be the opposite of "optimistic locking" and I guess it is not really a solution to the problem. Using contraints and triggers is, however, IMHO a good idea (but also not necessary if there is only one application for the bookings which cuts the transactions correctly). – Doc Brown Apr 26 '18 at 14:57
  • If the is a cast-iron guarantee that there will be no concurrent attempt to book the same "dinner table" at the same time then locking is not necessary. In any other case some concurrency control, such as locking, is required. Either implicitly through a declared unique constraint (internally DBMSs use a locking mechanism to ensure concurrent uniqueness); or explicitly if the uniqueness is programmed imperatively. A declarative or imperative constraint also places far fewer limitations on the transaction management when amending bookings. – user298084 Apr 26 '18 at 15:41
  • I am saying you don't need to use any explicit locks, like imposed by "SELECT FOR UPDATE". Of course, one needs a transactional database for this, and cut the transactions correctly (as described in my answer). – Doc Brown Apr 26 '18 at 15:50
0

Lets say you have a Table table (master) and a Bookings table (detail). Bookings have some attributes like

  • BookingID
  • TableID
  • StartDateTime
  • EndDateTime
  • Name of booker etc.

Lets further assume bookings can be grouped "by day" (the restaurant will probably have some regular closing hours, and bookings will be not allowed when they overlap those closing hours).

Then organize your transactions in a way so all changes to the set of booking records for a specific table and a specific day are done in one transaction, which does

  • delete all existing bookings for that table and day

  • write the booking records which represent the new schedule for the table and the day

If you get a collision, you need some strategy for handling it, like "first comes first" or something like that, but you should never run into a situation where there are any overlapping bookings in the database.

A variation of that strategy might work even when the restaurant has no closing hours. Loading always all bookings of a table and modifying them at once, in one transaction, could in theory solve the problem, but this becomes quickly impractical if you have always to write all bookings for the last 5 years for a table, for each slight change to the schedule. But by restricting changes only to future bookings, and forbidding any change to older bookings, this becomes a strategy of reading, modfiying and committing the whole future schedule at once. That should IMHO work, even when the bookings set of a table cannot be easily cut into independent subsets by day.

| improve this answer | |

Not the answer you're looking for? Browse other questions tagged or ask your own question.