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.
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.
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...)
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)