Here's a possible solution that would maintain concurrency. It would
involve breaking down your booking periods into discrete units. Create a
table w/ unique constraint based on the discrete time block. So instead
of inserting a single start/end record, you would insert a record for
each unit inside timeframe.
Example, let's say the minimum booking period is 1 hour and somebody
wants the timeframe between 8/25/05 10PM to 8/26/05 9PM. So you would
have a loop that attempts to insert 8/25/05 10PM, 8/25/05 11PM, ...,
8/26/05 7PM, 8/26/05 8PM. With an unique constraint on DATE+TIME_BLOCK,
an unavailable period would return a duplicate key error -- which if you
detect, you'd break out of your loop. Put this loop inside a transaction
and the entire job will just rollback so the booked time units up to
that point would auto-unbook themselves.
You are getting concurrency in exchange for doing more work so the point
where the extra records decreases performance more than locking depends
on how many period units within the average start/end timeframe.
neil.saunders@accenture.com wrote:
> Hi,
>
> Following on from my last question, I'm attempting to write a function that inserts periods. Periods are either
bookings,available, or unavailable, each stored in a seperate table, and all have start date and end date columns, all
ofwhich inherit a table 'calendar_entries'.
>
> The function needs to check that there are no overlapping periods, so I need to check all three tables - I need to
prevententries being added in parrell for the duration of the function. My question is - Is it sufficient to LOCK
calendar_entriesIN EXCLUSIVE MODE, or do I need to lock all three tables individually?
>
> Also, inside the function I need to use a transaction in order to DELETE one row, and UPDATE another. Will this
'inner'transaction have write access to all 3 tables?
>
> Just trying to get my head around concurrency - Have a good weekend!