@mishaluba

That's exactly the scenario I was looking to solve (and the problem of cleaning up these artifacts we create) while I was looking for this solution. When something happens and doesn't terminate cleanly we have to resort to manual overrides and failed processes.

Your understanding seems sound, but I would also say that just because Process 2 starts doesn't mean that Process 1 completed successfully. Only that it isn't running! You would need to check somewhere else to ensure that it had completed successfully.

This is based on multiple clients accessing a single db server. If you wanted to synchronise between two database servers you would have to elect one of them to be the co-ordinator and access the stored procedure using linked servers.

The scenario we use this technique for is based on a batch job (Batch A) of stored procedures that run every 30 minutes. While that is running we don't want another batch job (Batch B which runs twice a day) to start. There are 2 versions of Batch B to handle different countries and currencies and we are happy to run them side by side, so they get Shared application locks. But while Batch A is running they may not start, so Batch A takes an Exclusive lock. This also has the added benefit that if Batch B runs late, then Batch A cannot start.

This technique is not about synchronising data within a transaction. SQL does that fine. Its about co-ordinating business processes.