CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Neil Knight
Neil Knight

Reputation: 48547

Row locks - manually using them

I basically have an application that has, say 5 threads, which each read from a table. The query is a simple SELECT TOP 1 * from the table, but I want to enforce a lock so that the next thread will select the next record from the table and not the locked one. When the application has finished it's task, it will update the locked record and release the lock and repeat the process again. Is this possible?

Upvotes: 7

Views: 3709

Answers (3)

Alexander Sigachov
Alexander Sigachov

Reputation: 1551

More detail article about this techtique "Processing Data Queues in SQL Server with READPAST and UPDLOCK"

https://www.mssqltips.com/sqlservertip/1257/processing-data-queues-in-sql-server-with-readpast-and-updlock/

Upvotes: 0

André Haupt
André Haupt

Reputation: 3524

Service Broker Queues in the Sql Server were designed specifically to address this scenario.

Having to lock tables and rows seems like a backwards way to achieve this functionality.

Upvotes: 0

AdaTheDev
AdaTheDev

Reputation: 147224

The kind of approach I'd recommend is to have a field in the record along the lines of that indicates whether the record is being processed or not. Then implement a "read next from the queue" sproc that does the following, to ensure no 2 processes pick up the same record:

BEGIN TRANSACTION

-- Find the next available record that's not already being processed.
-- The combination of UPDLOCK and READPAST hints makes sure 2 processes don't 
-- grab the same record, and that processes don't block each other.
SELECT TOP 1 @ID = ID
FROM YourTable WITH (UPDLOCK, READPAST)
WHERE BeingProcessed = 0

-- If we've found a record, set it's status to "being processed"
IF (@ID IS NOT NULL)
    UPDATE YourTable SET BeingProcessed = 1 WHERE ID = @ID

COMMIT TRANSACTION

-- Finally return the record we've picked up
IF (@ID IS NOT NULL)
    SELECT * FROM YourTable WHERE ID = @ID

For more info on these table hints, see MSDN

Upvotes: 8

Related Questions