App Locks and Read Committed Snapshot Isolation

Michael J Swart has a tip for those who have RCSI turned on and are using app locks:

The procedure sp_getapplock is a system stored procedure that can be helpful when developing SQL for concurrency. It takes a lock on an imaginary resource and it can be used to avoid race conditions.

But I don’t use sp_getapplock a lot. I almost always depend on SQL Server’s normal locking of resources (like tables, indexes, rows etc…). But I might consider it for complicated situations (like managing sort order in a hierarchy using a table with many different indexes).

Click through to see how it normally works and how you should switch things up if you’re using Read Committed Snapshot Isolation.