Locking assistance required

Question

I have a stored procedure that SELECTs the TOP 1 record from the table and assigns some variables with the values of the columns. It then SELECTs 10 records where the Id column has the same value as the TOP 1 variable column.

SELECT TOP 1 @Id = [Id]
  FROM [TableA]
 ORDER
    BY [DateCreated], [Priority]

SELECT TOP 10 *
  FROM [TableA]
 WHERE [Id] = @Id;

The purpose is to SELECT one record, and then get 10 matchings records to process. This works if one connection is calling the stored procedure. I have an application which is multi-threaded and this then causes a problem, because, if the first and second records are the same Id, I'd like them to be in one result set so that the second thread will pick up a different value. Here is where I have the problem. I've tried to use HOLDLOCK, UPDLOCK, ROWLOCK hints on the first SELECT statement, but that locks the first record and allows another thread to pick up the next value which I'd like in the first thread. I have also started a TRANSACTION within the stored procedure and changed the ISOLATION LEVEL to READ COMMITTED, but the second thread is always getting in and taking that next value before the first thread can SELECT the 10 records where the Ids match.

Does anyone have anyone have any ideas on how I should go about this? Is this approach possible?

possible duplicate of [SQL Server Process Queue Race Condition](http://stackoverflow.com/questions/939831/sql-server-process-queue-race-condition) — gbn, Dec 12 '11 at 09:54
What ORDER BY should apply to the queries? What does the full code look like? What does the table look like? — gbn, Dec 12 '11 at 09:57

score 1 · Accepted Answer · answered Dec 12 '11 at 10:03

Your approach seems odd: selecting from the same table, TOP without ORDER BY etc.

In the absence of any other information and assuming you want to keep the same solution, you'd have to use a semaphore solution with sp_getapplock. This will serialise access through your code: a 2nd process can wait or timeout etc depending how you set the parameters

Locking assistance required

1 Answers1