@SeanGallardy I was under the impression that when @LockOwner is Transaction (the default) then it behaves the same way as any other lock. So you need to use XACT_ABORT ON to guarantee rollback, otherwise you are in the same position as any other lock taken in a transaction.
I agree that when under @LockOwner='Session' then you need to very carefully consider your backout code, in particular how connection pooling plays with it. But not sure why you'd ever use that option anyway.
Also that you do need to check the result of the EXEC for the error conditions (timeout, deadlock, cancellation), and it would be nicer if those errors just threw as normal.
First, I would avoid making a round trip to the database for every value. For example, if your application knows it needs 20 new IDs, do not make 20 round trips. Make only one stored procedure call, and increment the counter by 20. Also it might be better to split your table into multiple ones.
...
It is my first choice because I have researched and stress tested it only once, and I can reuse it in any situation - the serializing has already happened, so everything that happens after sp_getapplock does not affect the outcome. With standard locking mechanisms, I can never be so sure - adding an index or just getting another execution plan can cause deadlocks where before there were none. Ask me how I know. — A-KMar 16, 2013 at 1:35
I sometimes think people advocating "normal locking mechanisms" have never dealt with a real production database
For things like ensuring a procedure is run as a singleton, it is possible to (ab)use standard locking to achieve the same result, but I think an app lock conveys the intention better.
