Re: Concurrent Update Design

Thanks Seth.
My concern with this approach would be introducing an unnecessary point of serialization, as we don't need to wait for the lock on the next room: we are happy just to skip ahead to the next-but-one room if the immediate next one is locked (as it's equivalent). That's why I'm toying with SKIP LOCKED ROWS. (We probably wouldn't have this issue with real hotel rooms, but this is just an analog for our real application where concurrent requests for "rooms" can be very high) Thanks,
Charlotte

>Charlotte,
>
>This is a great question. Fortunately, T Kizzel addresses almost this exact
>scenario in Expert Oracle Database Architecture
><http://books.google.com/books?id=TmPoYfpeJAUC&lpg=PA1&pg=PA17#v=onepage&q&f=false>>
>.
>
>Seth Miller
>
>On Mon, Sep 8, 2014 at 10:58 AM, Adam Musch <ahmusch_at_xxxxxxxxx>> wrote:
>
>> What AQ does/did under the covers is SELECT FOR UPDATE ... SKIP LOCKED.
>>
>> So it's not as kludgy as it sounds.
>>
>> On Mon, Sep 8, 2014 at 10:27 AM, Charlotte Hammond <
>> dmarc-noreply_at_xxxxxxxxxxxxx>> wrote:
>>
>>>
>>> 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
>>>
>>
>>
>>
>> --
>> Adam Musch
>> ahmusch_at_xxxxxxxxx
>>

Thanks Seth.
My concern with this approach would be introducing an unnecessary point of serialization as we don't need to wait for the lock on the next room: we are happy just to skip ahead to the next-but-one room if the immediate next one is locked (as it's equivalent). That's why I'm toying with SKIP LOCKED ROWS. (We probably wouldn't have this issue with real hotel rooms, but this is just an analog for our real application where concurrent requests for "rooms" can be very high) Thanks,
Charlotte

>Charlotte,
>
>This is a great question. Fortunately, T Kizzel addresses almost this exact
>scenario in Expert Oracle Database Architecture
><http://books.google.com/books?id=TmPoYfpeJAUC&lpg=PA1&pg=PA17#v=onepage&q&f=false>>
>.
>
>Seth Miller
>
>On Mon, Sep 8, 2014 at 10:58 AM, Adam Musch <ahmusch_at_xxxxxxxxx>> wrote:
>
>> What AQ does/did under the covers is SELECT FOR UPDATE ... SKIP LOCKED.
>>
>> So it's not as kludgy as it sounds.
>>
>> On Mon, Sep 8, 2014 at 10:27 AM, Charlotte Hammond <
>> dmarc-noreply_at_xxxxxxxxxxxxx>> wrote:
>>
>>>
>>> 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
>>>
>>
>>
>>
>> --
>> Adam Musch
>> ahmusch_at_xxxxxxxxx
>>

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