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?