RE: Concurrent Update Design

From: Mark W. Farnham <mwf_at_rsiz.com>
Date: Mon, 8 Sep 2014 11:56:13 -0400
Message-ID: <1f5a01cfcb7d$6b627980$42276c80$_at_rsiz.com>

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_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Charlotte Hammond (Redacted sender "charlottejanehammond_at_yahoo.com" 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

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

Received on Mon Sep 08 2014 - 17:56:13 CEST