CODEWITHSUNDEEP
sqlsql-serversql-server-2005sql-server-2008
user1085351
user1085351

Reputation: 105

SQL Server - Locking/Blocking scenario

I have some file processing functionality which uses a database table to establish whether or not a file has been processed.

IF EXISTS (SELECT * FROM FileTable WITH (UPDLOCK, ROWLOCK, HOLDLOCK) 
           WHERE FileName = @FileToProcess)
BEGIN
    -- File already processed.
END
ELSE
BEGIN
    -- Process file.
END

The behaviour I want is as follows: -

I am pretty sure this is possible but my SQL locking knowledge is not quite up to scratch! My attempts so far either include the locking hints in the example above, which fails bullet point 2. All other locking hints I have tried have resulted in bullet point 3 failing.

What am I missing?

Upvotes: 0

Views: 415

Answers (2)

gbn
gbn

Reputation: 432260

I've answered similar questions before: SQL Server Process Queue Race Condition. In summary you need the ROWLOCK, READPAST, UPDLOCK hints to use a table as a queue.

However, you can't "block" the same file using DB engine locks because that implies leaving a transaction open while the file is being processed. What you can do is "flag" the file so it is skipped by another process in a safe concurrent fashion as per my link above

I've other answers too that may help you https://stackoverflow.com/search?tab=votes&q=user%3a27535%20readpast

Upvotes: 2

Martin Smith
Martin Smith

Reputation: 453212

Try adding READPAST to allow point 2.

You might be interested in this article

Upvotes: 0

Related Questions