Row locks protect rows, but sometimes the thing to protect is a process: only one session should run the nightly repricing at a time, however many try to start it. DBMS_LOCK provides named user locks for this. Sessions request a lock by name, and only one can hold it in exclusive mode.
Code for This Guide
The main examples are in the examples/pkg-utilities folder of the Oracle Database 26ai code repository on GitHub, each with its output. They use NIMBUS, the sample schema of a fictional airline, which you can install with the scripts in the setup/nimbus folder.
They come from Oracle Database 26ai SQL and PL/SQL Book.
Syntax
dbms_lock.allocate_unique(lockname, lockhandle_out);
result := dbms_lock.request(lockhandle, lockmode => dbms_lock.x_mode,
timeout => seconds, release_on_commit => boolean);
result := dbms_lock.release(lockhandle);REQUEST returns 0 for success, 1 for a timeout, 2 for a deadlock, 3 for a parameter error, 4 if the session already holds the lock, and 5 for an illegal handle. RELEASE returns 0, 3, 4 (not owned), or 5.
Request and Release a Lock
Example:
declare
v_handle varchar2(128);
v_result integer;
begin
dbms_lock.allocate_unique('NIMBUS_NIGHTLY_REPRICING', v_handle);
v_result := dbms_lock.request(v_handle, dbms_lock.x_mode, timeout => 0,
release_on_commit => false);
dbms_output.put_line('request: ' || v_result || ' (0 = success)');
v_result := dbms_lock.request(v_handle, dbms_lock.x_mode, timeout => 0);
dbms_output.put_line('again: ' || v_result || ' (4 = already own it)');
v_result := dbms_lock.release(v_handle);
dbms_output.put_line('release: ' || v_result);
end;
/Output:
request: 0 (0 = success) again: 4 (4 = already own it) release: 0 PL/SQL procedure successfully completed.
ALLOCATE_UNIQUE turns the name into a handle. The first request succeeds; the second returns 4, since this session already holds the lock. A second session asking with timeout 0 would get 1 and know the job is already running.
Release on Commit
By default a user lock is held until it is released or the session ends. With RELEASE_ON_COMMIT => TRUE, the lock ends with the transaction.
Example:
declare
v_handle varchar2(128);
v_result integer;
begin
dbms_lock.allocate_unique('NIMBUS_FARE_IMPORT', v_handle);
v_result := dbms_lock.request(v_handle, dbms_lock.x_mode, timeout => 0,
release_on_commit => true);
dbms_output.put_line('request: ' || v_result);
commit; -- releases the lock
v_result := dbms_lock.release(v_handle);
dbms_output.put_line('release after commit: ' || v_result || ' (4 = not owned)');
end;
/Output:
request: 0 release after commit: 4 (4 = not owned) PL/SQL procedure successfully completed.
After the COMMIT, the session no longer holds the lock, so RELEASE returns 4.
Things to Know
- Using DBMS_LOCK needs EXECUTE on the package, granted by a DBA.
- ALLOCATE_UNIQUE commits the current transaction, so call it before starting DML, or use ALLOCATE_UNIQUE_AUTONOMOUS, which does not.
- Choose names with an application prefix; lock names are shared across the whole database. Names starting with ORA$ are reserved.
Related Guides
Conclusion
DBMS_LOCK gives PL/SQL named locks for protecting processes rather than rows. Allocate a handle from a unique name, request it with a timeout and check the result code, and release it when the work is done or tie it to the transaction with RELEASE_ON_COMMIT.
See my articles more often when you search Google.
