0

I have a table in SQL Server 2012 that's being used by a service that's continually updating records within the table. It's sort of a queue where the service processes the records, and then periodically I run another stored procedure to pull out the ones that are processed into another table. Records in this table start out in one status and as they're processed they get put into another status.

When I try to run the stored procedure to pull the completed records out, I'm running into a deadlocking issue if it happens to occur when the running process is updating the table, which happens about every 2 minutes. I thought about just using a NOLOCK hint to eliminate that, but after reading a bit on this SO thread, I'm thinking I should avoid NOLOCK whenever possible.

GOAL:

Allow the service to continue running as usual, but also allow another stored procedure to periodically go in and remove records that are completed. In the event that there's a lock on a given row, I'd like to just leave that row alone and pick it up on the next time I run the stored procedure. During the processing, there's no requirement that I get all the rows with the stored procedure. That only matters once all the records have been processed, at which point I need to ensure that I get all the records, all while having the service still running on other unrelated records, and not causing any deadlocking issues. Hopefully this makes sense.

This article seems to suggest REPEATABLE READ

Am I on the right track or is there a better method?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
hyphen
  • 2,368
  • 5
  • 28
  • 59
  • 1
    [Using tables as Queues](http://rusanu.com/2010/03/26/using-tables-as-queues/) – Lukasz Szozda Aug 18 '18 at 09:09
  • @LukaszSzozda - so based on your link I have basically a heap queue. So based on that and looking at this answer (https://stackoverflow.com/a/24224263/4113027), I'm thinking I would need to add `with (UPDLOCK, ROWLOCK)` to the queries used by the service, and anything consuming from the queue would need the `with (READPAST)` hint? – hyphen Aug 18 '18 at 09:36
  • 1
    Did you consider triggers? – JohnyL Aug 18 '18 at 10:41
  • @JohnyL - no, but the nature of what I'm doing i think would prevent that as a solution. The trigger would have to live on the queue, and eventually there may be multiple processes feeding into the queue from unknown tables. I guess I could add the source table as a column and somehow dynamically send that data back to the source table. Or maybe set up a "completed" table and have the trigger just move it out of the queue into the completed table. and then any other processes would read from the completed table. that could work.. – hyphen Aug 18 '18 at 11:03
  • 1
    @hyphen Yes.Something like the answer you linked to. – Lukasz Szozda Aug 18 '18 at 15:27

0 Answers0