Building lock based process control is to avoid conflicting processes from running at same time. A very common question DBAs undergo is that Task A can be launched when Task B(s) are not running. Many times I heard that “I will have to disable jobs, wait until they are finished, start another job, wait until it finishes, and then enable the jobs were disabled previously.”. The cycle of this process some time is just very few minutes but mostly it’s half to few hours which is occasionally scheduled in the midnight. The work like this is no longer to be fun to DBAs. It would be nice if something can be implemented in the process which it can detect, wait, and/or safely stop or interrupt running business operations, start its own process after all business processes stopped, and then resume the business processes automatically…This is possible in SQL Server. SQL Server application locks can even bring more value for such matters.

Senario 1
“I have ten jobs running. I need run anther job after those ten complete”. This is exactly what has been described in the previous paragraph. In this case, you can acquire a Shared lock before each of those ten jobs running and then release them at the completion of the jobs.

exec sp_getapplock @Resource = 'MyResource', @LockMode = 'Shared', @LockOwner = 'Session', @LockTimeout = -1
--you code in the job 
exec sp_releaseapplock @Resource = 'MyResource', @LockOwner = 'Session'

In the job conflicting with other ten jobs, you need use exclusive lock.

exec sp_getapplock @Resource = 'MyResource', @LockMode = 'Exclusive', @LockOwner = 'Session', @LockTimeout = -1
--you code in the job 
exec sp_releaseapplock @Resource = 'MyResource', @LockOwner = 'Session'

In the duration of each of those ten running jobs, Shared locks on resource MyResource are granted for each of them until job finishes. Since there is no conflict between shared locks on one resource, jobs will not be blocked. When the conflicting job is coming, it will request an exclusive lock on the same resource hold by other jobs. Thus, the conflicting job will wait. Any jobs need Shared lock on MyResource will wait due to the waiting conflicting job. When there isn’t any Shared locks blocking Exclusive lock requested by the conflicting job. The conflicting job will start working. After the completion of conflicting job, the jobs need Shared lock queued behind it will start. So you garanteed that there is no regular jobs running while conflicting job is running. This is also the concept of semaphore we talked in last post of this series.

Senario 2
“I have two group of jobs, Group A and Group B. Jobs in Group A can run concurrently. Jobs in Group B can run at same time as well. However, Group A must conflict with Group B”. The’s the same way that we will consider granting lock for each job in both group before it’s started. We need to choose the lock being used for the jobs in the same group not conflicting with itself but conflicting with the locks to be used for the jobs in other group. I choose Intent Exclusive lock for Group A’s jobs and Shared lock for Group B.

---Lock for Group A
exec sp_getapplock @Resource = 'MyResource', @LockMode = 'IntentExclusive', @LockOwner = 'Session', @LockTimeout = -1
Print 'Group A '
exec sp_releaseapplock @Resource = 'MyResource', @LockOwner = 'Session'
--- lock for Group B
exec sp_getapplock @Resource = 'MyResource', @LockMode = 'Shared', @LockOwner = 'Session', @LockTimeout = -1
Print 'Group B '
exec sp_releaseapplock @Resource = 'MyResource', @LockOwner = 'Session'

When the first job from Group A comes in, IX lock is granted, it runs. When the second job from Group A kicks off, it will run as well because IX lock is compatible with IX lock. While previous 2 jobs running, a job from Group B jumps in, it will be waiting for IX lock to be released in order to have Shared lock granted.

---Session 1
exec sp_getapplock @Resource = 'MyResource', @LockMode = 'IntentExclusive', @LockOwner = 'Session', @LockTimeout = -1
Print 'Group A, job 1'
--Group A, job 1 

---Session 2
exec sp_getapplock @Resource = 'MyResource', @LockMode = 'IntentExclusive', @LockOwner = 'Session', @LockTimeout = -1
Print 'Group A, job 2'
--Group A, job 2

---Session 3
exec sp_getapplock @Resource = 'MyResource', @LockMode = 'Shared', @LockOwner = 'Session', @LockTimeout = -1
Print 'Group B, Job 1 '
--- this will wait

---Go to Session 1
exec sp_releaseapplock @Resource = 'MyResource', @LockOwner = 'Session'
-- Session 3 will still be waiting

---Go to Session 2
exec sp_releaseapplock @Resource = 'MyResource', @LockOwner = 'Session'
-- Session 3 will stop waiting and execute

-- Then execute jobs in session 2
exec sp_getapplock @Resource = 'MyResource', @LockMode = 'IntentExclusive', @LockOwner = 'Session', @LockTimeout = -1
Print 'Group A, job 2'
-- it will be waiting becuase session 3 holds Shared Lock

--- Go to Session 3
exec sp_releaseapplock @Resource = 'MyResource', @LockOwner = 'Session'
-- session 2 will return, if you re-run line 12 and 13 in Session 3, it will wait.

One thing you have to be aware of is that while A is running, B comes in and wait completion of A, and then another A starts, the last A will be waiting until B gets running signal and finished. If you have a system which has group of operations where A is conflicting to B and C is conflicting to D. You can use the same way but let A and B to utilize lock resource ‘MyResource1’ and let C and D to use lock resoure ‘MyResource2’.

As a variation of this solution, what if “I have two group of jobs, Group A and Group B. Jobs in Group A can run concurrently. Jobs in Group B can run at same time as well. Group A and Group B are conflicting with each other. While jobs in Group A are running, all coming jobs in Group will stay priority, any jobs in Group B should not block. While jobs in Group B are running, all coming jobs in Group B stays priority”. In this case, you can adjust lock timeout value. See the code below.

---Lock for Group A
declare @ret int
exec @ret = sp_getapplock @Resource = 'MyResource', @LockMode = 'IntentExclusive', @LockOwner = 'Session', @LockTimeout = 0
if @ret <0
	return
Print 'Group A '
exec sp_releaseapplock @Resource = 'MyResource', @LockOwner = 'Session'
go
--- lock for Group B
declare @ret int
exec sp_getapplock @Resource = 'MyResource', @LockMode = 'Shared', @LockOwner = 'Session', @LockTimeout = 0
if @ret <0
	return
Print 'Group B '
exec sp_releaseapplock @Resource = 'MyResource', @LockOwner = 'Session'

Senario 3
"I have a system that I hope while OP1 is running one or few subsystems should wait, and when OP2 is running, entire system should wait". In this case, you should consider to build your system processes in a hierarchical structure as showing below.

• Entire System (IX on ResA)
  • Subsystem1 (IX on ResB)
    • Process 1 (X on ResC)
    • Process 2 (X on ResD)
    • Process 3 (X on ResE)
  • Subsystem2 (IX on ResF)
    • Process 4 (X on ResG)
    • Process 5 (X on ResH)
  • Subsystem3 (IX on ResI)
    • Process 6 (X on ResJ)
    • Process 7 (X on ResK)
    • Process 8 (X on ResL)

The idea behind it is that when you run Process 1, you should acquire IX lock on ResA then acquire IX lock on ResB and last acquire X lock on ResC ( if Process 1 can have multiple instances, you should use IX on ResC). The same approach is applied to all rest of processed from 2 t 8, for instance before running Process 8, you need to have IX on ResA, IX on ResI, and X on ResL.
if OP1 conflicts with Subsystem 2, then you should try to get S lock on ResF for OP1. If you want to stop entire system before running OP2, then you only need to grant S lock to ResA for OP2. When OP1 or OP2 finish, S lock will be released and system resume to normal status automatically.