3

TL;DR

SQL Server race condition on a round-robin process queue despite adding ROWLOCK, READPAST, and UPDLOCK hints in conjunction with application locks.

I have an application where multiple app servers and multiple processes run "jobs" that are stored in a queue-like table in our SQL Server 2014 database. Every x number of seconds, each process queries the table for new jobs to run (if the process is available to take jobs). New jobs need to be assigned in something like a round-robin fashion on a user-to-user basis. The problem is that my current query is running into a race condition so that multiple processes are acting on the same job, resulting in duplicate data sets in our output tables. This is what the current query looks like:

EXEC sp_getapplock @Resource = 'GetJobAssignments', @LockMode = 'Exclusive', @LockOwner = 'Session';
BEGIN TRANSACTION;
    WITH cte AS
    (
    SELECT TOP (@nMaxJobs) [JobId]
        ,[Owner]
        ,[Timeout]
        ,[StartTime]
        ,[Status]
        ,[userID] 
        , ROW_NUMBER() OVER (PARTITION BY [userID] ORDER BY [JobID]) AS [recID] 
    FROM [Jobs]
    WITH (ROWLOCK, READPAST, UPDLOCK)
    WHERE [Status] = 0 AND [Owner] is null
    ORDER BY [recID], [userID]
    )
    UPDATE cte SET [Owner] = @owner, [Status] = 1;
COMMIT;
EXEC sp_releaseapplock @Resource = 'GetJobAssignments', @LockOwner = 'Session';

I have tried various combinations of ROWLOCK, READPAST, UPDLOCK, and XLOCK with no luck. Also used with and without the applock on both Session and Transaction settings. In other iterations of the query I messed around with putting the transaction isolation level to REPEATABLE READ and SERIALIZABLE with equal measures of no success.

Bearing in mind that I am neither an SQL nor a SQLServer expert, this next part might not mean anything...I have tried doing this within a stored procedure (as it is now), but in the past it has also been called from EF5 using dbContext.Database.ExecuteSqlCommand() (it is now just a stored procedure that is part of the dbContext).

This current query is the result of messing around with a couple known working answers to questions similar to this:

SQL Server Process Queue Race Condition

https://dba.stackexchange.com/questions/81791/strategies-for-checking-out-records-for-processing

Outside of basic research on the things I have mentioned here, I am pretty unfamiliar with strategies in SQL Server to prevent deadlocks/race conditions. Are there any other options that I could try in my query, or are there any known preconditions that might exist that I could check for that would prevent this current strategy from working?

Community
  • 1
  • 1
Nick Tallents
  • 131
  • 1
  • 5
  • Instead of wasting a bunch of time trying to do something with a database server that it probably never will do well, why not just use a message broker, e.g. RabbiMQ? – theMayer Feb 07 '18 at 19:03
  • @theMayer would it be easy to drop in RabbitMQ and implement implement for this use case? In my case there are several servers that use a DB to communicate. Can RabbitMQ be set up as a message queue accessible over ip? – seebiscuit Feb 07 '18 at 19:05
  • @seebiscuit Yes, that's what it was meant for. – theMayer Feb 07 '18 at 19:48
  • As you are using `sp_getapplock` in exclusive mode only one concurrent connection will be able to execute that bit of code. There must be something else going on. – Martin Smith Feb 07 '18 at 20:24
  • @MartinSmith do you mean something else like another `UPDATE` being made somewhere in the application? This is the only update that modifies the job owner in the application. – Nick Tallents Feb 07 '18 at 21:45
  • @theMayer I'm combing through the documentation now to see if rules can be set, but it looks like the round-robin used by RabbitMQ is prioritized by default by FIFO, where I want to prioritize by user first, then by queue entry – Nick Tallents Feb 07 '18 at 21:50
  • @NickTallents - If by queue entry, you mean FIFO, then yes. If you need by user, you would have to create a queue per user, then consume in priority order from among the several queues. – theMayer Feb 07 '18 at 22:43
  • @NickTallents - as [this answer](https://stackoverflow.com/questions/21363302/rabbitmq-message-order-of-delivery/21363518#21363518) illustrates, relying upon order of processing when you have multiple consumers is tenuous at best. – theMayer Feb 07 '18 at 22:45

0 Answers0