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

<krislioe_at_gmail.com> wrote in message news: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

The select won't block anyone. I believe the way airlines do it etc. is to actually reserve the seat and have some sort of time to live column. If the order isn't completed within x amount of time then the seat is unblocked. Of course, if the user removes it from their shopping cart then the reservation is removed.
Jim Received on Wed Feb 11 2009 - 08:57:07 CST