New locking features make parallel algorithms simpler.
The PSQL package dbms_lock has been expanded with functionalities for named locking within a session: request, release, is_acquired, and the table function held_locks. These locks only work within one’s own session; coordination across sessions, processes, or machines is beyond the scope.
Operation
A lock is requested with dbms_lock.request(name) and released with dbms_lock.release(name). Names are compared case-insensitively. A variant with a time limit, dbms_lock.request(name, timeout_sec), accepts seconds with decimals (0.25 is 250 milliseconds) and raises the catchable exception lock_timeout upon exceeding it.
An obtained lock applies to a non-parallel executed statement, such as an anonymous PSQL block or a block within a parallel. Locks not released lead to an error message at the end of the statement.
A lock can be acquired multiple times; a counter increments then. A lock is obtained if the counter is 1 or higher, and released if it is 0.
If a deadlock occurs, then deadlock_detected arises.
is_acquired(name) and held_locks() only answer the question for the calling frame.
Example: Requesting, Counting, and Releasing
The following example shows the counter and the table function held_locks:
declare
l_held boolean;
begin
dbms_lock.request('example');
dbms_lock.request('example');
for r in
( select name
, hold_count
from table(dbms_lock.held_locks())
)
loop
dbms_output.put_line(r.name || ' counter ' || r.hold_count);
end loop;
dbms_lock.release('example');
dbms_lock.release('example');
l_held := dbms_lock.is_acquired('example');
if not l_held
then
dbms_output.put_line('released');
end if;
end;
The output reads:
example counter 2
released
Example: Year-end Closing with Time Limit
The following example is directly executable and protects a financial closing process that must run at most once per administration at a time. The request waits at most thirty seconds; if the closing is still running elsewhere, the block reports it and stops without harm:
begin
create or replace table year_end_records@InMemoryStorage
as
select 2026 year_end
, 'OPEN' status
from dual@DataDictionary
;
begin
dbms_lock.request('year-end-closing-l123456789', 30);
exception
when lock_timeout
then
dbms_output.put_line('The year-end closing is still running elsewhere; this run skips.');
raise;
end;
--
-- The critical section: revaluation and closing entries of the administration.
--
update year_end_records@InMemoryStorage
set status = 'CLOSED'
where year_end = 2026
;
dbms_lock.release('year-end-closing-l123456789');
dbms_output.put_line('Year-end 2026 closed.');
drop table year_end_records@InMemoryStorage;
end;
The output reads:
Year-end 2026 closed.
See also
Availability
The new functionality is available from release 27.0.