row level locking on sql server

Hello people,

here is the scenario: We have a message queue and we are processing that queue in multiple threads. Now each thread will take 25 email addresses from a particular table and then process it. So we are looking for a mechanism which actually provides us facility to lock a particular row in the table, once thread has picked it up for processing. We thought about setting a bit manually but select statement on TOP(25) and then update on the same row would be very time consuming in a sense what if another thread comes in  and picks up the thread.

Any suggestions?

Thanks
TheCommunicatorAsked:
Who is Participating?
 
Brendt HessSenior DBACommented:
The flagging of the row option is really your best option.  Something like
UPDATE myData
SET HandledBy = @me
WHERE myData.MyID IN (
   SELECT TOP 25 MyID
   FROM MyData
   WHERE HandledBy is null
   ORDER BY MyID
   )

Alternately, use an INSERT statement into a separate table with some error handling, e.g.

INSERT INTO ToHandle (
    MyId,
    HandleBy
    )
SELECT Top 25 MyID, @HandleBy
FROM MyData
LEFT JOIN ToHandle
    On MyData.MyID = ToHandle.MyID
    WHERE ToHandle.MyID is null
ORDER BY MyData.MyID

SELECT MyID
FROM ToHandle
WHERE HandleBy = @HandleBy

You can also use either of these mechanisms to recover safely from an error in processing.  And you can still limit it to 25 rows if you change your TOP to a calculation based on 25 - the number of rows already flagged as handled by @HandleBy.  You would of course need a check for the case of 25 still to be processed.
0
 
Ephraim WangoyaCommented:

If you want to set a field marker in the table then you need to make use of a stored procedure. A simple query will not do, just as you realized in your statement.

You can have two fields in the table, one for the user locking the record and another for the Lock bit indicator
LockUser varchar(40)
Status bit

Your stored procedure will accept user and status and return to you the locked records that you can work on

procedure LockRecords(@user varchar(40), out @RecordIDs varchar(4000))

@RecordIDs could be a delimited list of the records that were locked successfuly
   
0
 
TheCommunicatorAuthor Commented:
Thank you so much guys. The information that you guys gave was helpful:)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.