0

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?

Improve this question
1

2 Answers 2

Reset to default
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

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

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
0

Try adding READPAST to allow point 2.

You might be interested in this article

Improve this answer

2 Comments

Unfortunately, the READPAST hint can't be used alongside the HOLDLOCK hint and if I remove the HOLDLOCK hint it breaks point 3.
Excellent article. You might want to promote it more in your answer. :)

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.