0

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?

2

2 Answers 2

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

0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.