Re: How to do this query without blocking each other ?

From: <roelof.streekstra_at_gmail.com>
Date: Wed, 11 Feb 2009 14:14:06 -0800 (PST)
Message-ID: <e21d6b2a-ad7b-4d6f-8a30-6e345465a3a0_at_j39g2000yqn.googlegroups.com>

On Feb 11, 9:58 am, Mark D Powell <Mark.Pow..._at_eds.com> wrote:
> On Feb 11, 9:23 am, krisl..._at_gmail.com wrote:
>
>
>
> > Hi Gurus,
>
> > I have a table like below to implement Seat stock taking :
> > (the table below is simplified form, just to explain the requirement )
>
> > CREATE TABLE SEAT_STOCK
> > (
> > ROOM_ID NUMBER(12) PRIMARY KEY,
> > ROOM_NO VARCHAR2(5 CHAR) NOT NULL, -- room number
> > SEAT_NO VARCHAR2(1 CHAR) NOT NULL, -- seat number : A,B.C,D etc
> > ROOM_TYPE VARCHAR2(1 CHAR), -- Room Status [N]Neutral, [M]ale, [F]
> > emale
> > SEAT_STATUS VARCHAR2(1 CHAR), -- Unoccupied, [O]ccupied
> > PSG_STATUS VARCHAR2(1 CHAR), -- Passenger Status : [M]ale, [F]emale
> > PSG_NAME VARCHAR2(30 CHAR) -- Passenger Name
> > );
>
> > begin
> > INSERT INTO SEAT_STOCK(ROOM_ID, ROOM_NO, SEAT_NO, ROOM_TYPE,
> > SEAT_STATUS) VALUES (1, '101', 'A', 'N', 'U');
> > INSERT INTO SEAT_STOCK(ROOM_ID, ROOM_NO, SEAT_NO, ROOM_TYPE,
> > SEAT_STATUS) VALUES (2, '101', 'B', 'N', 'U');
> > INSERT INTO SEAT_STOCK(ROOM_ID, ROOM_NO, SEAT_NO, ROOM_TYPE,
> > SEAT_STATUS) VALUES (3, '101', 'C', 'N', 'U');
> > INSERT INTO SEAT_STOCK(ROOM_ID, ROOM_NO, SEAT_NO, ROOM_TYPE,
> > SEAT_STATUS) VALUES (4, '101', 'D', 'N', 'U');
> > INSERT INTO SEAT_STOCK(ROOM_ID, ROOM_NO, SEAT_NO, ROOM_TYPE,
> > SEAT_STATUS) VALUES (5, '101', 'E', 'N', 'U');
> > --
> > INSERT INTO SEAT_STOCK(ROOM_ID, ROOM_NO, SEAT_NO, ROOM_TYPE,
> > SEAT_STATUS) VALUES (6, '201', 'A', 'N', 'U');
> > INSERT INTO SEAT_STOCK(ROOM_ID, ROOM_NO, SEAT_NO, ROOM_TYPE,
> > SEAT_STATUS) VALUES (7, '201', 'B', 'N', 'U');
> > INSERT INTO SEAT_STOCK(ROOM_ID, ROOM_NO, SEAT_NO, ROOM_TYPE,
> > SEAT_STATUS) VALUES (8, '201', 'C', 'N', 'U');
> > INSERT INTO SEAT_STOCK(ROOM_ID, ROOM_NO, SEAT_NO, ROOM_TYPE,
> > SEAT_STATUS) VALUES (9, '201', 'D', 'N', 'U');
> > INSERT INTO SEAT_STOCK(ROOM_ID, ROOM_NO, SEAT_NO, ROOM_TYPE,
> > SEAT_STATUS) VALUES (10,'201', 'E', 'N', 'U');
> > end;
>
> > SELECT * FROM SEAT_STOCK
> > WHERE SEAT_STATUS = 'U'
> > AND ROWNUM <= 3
>
> > The objective of the query is :
> > On above example 10 rows available to be taken, if there is 3 users
> > each request 3 rows "almost concurrently" :
> > user_1 get row 1 - 3
> > user_2 get row 4 - 6
> > user_3 get row 7 - 9
> > without blocking / waiting each other to commit .
> > [I dont use SELECT FOR UPDATE to avoid blocking each other, but I read
> > also that SKIP LOCKED is not supported / recommended]
>
> > How can I achieve the objective of the query ?
>
> > Thank you very much,
> > xtanto
>
> xtanto, Oracle does not read lock so mutiple users can read the data
> without interferring with each other.  The data would only be locked
> in the event of update and the locks would only block if multiple
> users attempt to update the same row(s).
>
> I think you need to explain your processing requirements in more
> detail unless the above actually provides the information you need.
>
> HTH -- Mark D Powell --

Tom kite describes this problem very well in his book "Expert Oracle Database Architecture" pages 186 to 200.

Roelof Received on Wed Feb 11 2009 - 16:14:06 CST