johnsparrowuk wrote:

> The only workaround I can think of (that doesn't use locking):
>
> quantify the timestamps as an integer or something (ie representing
> each 10-minute time period as in cast(ts * 144.000 as integer) ).
> Then store these in a table like:
>
> create table X (idcode integer not null, timecode integer not null,
> primary key(idcode,timecode));
>
> So a 1 hour period would insert 6 records into this table. A 1 day
> period 144 records.
>
> Post a record in the original table that overlaps an existing one,
> and the trigger will cause duplicate keys to be entered into table X,
> causing an exception!

You are on the right way, but thinking of a resolution in seconds is not
possible since the number of records needed to be generated is enormous.

The solution is to track a Freelist in a Table wich contains available
time-periods. You can start with one record representing a period from
01.01.1900 0:00 to 31.12.2199 23:00 (That should be big enough). Put a primary
key on the field with the beginning timestamp.
Write a stored procedure wich takes a period to book as input parameter and
wich selects a period from the free-table where the period is fully enclosed.
Update that record setting the end-time to the start-time of the period to
book and insert a new free-record to that table setting the start-time to the
end-time of the period to book and the end-time to the old endtime of the
original free-peiod.

if you want to book a period where ends would overlap with other periods you
wo'nt find a fitting free-block...
if you split a free-block and successfully commit you can be sure nobody else
could have updated that free-block.

If you have to keep track of deleting booked periods you may hav a second
table containig the booked periods with a after-delete-trigger
reconcatenating the free-list-entrys in the neighbourhoud..

This works fast in mga-architecture.

Enjoy
Martin Suer