Oracle – Creating Non-Overlapping Jobs

jobsoracle

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.

Best Answer

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

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

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
as
  l_status integer;
begin
  l_status := dbms_lock.request( 'MY_LOCK_NAME' );
  if( l_status in (1,4) )
  then
    <<do actual work>>
  end if;
end;
Related Question