I have a process that runs from a client application against an SQL Server database.
This applications reads data from TableEntries, then it performs some checks, run some storeds and after all that it decides if the entry is valid or not. Then, it deletes the record from the TableEntries and move it to either TableValids or TableInvalids.
Now, TableEntries is being filled by an eternal process, Oracle through heterogenous service, they run in READ COMMITED mode, the default.
They fill this table all day at any given moment, thay can send 100 , 500, 10000 or 20000 records in a given moment.
My process is sleeping and wake up every minute to process this data. It don't run again until it finishs its current task, so it never is running more than once at a given moment. All this have worked fine for months.
But now I've been told that this process must be able to run several instances at same time, now I have a doubt that some of my instance can re-read data that is being processed by another application instances therefore causing some invalid results.
Which isolation level should I use for :
Read 1 record from a table.
Run storeds and client processing.
Delete the record from it and save it to other two tables.
All this without any interfernce from each other application instance ?
Thanks in advance,