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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.