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?
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.
Lets say you have a Table table (master) and a Bookings table (detail). Bookings have some attributes like
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.
