Handling Multi User Stock Taking

From: <krislioe_at_gmail.com>
Date: Tue, 10 Feb 2009 03:08:15 -0800 (PST)
Message-ID: <fd68cf80-4210-4cc4-b995-de10e9fc80fa_at_z27g2000prd.googlegroups.com>



Hi sql gurus,

We have a system to handle seat inventory stock taking in a marine cruise. All seats in all rooms are maintained in a table. below is the simplified structure of the table :
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 )

I want to create a procedure that will do stock taking the available seat based on the passed parameter:
v_psg_status (M/F)
v_num_seat (Number of seat requested)

The rule is :
- at the beginning, all Seat in the room will be 'Unoccupied' and have Room_Type : 'Neutral'
First Male passenger that occupy the room will change ALL Seat in the room to have Room_Type = 'Male', and the SEAT_Status = 'Occupied' First Female passenger that occupy the room will change ALL Seat in the room to have Room_Type = 'Female', and the SEAT_Status = 'Occupied' (because male & female cannot share same room)

  • On stock taking, the priority rule applies : For Male pasenger, first the query should look for the 'Unoccupied' Seat in Room_Type 'Male' for the number of seat requested, Only if its not available or not enough then the remaining should go to 'Unoccupied' seat in Neutral room (consequently change the room type to 'Male'). Same rule applies for female passenger.
  • How to handle multi user locking/ concurrency requirement ? because many users may see SAME unoccupied Seat and take it concurently .

I try to avoid cursor and use 2 or 3 DMLs to do the task if its more efficient. Could you please give suggestions ?

Thank you very much,
xtanto

PS : the sample data.
begin
INSERT INTO SEAT_STOCK(ROOM_ID, ROOM_NO, SEAT_NO, ROOM_TYPE) VALUES
(1, '101', 'A', 'N');

INSERT INTO SEAT_STOCK(ROOM_ID, ROOM_NO, SEAT_NO, ROOM_TYPE) VALUES
(2, '101', 'B', 'N');

INSERT INTO SEAT_STOCK(ROOM_ID, ROOM_NO, SEAT_NO, ROOM_TYPE) VALUES
(3, '101', 'C', 'N');

INSERT INTO SEAT_STOCK(ROOM_ID, ROOM_NO, SEAT_NO, ROOM_TYPE) VALUES
(4, '101', 'D', 'N');

INSERT INTO SEAT_STOCK(ROOM_ID, ROOM_NO, SEAT_NO, ROOM_TYPE) VALUES
(5, '101', 'E', 'N');

--
INSERT INTO SEAT_STOCK(ROOM_ID, ROOM_NO, SEAT_NO, ROOM_TYPE) VALUES

(6, '201', 'A', 'N');
INSERT INTO SEAT_STOCK(ROOM_ID, ROOM_NO, SEAT_NO, ROOM_TYPE) VALUES
(7, '201', 'B', 'N');
INSERT INTO SEAT_STOCK(ROOM_ID, ROOM_NO, SEAT_NO, ROOM_TYPE) VALUES
(8, '201', 'C', 'N');
INSERT INTO SEAT_STOCK(ROOM_ID, ROOM_NO, SEAT_NO, ROOM_TYPE) VALUES
(9, '201', 'D', 'N');
INSERT INTO SEAT_STOCK(ROOM_ID, ROOM_NO, SEAT_NO, ROOM_TYPE) VALUES
(10,'201', 'E', 'N');
end;
Received on Tue Feb 10 2009 - 05:08:15 CST

Original text of this message