How can I ensure that a job will never have more than one instance running?

For example, if I have a job that is scheduled to run once a minute, but it actually runs for three minutes, I don't want a second and third instance of the job to start and run concurrently.

  • and by jobs I mean "not the employment kind"! – Mark Harrison Aug 8 '15 at 2:38
  • Are you using the dbms_job or the dbms_scheduler package? – Justin Cave Aug 8 '15 at 5:39
  • I typically use dbms_job, but that's not a firm requirement. – Mark Harrison Aug 8 '15 at 16:10

Using dbms_job, you're never going to have two instances of a job running at the same time. If I do something like this with an interval that is 1 minute in the future and a procedure that takes 3 minutes to run

  l_jobno pls_integer;
  dbms_job.submit( l_jobno,
                   'begin proc_that_takes_3_minutes(); end;',
                   'sysdate + interval ''1'' minute' );

the interval expression is only evaluated after the prior run finishes. There won't be 3 instances of the job running simultaneously. The job will just run once every 4 minutes.

This is why you frequently hear people about jobs "drifting" over time-- a job is scheduled to run at 4 AM with an interval of "sysdate + 1" so every day it runs at a slightly later time. If the job takes 1 minute to run, after 2 months, it's now running just after 5 AM. And that's why you'd generally want your interval to be something like "trunc(sysdate+1) + interval '4' hour" if you want to run every day at 4 AM.

If you want to provide additional protection in case, say, someone tries to run the procedure in a different session at the same time that the job is running, you can put a dbms_lock.request call in the procedure that tries to acquire a user-defined lock before doing the actual work.

create or replace procedure what_the_job_does
  l_status integer;
  l_status := dbms_lock.request( 'MY_LOCK_NAME' );
  if( l_status in (1,4) )
    <<do actual work>>
  end if;
| improve this answer | |

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.