4

What would be good ways to design a group of jobs such that they can never run at the same time?

I am looking for a generic concept, like a semaphore, which makes it easier to ensure that some jobs are never running at the same time.

For example, I'm thinking of a "signal table". When a job starts, it sets the signal and at the end, it resets the signal. In addition, every job needs to wait until the signal is set. (How to implement a CPU friendly waiting?) However, when a job crashes, there must then be a solution to automatically reset the signal, otherwise jobs would never start.

We have 4 jobs which are doing various updates, imports, calculations. They all are running by scheduler, and in addition, some of them can also be started manually by super users in case a special run is needed. However, they should not run at the same time, because it often results then in locks or even deadlocks and the database does not properly respond to the users then.

EDIT: It's a data warehouse which is gathering and consolidating data from multiple external data sources. Every job is responsible for another data source, like this:

  • job #1 is running every 15 minutes
  • job #2 every 3 hours
  • job #3 and Job #4 run once per day or have extra runs when started by super users.
5
  • 1
    Couldn't you just set up a single job with 4 consecutive steps ?
    – Kate
    Dec 19, 2020 at 11:43
  • Seconded putting them as separate steps in the same job. It's almost no different than having 4 separate jobs in your case, unless some of the current jobs are truly time dependent due to external conditions, e.g. Job 1 needs to load data from Table A after it's been staged with data in the morning and Job 4 needs to truncate Table A at the end of the day. Though there's ways to design around that too. Also if you locking is the issue you're trying to solve, you should try to tune the performance of the queries in the jobs themselves rather than try to force them to run serially.
    – J.D.
    Dec 19, 2020 at 12:52
  • @J.D. No, it's not possible to put the jobs into one single job, because they are running at different frequency.
    – askolotl
    Dec 19, 2020 at 14:34
  • @J.D.I edited the question to make the usage of the database more clear,
    – askolotl
    Dec 19, 2020 at 14:42
  • @askolotl Fair enough, hopefully my answer solves your issue. Nice username by the way. ;)
    – J.D.
    Dec 19, 2020 at 17:26

3 Answers 3

4

Aside from the comments, here's something you can use to accomplish what you're trying to do (since I'm not a fan of reinventing the wheel for locking systems).

You can use the system stored procedure msdb.dbo.sp_help_job to determine if your other jobs are running as the first step in each job. Then from there you can choose what logic you want it to take next when another job is running such as ending the job (if it's on a routine schedule) or implement a mock sleep using WAITFOR DELAY ('00:00:01') -- Sleeps for 1 second, which you can put inside a WHILE loop to recheck job status.

You can call the sp_help_job by passing in the job name:

EXEC msdb.dbo.sp_help_job @Job_name = 'TheJobName'

And you can use the columns current_execution_status and current_execution_step of the procedure's result set, to help you determine when it's safe to run the current job.

There's also some system job tables that might be helpful to you to query as well, which you can read up on in this StackOverflow answer.

2

If SQL Server Enterprise Edition is being used, SQL Agent jobs can be classified into a workload group specifically for the agent based on account in suser_name in the classifier function. Or, the various steps of each relevant job could be used as app_name values in a classifier function. The assigned workload group can have set GROUP_MAX_REQUESTS to 1 so only 1 request can execute in the workload group at a time.

https://learn.microsoft.com/en-us/sql/t-sql/statements/create-workload-group-transact-sql?view=sql-server-ver15

0

Your suggestion of a semaphore table is a reasonable one. It puts onto you, as the application developer, all the responsibility for handling clean-up after failure of steps, jobs, system or hardware. You can pass all this work to SQL Server by using the built-in function sp_getapplock.

This functions just the same as the shared and exclusive locks taken on rows to prevent write conflicts. The difference is you get to name the lock and decide what it protects. Each job will gain a step at the beginning to acquire the lock and one at the end to release it. If the lock cannot be acquired because another job is running you can choose to wait in a queue or return immediately. The job can handle the appropriate actions in either case.

Should the job currently holding the lock fail SQL Server will automatically release the lock during its built-in tidy-up. No additional application logic is required. The next job to run will be free to acquire the lock and perform its processing.

3
  • I'm trying to use sp_getapplock in a SQL Server Agent job (or step thereof), but it is immediately returning with a -1 (unable to get lock). Any reason this wouldn't behave in an Agent job like it does in an SSMS session? EXEC @lock = sp_getapplock, @Resource = 'ProcessAccountUpdateQueue', @LockMode = 'Exclusive', @LockOwner = 'Session', @LockTimeout = 0
    – jklemmack
    Sep 9, 2021 at 23:58
  • @jklemmack Return code -1 is a timeout. For how long are other processes holding this lock? Extended events will allow tracing of lock acquisition & release. getapplock lets you set a timeout. Sep 12, 2021 at 4:14
  • I ended up renaming the lock & voila it worked. I suspect something else was holding that lock name.
    – jklemmack
    Sep 13, 2021 at 19:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.