Concurrent Update Design

Hi All,

I'm looking for a bit of design advice on the following....

For the sake of argument, say I have a table of hotel rooms, ROOMS (ROOM_NUMBER NUMBER, BOOKED CHAR(1)). (To simplify matters, don't worry about different dates).

Multiple concurrent users are trying to book these rooms by updating BOOKED to 'Y'. When a session comes in we'd like them to be provided the next un-booked room number, excluding all rooms which are already booked even if that transaction has not yet committed (as we assume it will be soon).

What's the best way to go about this - we're thinking of using SELECT..FOR UPDATE..SKIP LOCKED. But that sounds kind of cludgy and we should be using AQ but I can't really see how this fits this scenario as a room isn't a message to be processed, it's a static piece of inventory in a table.

Any recommendations?

Thanks!
Charlotte

--
http://www.freelists.org/webpage/oracle-l