@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.
To show the contents of the spool's internal worktable?
I mean, as I said, there are other ways, but they're quite convoluted compared with setting a trace flag
@Charlieface It does not, but (a) that's not really relevant in the question's context; and (b) which return code would you care about and how would you handle it in that specific scenario?
It's a shame sys.dm_db_page_info isn't more useful, or maybe we need a new DMV sys.dm_db_page_body_info. Then we could just apply it inside the same query.
> -1 The lock request timed out. -2 The lock request was canceled. -3 The lock request was chosen as a deadlock victim.
The examples don't use a time out do they? Lock request cancelled is never seen. I suppose a deadlock is possible in theory, but it's not really that uncommon to omit error handling code when demoing a separate concept.
I guess. Having said that, usually the whole point of using it is to avoid a deadlock, so you have to do something really stupid (like take locks in different orders in different places) to get a deadlock.
What does it mean to "cancel" a lock anyway? As opposed to a timeout?
MichaelSwart points out that sp_releaseapplock shouldn't be used under RCSI anyway, as the COMMIT does that. michaeljswart.com/2021/01/…
@Charlieface Seems like a moot point. You protect the thing that needs locking until it doesn't need locking any more. I suppose some people might be unaware that ending the transaction releases the app lock, but they should read the documentation.
People like that are the reason peanut butter has a warning about possibly containing nuts
