How to do this query without blocking each other ?

From: <krislioe_at_gmail.com>
Date: Wed, 11 Feb 2009 06:23:22 -0800 (PST)
Message-ID: <3d21a129-83c2-4c69-a6a4-e3d093d8b37b_at_r15g2000prd.googlegroups.com>

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

Received on Wed Feb 11 2009 - 08:23:22 CST