RE: Concurrent Update Design
Date: Mon, 8 Sep 2014 09:44:26 -0700
Message-ID: <013501cfcb84$273d8ae0$75b8a0a0$_at_comcast.net>
We used the SKIP LOCKED methodology on the equivalent of a queue processing with 100's of worker processes and processing over a billion transactions per day (80% peak for 14 hours per day or about 15.7K tps).
It works fine.
There is a point that a single queue becomes a problem in extreme scaling and you may need to partition the data, at the queue layer, at the table layer or with multiple tables to decrease the scan time across the locked rows.
Matthew Parker
Chief Technologist
425-891-7934 (cell)
<mailto:Dimensional.dba_at_comcast.net> Dimensional.dba_at_comcast.net
<http://www.linkedin.com/pub/matthew-parker/6/51b/944/> View Matthew Parker's profile on LinkedIn
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 9:31 AM
To: Oracle-l Digest Users
Subject: 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
<http://books.google.com/books?id=TmPoYfpeJAUC&lpg=PA1&pg=PA17#v=onepage&q&f
=false> &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
<http://books.google.com/books?id=TmPoYfpeJAUC&lpg=PA1&pg=PA17#v=onepage&q&f
=false> &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-lReceived on Mon Sep 08 2014 - 18:44:26 CEST