Thank you both very much for your feedback!

Noel:

I agree, the

IF not exists()

Insert...

Technique is definitely flawed; I have seen this approach fail many times. Very good point about combining the existence check with the insert!

The locking hints in the second example will also, most definitely, block other processes. As you know, the blocking in that example is deliberate because we wish to ensure that multiple instances of a proc cannot run simultaneously. You are also correct that this can still produce unexpected results (blocking attempts to lock other rows).

The benefit is that, because you are simply acquiring a lock on a table that serves no other purpose than to be locked, you can block processes you wish you block, without blocking access to 'real' tables (and thus blocking processes you may not wish to be blocked).

Perhaps I should have been clearer in the article. The first two--I believe to be flawed--examples were approaches that I have seen used in the past. For this reason I concluded that if you needed to serialize calls in this manner, it would be best to use sp_getapplock (rather than the first two examples).

tymberwyld:

An example that springs to mind is a variation of what I first used this technique for. Imagine you have a 50 machines that are all retrieving sets of data from SQL server to process and then act on (e.g. sends an email to a customer). Also imagine that these sets of data are provided by a single stored procedure (this procedure will retrieve the N oldest rows, and then update the datestamp). As each machine completes processing of its respective set of data, it hits the PROC for a new set. Occasionally two machines will hit the database at the same time and end up with sets of data that overlap each other causing the applications to act twice on the same row(s) of data. By ensuring that only one application can call the proc at a time, you ensure that every application has a distinct set of data to work with.

I will try to think of some more examples where this might be useful.

I hope this helps!

-Robert Cary