I have a SQL Server table full of orders that my program must “track” (call a web service to find out that something has been done with them). My application is multithreaded and may have instances running on multiple servers. Currently, each so often (by Threading timer) process selects 100 lines from a list of "unconfirmed" orders in random order ( ORDER BY NEWID()) and checks them, noting everything that returns successfully.
The problem is that there are many overlaps between threads and between different processes, and they do not guarantee that the new order will be checked in the near future. In addition, some orders will never be “confirmed” and will be dead, which means that they interfere with orders that need to be confirmed, slowing down the process if I continue to select them again and again.
I would prefer that all outstanding orders be systematically checked. I can imagine two simple ways:
- The application selects one order for verification at a time, passing in the last order, which it checked as a parameter, and SQL Server sends the next order, which is unconfirmed. More database queries, but this ensures that every order is checked in a reasonable amount of time. However, different servers can re-check the same order in a row, without need.
- SQL Server keeps track of the last order that it asked to check, maybe in a table, and gives a unique order for each query, increasing the counter. This is due to maintaining the last order somewhere in SQL, which I wanted to avoid, but it also ensures that the threads will not need to check the same orders at the same time.
Are there any other ideas that I am missing? Does that even make sense? Let me know if I need clarification.
RESULT:
, , LastCheckedForConfirmation , , Unconfirmed GETDATE() , . , ( , ), OrderNumber .
" ", : SP , " " > " ", , - 5 , 10, 20, 40, 80, 120, , 15 (6 ), , SP .
, - , , .