SQL Server - Locking/Blocking scenario

Question

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: -

Only one transaction should process a file at a time.
Different files are processed concurrently.
If an attempt to process the same file is made whilst it is being processed then that transaction will block until the other one finishes.

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?

possible duplicate of [SQL Server Process Queue Race Condition](http://stackoverflow.com/questions/939831/sql-server-process-queue-race-condition) — gbn, Dec 09 '11 at 10:55

score 2 · Answer 1 · edited May 23 '17 at 10:08

2

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

edited May 23 '17 at 10:08

Community

1
1

answered Dec 09 '11 at 11:00

gbn

422,506
82
585
676

See my 2nd paragraph: the pattern you want is normally that some client code will call the database, get some info on what to process, process it, write back. You don't keep transactions open – gbn Dec 09 '11 at 11:21

score 0 · Answer 2 · answered Dec 09 '11 at 10:42

0

Try adding READPAST to allow point 2.

SQL Server - Locking/Blocking scenario

2 Answers2