Preventing simultaneous execution of a series of operations, such as jobs, from different sessions in the Oracle database

🔷️Preventing simultaneous execution of a series of operations, such as jobs, from different sessions in the Oracle database

Alireza Kamrani
07/12/2025

If you want to ensure that your job is not executed concurrently (i.e., multiple instances running at the same time), the simplest approach is to use the DBMS_LOCK package to implement an application-level lock. You can achieve this by acquiring a user-defined lock at the beginning of your job or procedure (e.g., update_pricerange), like in the following example:


CREATE OR REPLACE PROCEDURE test IS
  l_lock_name    VARCHAR2(30) := 'LOCK_NAME';
  l_lock_handle  VARCHAR2(128);
  l_status       INTEGER;
BEGIN
  -- Allocate a unique lock handle
  DBMS_LOCK.ALLOCATE_UNIQUE(l_lock_name, l_lock_handle);
 
  -- Request the lock (default timeout: wait indefinitely)
  l_status := DBMS_LOCK.REQUEST(l_lock_handle);

  -- Handle possible lock request outcomes
  IF l_status = 1 THEN
    RAISE_APPLICATION_ERROR(-20001, 'Timeout waiting for ' || l_lock_name);
  ELSIF l_status = 2 THEN
    RAISE_APPLICATION_ERROR(-20002, 'Deadlock detected for ' || l_lock_name);
  ELSIF l_status = 3 THEN
    RAISE_APPLICATION_ERROR(-20003, 'Parameter error for ' || l_lock_name);
  ELSIF l_status = 5 THEN
    RAISE_APPLICATION_ERROR(-20005, 'Illegal lock handle for ' || l_lock_name);
  END IF;

  -- <<Do your job logic here>>
  -- <<Your critical section logic here>>

  -- Release the lock
  DBMS_LOCK.RELEASE(l_lock_handle);
END;
/

With this setup, if another session attempts to run the same procedure simultaneously, it will wait indefinitely (by default) for the lock to be released unless you explicitly specify a timeout in the REQUEST call.

🅾️Oracle Documentation Reference:

• DBMS_LOCK.ALLOCATE_UNIQUE: Registers and returns a unique lock handle for the named lock.
Reference: Oracle® PL/SQL Packages and Types Reference → DBMS_LOCK
• DBMS_LOCK.REQUEST: Requests the lock. Parameters allow you to define timeout and lock mode.
Default mode is 6 (exclusive), and timeout is -1 (wait indefinitely).
Return status values:
• 0 = Success
• 1 = Timeout
• 2 = Deadlock
• 3 = Parameter error
• 4 = Already owned
• 5 = Illegal lock handle
• DBMS_LOCK.RELEASE: Releases the acquired lock.

▶️Lock Modes

Mode 6 = Exclusive

Mode 4 = Share (used for less strict control)

Mode 0 = Null (only for checking existence)

🟪Additional Tips:
• If you don’t want the job to wait forever, use the timeout parameter in DBMS_LOCK.REQUEST, like so:
l_status := DBMS_LOCK.REQUEST(l_lock_handle, timeout => 10); -- waits 10 seconds
• It’s good practice to always wrap the lock and release logic inside BEGIN...EXCEPTION...END to ensure the lock is released on error.

🔷️Example:
DBMS_SCHEDULER Job with Lock to Prevent Concurrent Execution

Let’s assume you have a procedure update_pricerange that must not run concurrently if triggered by overlapping job runs.

1. Wrap the logic inside a procedure with DBMS_LOCK

CREATE OR REPLACE PROCEDURE update_pricerange IS
  l_lockhandle VARCHAR2(128);
  l_result     INTEGER;
BEGIN
  -- Allocate a unique user lock
  DBMS_LOCK.ALLOCATE_UNIQUE('LOCK_UPDATE_PRICERANGE', l_lockhandle);

  -- Try to acquire exclusive lock (mode 6)
  l_result := DBMS_LOCK.REQUEST(
                lockhandle         => l_lockhandle,
                lockmode           => 6,
                timeout            => 0,         -- Don't wait, fail immediately if locked
                release_on_commit  => FALSE);

  IF l_result = 0 THEN
    -- Lock acquired successfully, run your code
    DBMS_OUTPUT.PUT_LINE('Lock acquired. Executing job...');

    -- Your actual job logic here
    NULL; -- Replace with actual code

    -- Manually release the lock if needed
    DBMS_LOCK.RELEASE(l_lockhandle);

  ELSE
    DBMS_OUTPUT.PUT_LINE('Another instance is running. Skipping execution.');
  END IF;
END;
/

2. Schedule the procedure using DBMS_SCHEDULER
BEGIN
  DBMS_SCHEDULER.CREATE_JOB (
    job_name        => 'JOB_UPDATE_PRICERANGE',
    job_type        => 'PLSQL_BLOCK',
    job_action      => 'BEGIN update_pricerange; END;',
    start_date      => SYSTIMESTAMP,
    repeat_interval => 'FREQ=MINUTELY; INTERVAL=10',  -- adjust as needed
    enabled         => TRUE,
    comments        => 'Prevents overlap using DBMS_LOCK');
END;
/

🔶️Notes
• timeout => 0 means the session won’t wait if the lock is already held. You can set a wait time (e.g., timeout => 60) if slight delay is acceptable.
• release_on_commit => FALSE keeps the lock until explicitly released (or session ends).
• You can also use this logic in ad hoc calls, user-triggered jobs, or concurrent application sessions.


🟠Optional: View Lock Usage at Runtime
SELECT * FROM dba_lock WHERE type = 'User Lock';
Would you like a version that automatically logs lock contention or alerts if skipped due to lock (e.g., via email or logging table)?


Do you know another methods to overcome this challenge?