At 11:29 PM 14/06/2004 +0200, you wrote:

>Ann, Helen, can someone help? :-)

Well, I've kept out of it on the basis that inter-row dependencies are IMO
a sign of poor design. The argument that somehow dirty read would save the
day is entirely specious. It would buy the ability to do this unsafe thing
at the cost of atomicity. You can't have atomic transactions with dirty
read, period.

Lester's solution comes closest to what I'd recommend - and Lester should
know. Lester's stuff keeps the British trains running.

Allocating from an inventory of time spaces isn't so different from
allocating any other sort of commodity. Would you have an inventory system
without a stock quantities table? Predefine all the "spaces", carry a
status flag indicating whether a space is "in use" and disallow end-users
from inserting records at all. A user may select an available "space" (as
seen from her own transaction) and try to set its flag. If another user
already has a pending update on the flag, the update either fails (NO WAIT)
or waits to see whether the other user's update gets rolled back (WAIT).
That way, you can constrain usage properly - and atomically - in terms of
transaction isolation. It's bomb-proof.

btw, Martijn, I behold with abject horror any CHECK constraint that checks
entries against *anything* other than the data in the current row of the
current table. "Just because you can, doesn't mean you should", like
sky-diving without a parachute.

Errm, well, you did ask. :-))

/heLen