I have a question regarding how to prevent multiple instances of an application and/or threads from selecting the same rows within an MSSQL table.
This is the scenario: I have an ASP.net site where users will be able to enter their email. The email will then be inserted into a table within my MSSQL Database.
Then the idea is to have a WebJob(Azure WebJob) that once every five second selects all newly created rows and then sends an email to each user and then flags the row as "processed"..
However.. What if I for some reason decide to scale my WebJob into multiple instances?.. I assume that there might be a smal window where multiple instances might select the same rows within the database and then process the rows and send multiple emails to the registered user? Is there someway to sort of "lock" the rows thats currently in process by another instance/thread?
Or am I approaching this the wrong way?
Br, Inx