Web application calling stored procedure can cause race conditions (asynchronous processing)

I am looking to synchroize an asynchronous process.
I have a bunch of user on the Web page. They are creating items.  They click on the save button to create a new item. Behind the scene,  the web application calls a stored procedure, CreateItem(variouse parameters...) and returns an item number. Because it is the web, this is asynchronous.  The problem occurs, if 2 or more user creates different items (of course different items) at the same time (ie click on the Save button almost simultaneously), then there is a race condition.  Only one item number is valid in the system.  One item number cannot belong to 2 products.

Stored Procedure:
1. query newitemnumber table. It is new if status_id =0
2. claim the item # by setting the status_id to another value. This is product dependent.
Begin Work;
3. Insert into item table. (error could occur if item # already existed)
4. update other tables related to item setup. (ie description, size, price, etc...)
Commit work;
5. If any error occurs for any reason,  go to exception handling. In exception handling, return the item# back to item# pool. This is done by setting status_id in newitemnumber table to 0. Roll Back work;

Race Condition (rare, but does happen)
Person A create item for greeting card, Person B create item for can of Soup.
Both click Save  almost simultaneously (no they are in different groups, they are not coordinating with each other).
Process A: Gets New item# 123
Process B: Gets new item# 123
Process A: Claim the item by setting status_id to 5
Process B: Claim the item # by setting status_id to 6
Process A:  Inserts into item table, with new item # 123. This item is for greeting card.
Process B: Tries to insert into item table with item# 123, for Can of Soup.
Process B: Gets an exception. Unique Key constraint.
Process B: returns item # 123 back to item# pool (newitemnumber table) by setting status to a 0
Process B: returns an error back to the UI
Process A: returns item # 123 back to Person A. success

Process C (or anyone afterward): Gets item # 123 (because status_id =0.
Process C: exception generated because tries to insert item# 123 into item table
On call person gets a call because no one can create a new item.

** Item number are unique to the system.
Item numbers are not sequence numbers
We do reuse our item #. (ie old product discontinued, gets put back into item# pool)
LVL 1
THBAAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

mastooCommented:
You don't say the database but a rudimentary improvement would be on your "Claim the item" step.  On the update, add a where status_id = 0, then check the rows affected count.  If somebody else claims it you'll get rows affected = 0, meaning you didn't claim the item at which point you could retry it.
0
THBAAuthor Commented:
I'm using informix.  I am already using where status_id =0 to get the item number. . How do I check the rows affected count?
 
0
mastooCommented:
Sorry, I'm not familiar with Informix.  I'd assume it has that feature as the other databases I've worked with all have it.
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

gheistCommented:
You can use transactions and select for update... Otherwise your algorithm is flawed.
0
gheistCommented:
Can you post actual SQL?
0
THBAAuthor Commented:
Hi everyone,
I had to take some time to create a test program to simulate multiple users calling the stored procedure. (like what can happen on a busy Monday when user used the web).
gheist,
Yes, I do use transaction. In Informix it is Begin Work/ Commit Work (also Rollback) . I have enclosed the code.  

CREATE FUNCTION sc_InsertNewItem(arg_WegmansBrandIND SMALLINT 
                               , arg_UserID LIKE Item.ChangedBy
                               , arg_AuthItemNumber LIKE Item.ItemNumber
                               , arg_CloneItemNumber LIKE Item.ItemNumber
                               , arg_StatusID LIKE Item.StatusId
                               , arg_DepartmentId LIKE Item.DepartmentId 
                               , arg_CategoryId LIKE Item.CategoryId 
                               , arg_ClassId LIKE Item.ClassId 
                               , arg_Description LIKE Item.Description
                ) RETURNING INTEGER;
  
  DEFINE wNewItemNumber  INT;
  DEFINE wCreateDate     DATE;
  DEFINE wChangedBy      NewItemNumber.ChangedBy;
  DEFINE wStatusID       NewItemNumber.StatusID;
  DEFINE wCloneItemCount INT;
  DEFINE wAuthItemNumber INT;
  DEFINE wDepartmentId   INT;
  DEFINE wCategoryId     INT;
  DEFINE wClassId        INT;
  DEFINE wSequenceNum    DEC;
  DEFINE	sqlErr				INT;
  DEFINE	isamErr				INT;
  DEFINE	errorInfo			VARCHAR(70);
  
  ON EXCEPTION SET sqlErr, isamErr, errorInfo
    ROLLBACK WORK;
    IF wNewItemNumber >0 THEN    
		IF sqlErr = -268 THEN --unique constraint error. 2 people simultaneously tries to get item#
		-- do not set statusid=0, someone else already has the number.
          return sqlErr;
		ELSE
		UPDATE NewItemNumber
		SET  StatusID = 0
	  WHERE ItemNumber = wNewItemNumber;
		END IF
    END IF
    
    RAISE EXCEPTION sqlErr, isamErr, errorInfo;
  END EXCEPTION;
  
  BEGIN WORK; 
  
  LET wNewItemNumber = -9999;
  LET wAuthItemNumber = arg_AuthItemNumber;
  LET wDepartmentId = arg_DepartmentId;
  LET wCategoryId = arg_CategoryId;
  LET wClassId = arg_ClassId;
  LET wSequenceNum = 0;
  
  -- Get available item number based on the Wegmans brand indicator
  IF arg_WegmansBrandIND = 0 THEN
    FOREACH
    SELECT FIRST 1 ItemNumber, AvailableDate 
      into wNewItemNumber, wCreateDate 
    FROM NewItemNumber
    WHERE StatusID = 0 
      AND ItemNumber > 99999
      AND VendorId = 0
    ORDER BY AvailableDate
    END FOREACH
  ELSE
    FOREACH
    SELECT FIRST 1 ItemNumber ,AvailableDate 
      into wNewItemNumber, wCreateDate 
    FROM NewItemNumber
    WHERE StatusID = 0 
      AND ItemNumber <= 99999
      AND VendorId = 0
    ORDER BY AvailableDate
    END FOREACH
  END IF
  
   IF wNewItemNumber <= 0 THEN
    RAISE EXCEPTION -746, 0, 'No New Item Numbers Available!!';
  END IF
  
  
  IF arg_CloneItemNumber = -9999 THEN
    LET arg_CloneItemNumber = 0;
  END IF
  
  SELECT  ChangedBy, StatusID
      into  wChangedBy,wStatusID 
    FROM NewItemNumber
    WHERE  ItemNumber = wNewItemNumber 
    
    
  
      -- Set the Status for the new item number to make it unavailable
  -- This needs to happen ASAP in this routine to limit exposure to new item number conflicts
  UPDATE NewItemNumber
    SET (StatusID, ChangedBy) = (arg_StatusID, arg_UserID)
  WHERE ItemNumber = wNewItemNumber;
  
  COMMIT WORK;
  
  BEGIN WORK; 
  -- Validate clone item number (only if there was one passed in)
  IF arg_CloneItemNumber <> 0 THEN
    SELECT Count(ItemNumber)
      into wCloneItemCount
    FROM Item
    WHERE ItemNumber = arg_CloneItemNumber;
    
	IF wCloneItemCount = 0 THEN
	  RAISE EXCEPTION -747, 0, 'Clone Item does not exist!!';
	END IF
  END IF
    
 
  
  -- Set some other data if there was a clone item provided
  IF arg_CloneItemNumber <> 0 THEN
    -- Set auth item to be the clone one if it wasn't provided
    IF wAuthItemNumber = -9999 THEN
      LET wAuthItemNumber = arg_CloneItemNumber;
    END IF
    
   -- Get dept/cat/class from the clone item if they were partially provided or not provided at all
    IF wDepartmentId = -9999 OR wCategoryId = -9999 OR wClassId = -9999 THEN
      FOREACH
      SELECT FIRST 1 DepartmentId, CategoryId ,ClassId
        into wDepartmentId, wCategoryId, wClassId
      FROM Item
      WHERE ItemNumber = arg_CloneItemNumber
      END FOREACH
    END IF
  END IF
  
  -- Get the sequence number
  SELECT Max(SequenceNumber) + 1
    into wSequenceNum
  FROM Item
  WHERE ClassId = wClassId;
  
  IF wSequenceNum IS NULL THEN 
    LET wSequenceNum = 0;
  END IF
  
  -- Build all the related item records...SNIC should always use itemnumber 0
  IF arg_StatusId < 5 AND arg_CloneItemNumber <> 0 THEN
	CALL sc_CopyItem(arg_CloneItemNumber, wNewItemNumber, arg_UserID, arg_statusID);
  ELSE
	CALL sc_CopyItem(0, wNewItemNumber, arg_UserID, arg_statusID);
  END IF
  
  -- First, update the Status in case any stored procs need to look at it
  UPDATE Item
    SET (StatusId, Description, SequenceNumber) = (arg_StatusId, arg_Description, wSequenceNum)
  WHERE ItemNumber = wNewItemNumber;
  
  -- If no clone provided but we have at least a departmentid, then update the product group
  IF arg_CloneItemNumber = 0 AND arg_DepartmentID > 0 THEN
    UPDATE Item
       SET (DepartmentID, CategoryID, ClassID) = 
           (arg_DepartmentID, DECODE(arg_CategoryID,-9999,NULL,arg_CategoryID), DECODE(arg_ClassID,-9999,NULL,arg_ClassID))
     WHERE ItemNumber = wNewItemNumber;
  END IF
 
  -- Run these for SNIC 
  IF arg_StatusId = 5 THEN  
    -- Now copy over all the Clone info
    CALL sc_CopyCloneProperties(wNewItemNumber, arg_CloneItemNumber, arg_UserID);
    CALL sc_CloneStoreAuthRecords(wAuthItemNumber, wNewItemNumber, 'Clone');
  END IF
 
  -- Update short description
  UPDATE ItemDescription
    SET (ShortDescription, POSDescription) = (arg_Description, '')
  WHERE ItemNumber = wNewItemNumber;
  
    -- Set pos description to blank
  UPDATE ItemPOS
    SET (POSDescription) = ('')
  WHERE ItemNumber = wNewItemNumber;
  
  -- Now move the item into the correct product group
  IF exists (select id from pgclass where id = wClassId) THEN
    CALL sc_MoveItem(arg_UserID, wNewItemNumber, wDepartmentId, wCategoryId, wClassId);
  END IF
  
  -- Update the table of Contents for the New item
  CALL sc_InsertNewItemStatusRecords(wNewItemNumber);
   
  COMMIT WORK;
  
  RETURN wNewItemNumber;
  
END FUNCTION;

Open in new window

0
gheistCommented:
I do not see falthrough in your code.
You have to insert row with new item number in first transaction to do correct locking. Then use other transactions to fix the record.
0
THBAAuthor Commented:
I do not understand. What do you mean by falthrough?  
I am updating in the first transaction.  I am grabbing available item # (because we recycle our item#).  I cannot insert into newitemnumber table.
 
 
0
gheistCommented:
Next id is generated in unsafe manner. It should be generated inside first transaction alongside lock acquisition.
Then oher session will respect lock and generate another ID after current one.
0
THBAAuthor Commented:
Hi,
OK. I made modifications. Is this what you mean?

CREATE FUNCTION sc_InsertNewItem(arg_WegmansBrandIND SMALLINT
                               , arg_UserID LIKE Item.ChangedBy
                               , arg_AuthItemNumber LIKE Item.ItemNumber
                               , arg_CloneItemNumber LIKE Item.ItemNumber
                               , arg_StatusID LIKE Item.StatusId
                               , arg_DepartmentId LIKE Item.DepartmentId
                               , arg_CategoryId LIKE Item.CategoryId
                               , arg_ClassId LIKE Item.ClassId
                               , arg_Description LIKE Item.Description
                ) RETURNING INTEGER;
 
  DEFINE wNewItemNumber  INT;
  DEFINE wCreateDate     DATE;
  DEFINE wChangedBy      VARCHAR(30);
  DEFINE wStatusID       LIKE newitemnumber.ChangedBy;
  DEFINE wCloneItemCount INT;
  DEFINE wAuthItemNumber INT;
  DEFINE wDepartmentId   INT;
  DEFINE wCategoryId     INT;
  DEFINE wClassId        INT;
  DEFINE wSequenceNum    DEC;
  DEFINE        sqlErr                          INT;
  DEFINE        isamErr                         INT;
  DEFINE        errorInfo                       VARCHAR(70);
 
  ON EXCEPTION SET sqlErr, isamErr, errorInfo
    ROLLBACK WORK;
    -- Oh great. Something happened. Now, return item # back to the
    -- NewItemNumber table.
 
    IF wNewItemNumber >0 THEN
    --    IF sqlErr = -268 THEN
    --unique constraint error. 2 people simultaneously tries to get
    -- item# do not set statusid=0, someone else already has the number.
     --           return sqlErr;
     --     End IF
 
          If not exists(select * from item  WHERE  ItemNumber = wNewItemNumber) THEN
 
              UPDATE NewItemNumber
              SET  StatusID = 0
              WHERE ItemNumber = wNewItemNumber;
              --  return sqlErr;
 
          END IF
    END IF
 
    RAISE EXCEPTION sqlErr, isamErr, errorInfo;
  END EXCEPTION;
 
 
 
  LET wNewItemNumber = -9999;
  LET wAuthItemNumber = arg_AuthItemNumber;
  LET wDepartmentId = arg_DepartmentId;
  LET wCategoryId = arg_CategoryId;
  LET wClassId = arg_ClassId;
  LET wSequenceNum = 0;
 
  -- Get available item number based on the Wegmans brand indicator
  IF arg_WegmansBrandIND = 0 THEN
    FOREACH
    SELECT FIRST 1 ItemNumber, AvailableDate,ChangedBy
      into wNewItemNumber, wCreateDate, wChangedBy
    FROM NewItemNumber
    WHERE StatusID = 0
      AND ItemNumber > 99999
      AND VendorId = 0
    ORDER BY AvailableDate
    END FOREACH
  ELSE
    FOREACH
    SELECT FIRST 1 ItemNumber ,AvailableDate,ChangedBy
      into wNewItemNumber, wCreateDate, wChangedBy
    FROM NewItemNumber
    WHERE StatusID = 0
      AND ItemNumber <= 99999
      AND VendorId = 0
    ORDER BY AvailableDate
    END FOREACH
  END IF
 
   IF wNewItemNumber <= 0 THEN
    RAISE EXCEPTION -746, 0, 'No New Item Numbers Available!!';
  END IF
 
 
  IF arg_CloneItemNumber = -9999 THEN
    LET arg_CloneItemNumber = 0;
  END IF
 
  BEGIN WORK;
  -- Set the Status for the new item number to make it unavailable
  -- This needs to happen ASAP in this routine to limit exposure to
  -- new item number conflicts
  UPDATE NewItemNumber
    SET  StatusID  = -1
  WHERE ItemNumber = wNewItemNumber;
 
  COMMIT WORK;
 
  BEGIN WORK;
  -- Validate clone item number (only if there was one passed in)
  IF arg_CloneItemNumber <> 0 THEN
    SELECT Count(ItemNumber)
      into wCloneItemCount
    FROM Item
    WHERE ItemNumber = arg_CloneItemNumber;
 
        IF wCloneItemCount = 0 THEN
          RAISE EXCEPTION -747, 0, 'Clone Item does not exist!!';
        END IF
  END IF
 
 
 
  -- Set some other data if there was a clone item provided
  IF arg_CloneItemNumber <> 0 THEN
    -- Set auth item to be the clone one if it wasn't provided
    IF wAuthItemNumber = -9999 THEN
      LET wAuthItemNumber = arg_CloneItemNumber;
    END IF
 
   -- Get dept/cat/class from the clone item if they were partially
   -- provided or not provided at all
    IF wDepartmentId = -9999 OR wCategoryId = -9999 OR wClassId = -9999 THEN
      FOREACH
      SELECT FIRST 1 DepartmentId, CategoryId ,ClassId
        into wDepartmentId, wCategoryId, wClassId
      FROM Item
      WHERE ItemNumber = arg_CloneItemNumber
      END FOREACH
    END IF
  END IF
 
  -- Get the sequence number
  SELECT Max(SequenceNumber) + 1
    into wSequenceNum
  FROM Item
  WHERE ClassId = wClassId;
 
  IF wSequenceNum IS NULL THEN
    LET wSequenceNum = 0;
  END IF
 
  -- Build all the related item records...SNIC should always use
  -- itemnumber 0
  IF arg_StatusId < 5 AND arg_CloneItemNumber <> 0 THEN
        CALL sc_CopyItem(arg_CloneItemNumber, wNewItemNumber, arg_UserID, arg_statusID);
  ELSE
        CALL sc_CopyItem(0, wNewItemNumber, arg_UserID, arg_statusID);
  END IF
 
  -- First, update the Status in case any stored procs need to
  -- look at it
  UPDATE Item
    SET (StatusId, Description, SequenceNumber) = (arg_StatusId, arg_Description, wSequenceNum)
  WHERE ItemNumber = wNewItemNumber;
 
  -- If no clone provided but we have at least a departmentid,
  -- then update the product group
  IF arg_CloneItemNumber = 0 AND arg_DepartmentID > 0 THEN
    UPDATE Item
       SET (DepartmentID, CategoryID, ClassID) =
           (arg_DepartmentID, DECODE(arg_CategoryID,-9999,NULL,arg_CategoryID), DECODE(arg_ClassID,-9999,NULL,arg_ClassID))
     WHERE ItemNumber = wNewItemNumber;
  END IF
 
  -- Run these for SNIC
  IF arg_StatusId = 5 THEN
    -- Now copy over all the Clone info
    CALL sc_CopyCloneProperties(wNewItemNumber, arg_CloneItemNumber, arg_UserID);
    CALL sc_CloneStoreAuthRecords(wAuthItemNumber, wNewItemNumber, 'Clone');
  END IF
 
  -- Update short description
  UPDATE ItemDescription
    SET (ShortDescription, POSDescription) = (arg_Description, '')
  WHERE ItemNumber = wNewItemNumber;
 
    -- Set pos description to blank
  UPDATE ItemPOS
    SET (POSDescription) = ('')
  WHERE ItemNumber = wNewItemNumber;
 
  -- Now move the item into the correct product group
  IF exists (select id from pgclass where id = wClassId) THEN
    CALL sc_MoveItem(arg_UserID, wNewItemNumber, wDepartmentId, wCategoryId, wClassId);
  END IF
 
  -- Update the table of Contents for the New item
  CALL sc_InsertNewItemStatusRecords(wNewItemNumber);
 
  COMMIT WORK;
 
  BEGIN WORK;
  -- At this point, I know the new number worked. That is, either
  -- I won the RACE condition or I'm not competing for the new item #
 
    UPDATE NewItemNumber
    SET (StatusID, ChangedBy) = (arg_StatusID, arg_UserID)
  WHERE ItemNumber = wNewItemNumber;
 
  COMMIT WORK;
 
  RETURN wNewItemNumber;
 
END FUNCTION;

Open in new window

0
THBAAuthor Commented:
On the UI side, I will check the return code and exceptions.  If the return code is -268 (or sql error code is -269), then I know someone else got the item number. The  web application will resubmit and try to get another new item number (3x). Another word, the web application will call the stored procedure up to 3 times before giving up and sending an error to the user.
0
gheistCommented:
At least it works.
I was thinking putting new number generation and setting status to nonavail in same transaction.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
THBAAuthor Commented:
Testing it right now. Will close and award points by the end of the week.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Databases

From novice to tech pro — start learning today.