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

From: Mark D Powell <Mark.Powell_at_eds.com>
Date: Wed, 11 Feb 2009 06:58:58 -0800 (PST)
Message-ID: <99770ae5-e37b-4d0b-856f-f20eaffb9850_at_c12g2000yqj.googlegroups.com>



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 -- Received on Wed Feb 11 2009 - 08:58:58 CST

Original text of this message