0

I am using a stored procedure to select a "Random" row from Available_Tickets table, after selection if the user like the number, and buy the ticket, the row will be deleted from the table.

my procedure looks like this:

CREATE OR REPLACE GET_RANDOM_TICKET (RESULT OUT INTEGER) IS
co,mn,mx integer;
BEGIN
SELECT COUNT(ID) , MIN(ID) ,MAX(ID) INTO CO,MN,MX FROM TICKETS;

SELECT TICKET_NUMBER INTO RESULT FROM (
   SELECT TICKET_NUMBER 
      FROM TICKETS WHERE ID >= DBMS_RANDOM(MN,MX)
) WHERE ROWNUM = 1;
END GET_RANDOM_TICKET;

if the user agrees on the returned number the selected row is deleted. can I get in a worst case scenario where the row with max(id) is delete after executing the first select statement ?

edit 1---- Would the two SELECT statements see the same data in-spite off the changes in the table? and why?

alibttb
  • 526
  • 4
  • 19

1 Answers1

0

i would redesign it as below 1) add a column (ticket status [free,reserved,sold]) to mark the returned ticket as reserved until the user confirmed his selection 2) use a cursor with for update clause to update that ticket status column after returning it to the user - also add where condition for free tickets 3) check ticket status again after user confirmation if it still reserved then update to sold. (in the very slim chance that 2 users ran the program in the same time and got the same number one of them will get the number and the other one should receive error message since the ticket is not reserved anymore.

hope that help

Ibrahim M. Nabil
  • 16
  • 1
  • Could you please add some code to the answer, I'd like you to notice that the deletion is done outside the procedure, so if I change that into an update, and select inside the procedure `for update` will that keep the lock? – alibttb May 22 '17 at 18:47
  • sorry been away for a while. you don't need to delete. you can keep them with the status sold for future references. as i mentioned before you can base your where condition on the ticket status – Ibrahim M. Nabil Jun 13 '17 at 09:36