RE: Concurrent Update Design
- From: "Mark W. Farnham" <mwf@xxxxxxxx>
- To: <dmarc-noreply@xxxxxxxxxxxxx>, "'Oracle-l Digest Users'" <oracle-l@xxxxxxxxxxxxx>
- Date: Mon, 8 Sep 2014 11:56:13 -0400
This *might* be a case for two status columns.
One is available for booking.
The other is booked.
This presumes that a big piece of functional contention is that once
acquired as a room to possibly book, the rest of the transaction takes quite
a while.
So in the two stage model, it would actually commit that room number as not
available for booking as soon as you grab it.
Then when you commit, it also becomes booked for the date range.
IF the reservation is not completed though, you mark the room available for
booking immediately after the rollback of the other bits of the transaction
(if any actually require rollbacks).
I don't know the sessions have an operator number (as when this is an
internal application) or if you are web facing. In the former case garbage
collection is pretty easy (freeing all the rooms marked not available by
some operator and not booked when they finish a session (or more quickly
explicitly release rooms unbooked for their operator number if they lost a
session).
That would tend to mean the value in the available for booking status would
be, oh, say, 0 for available and someone's operator number if a matter of
work in progress.
For lost internet sessions you probably have to do pretty quick garbage
collection across all rooms.
This is just one quick idea.
mwf
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of Charlotte Hammond (Redacted sender
"charlottejanehammond@xxxxxxxxx" for DMARC)
Sent: Monday, September 08, 2014 11:28 AM
To: Oracle-l Digest Users
Subject: 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
Other related posts:
