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:
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?