Fetching records that are not being updated SQL Server

155 views Asked by At

I have an API that multiple processor requests work from. The job of the the API is to select records from a memory optimized table and mark the status assigned and hand it off to the requester. I have multiple processor that could request work at the same time and we keep getting this contention

Error Message :- The current transaction attempted to update a record that has been updated since this transaction started. The transaction was aborted.

How do i overcome this issue?

Here is a example of the GetWork method in the API

update memtabletemp set memtabletemp.status = 'ASSIGNED' , memtabletemp.statustimestamp = current_timestamp
                 OUTPUT INSERTED.memid,INSERTED.recordtype from(select top 5 memid, recordtype,status,statustimestamp from
                   memtable where status = 'READY')memtabletemp
0

There are 0 answers