Good morning. I'm hoping that someone has dealt with this error and can shed some light on how to keep it from happening. I have a stored procedure that is called to extract 100 rows from an order header file, as well as it's associated detail. The stored procedure does select the order header based on the processed flag being equal to 'N' and it also updates the processed flag to 'Y' once it's selected.
The problem is that there are multiple processes running simultaneously that call the same sproc and occassionally the same order is grabbed by more than one stored procedure. I was thinking about adding a batch# to the order header record and updating it when the record is selected. I would also include "where batch# = 0" to the selection of the header record, preventing the second stored procedure from selecting the same order.
Is this a viable solution, or does anyone have another way of doing it that might be more effecient? Any help would be greatly appreciated.