Hint to force locks between 2 Select clauses

Question

I have a process that run in parallel on several threads and also on several machines.

In summary, the process has to 'lock' a row in a table named 'Queue' and then retrieves some columns from the locked row and do some other logic operations.

My objective is to do this with the best performance: reliable info and fastest way.

This is how I design the query of the process:

UPDATE  MQ.dbo.Queue
            SET     queue_lock = 1,
                    Queue_Key = 'H10_P19_T0_19032017T193926148',
                    Queue_LockDate = getdate()
            where   Queue_ID = (select top 1 Queue_ID 
                                    from MQ.dbo.Queue with (nolock)
                                    where queue_lock = 0
                                    and QueueName_ID = 16
                                    order by queue_id) 
            And     isnull(queue_lock,0) = 0


GetQueue (Datasource=mydatabase, Time=1.12 ms, Records=0) in F:\Web\Process\Component\MQ.cfc

Select  top 1 Queue_ID,Queue_Message
            From    MQ.dbo.Queue with (nolock)
            Where   QueueName_ID = 16
                    and queue_lock = 1
                    and Queue_Key = 'H10_P19_T0_19032017T193926148'

The Queue_key consist of:

• Host_ID
• Process_ID
• Thread_ID
• Datestamp

This choice (Queue_key) was taken to tolerate parallelism on the process with reliable results.

My problem is that in some cases the second query (Select top 1....) returns 0 records even so it should not, since there are a lot of waiting row in the table 'Queue'.

And the percentage of this case rise when I rise the parallelism.

I know where is the gap in this 2 queries but I search for a way to overcome it and I need your help doing this.

In fact, the gap is in the select clause of the first query (the sub-query): Since select clauses hold only shared lock and 2 shared locks (2 select) don't block each other (are compatible), it is possible that 2 threads running in parallel retrieve the same row and only one update take place (because update hold exclusive lock on the row). So the second query (select top 1 ...) returns result for only one thread.

So I need through a lock hint (maybe updlock or rowlock), to don't allow 2 select clauses to retrieve the same row, without degrading a lot the response time.

Rq: the with (nolock) hint was added to increase the response time since shared lock is compatible with another shared lock (either with this hint or without it).

Can anyone have a suggestion for this type of scenarios?

Answer 1

If you add an OUTPUT clause to your UPDATE statement you could do all the work using one statement thus eliminating race conditions

Available on SQL Server 2005 and later

https://www.sqlservercentral.com/articles/the-output-clause-for-update-statements

Answer 2

Have you considered using BEGIN TRANS ... COMMIT TRANS to encapsulate your data?

You can combine that with the WITH (rowlock) syntax:

Begin Transaction Queue1
Begin Try
  update [...]

  Select [...] From [...]
  With (RowLock)

  Commit Transaction Queue1
End Try
Begin Catch
  Rollback Transaction Queue1
End Catch

Select With clause information here.

Exhaustive discussion on SQL Server locking strategies here.