In the Oracle database, there is a feature named the "dbms_lock" package that allows the user to do application level locking. Now in PostgreSQL, we can use a similar behavior of the dbms_lock package in Oracle by using the extension named pg_dbms_lock. By using this extension, we can do an application level locking through Postgres. But under the hood, it actually uses the PostgreSQL advanced features like shared & exclusive advisory locks, and they also use the pg_sleep function.
Clone the repository.
git clone https://github.com/HexaCluster/pg_dbms_lock.git
Go to the folder
cd pg_dbms_lock
Compile and install this extension into the system installed postgres
make USE_PGXS=1 PG_CONFIG=/usr/lib/postgresql/18/bin/pg_config
sudo make USE_PGXS=1 PG_CONFIG=/usr/lib/postgresql/18/bin/pg_config install
If you are using PostgreSQL version 17 or versions below 17, you can use the same compilation commands and just change the version number like this.
If you are using system installed postgres version 17, the compilation commands look like this.
make USE_PGXS=1 PG_CONFIG=/usr/lib/postgresql/17/bin/pg_config
sudo make USE_PGXS=1 PG_CONFIG=/usr/lib/postgresql/17/bin/pg_config install
Now log in to the psql terminal.
sudo su postgres
psql
Create the extension.
create extension pg_dbms_lock ;
Check the functionalities of this extension.
\dx+ pg_dbms_lock
Result:
Objects in extension "pg_dbms_lock"
Object description
---------------------------------------------------------------------------------
function dbms_lock.allocate_unique(character varying,character varying,integer)
function dbms_lock.release(character varying)
function dbms_lock.release(integer)
function dbms_lock.request(character varying,integer,integer,boolean)
function dbms_lock.request(integer,integer,integer,boolean)
function dbms_lock.sleep(double precision)
table dbms_lock.dbms_lock_allocated
type dbms_lock.dbms_lock_allocated
type dbms_lock.dbms_lock_allocated[]
(9 rows)
We have a table named dbms_lock_allocated, and it shows the metadata related to locks created by this extension.
select * from dbms_lock.dbms_lock_allocated;
Result:
name | lockid | expiration
------+--------+------------
(0 rows)
Now explore the functionalities provided by this extension.
1. dbms_lock.allocate_unique
We can inspect the metadata of this function by using these short commands.
\df+ dbms_lock.allocate_unique
Result:
-[ RECORD 1 ]-------+-------------------------------------------------------------------------------------------------------------
Schema | dbms_lock
Name | allocate_unique
Result data type |
Argument data types | IN lockname character varying, INOUT lockhandle character varying, IN expiration_secs integer DEFAULT 864000
Type | proc
Volatility | volatile
Parallel | unsafe
Owner | postgres
Security | definer
Leakproof? | no
Access privileges | postgres=X/postgres +
| =X/postgres
Language | plpgsql
Internal name |
Description | Allocates a unique lock identifier (in the range of 1073741824 to 1999999999) given a lock name.
We can use this function to generate a unique ID for a lock name.
DO $$
DECLARE
lock_handle varchar;
BEGIN
CALL dbms_lock.allocate_unique(
'employee_import_lock',
lock_handle
);
RAISE NOTICE 'Lock Handle = %', lock_handle;
END $$;
Result:
NOTICE: Lock Handle = 1073741824
Now we can see a unique lock identifier. Now check the records from the table named dbms_lock.dbms_lock_allocated.
select * from dbms_lock.dbms_lock_allocated ;
Result:
-[ RECORD 1 ]--------------------
name | employee_import_lock
lockid | 1073741824
expiration | 2026-07-16 20:34:53
Now we can see the name of the lock, the unique identifier, and its related expiration date.
In PostgreSQL, we can see that the metadata of locks exists in all sessions from the pg catalogue named pg_locks.
select * from pg_locks ;
Result:
locktype | database | relation | page | tuple | virtualxid | transactionid | classid | objid | objsubid | virtualtransaction | pid | mode | granted | fastpath | waitstart
------------+----------+----------+------+-------+------------+---------------+---------+-------+----------+--------------------+------+-----------------+---------+----------+-----------
relation | 5 | 12073 | | | | | | | | 3/82 | 5684 | AccessShareLock | t | t |
virtualxid | | | | | 3/82 | | | | | 3/82 | 5684 | ExclusiveLock | t | t |
(2 rows)
Here we can see that there is no new lock created. We only generated the unique lock identifier.
2. dbms_lock.request
Checking the metadata like this.
\df+ dbms_lock.request
Result:
List of functions
-[ RECORD 1 ]-------+------------------------------------------------------------------------------------------------------------------------------------------
Schema | dbms_lock
Name | request
Result data type | integer
Argument data types | id integer, lockmode integer DEFAULT 6, timeout integer DEFAULT 32767, release_on_commit boolean DEFAULT false
Type | func
Volatility | volatile
Parallel | unsafe
Owner | postgres
Security | invoker
Leakproof? | no
Access privileges | postgres=X/postgres +
| =X/postgres
Language | plpgsql
Internal name |
Description | Acquire a lock in the mode specified by the lockmode parameter. Note that only Exclusive (X_MODE) and Shared (S_MODE) mode are supported.
-[ RECORD 2 ]-------+------------------------------------------------------------------------------------------------------------------------------------------
Schema | dbms_lock
Name | request
Result data type | integer
Argument data types | lockhandle character varying, lockmode integer DEFAULT 6, timeout integer DEFAULT 32767, release_on_commit boolean DEFAULT false
Type | func
Volatility | volatile
Parallel | unsafe
Owner | postgres
Security | invoker
Leakproof? | no
Access privileges | =X/postgres +
| postgres=X/postgres
Language | plpgsql
Internal name |
Description |
Here we can see two functions with different parameters. The only difference is that the first one’s first parameter is the unique lock identifier and the second function's first argument is the name of the lock.
Now let’s try to create a lock using the request function provided by the pg_dbms_lock extension.
DO $$
DECLARE
handle varchar;
result int;
BEGIN
CALL dbms_lock.allocate_unique(
'employee_import_lock',
handle
);
result := dbms_lock.request(
handle,
6,
10,
false
);
RAISE NOTICE 'Result = %', result;
END $$;
Now check the metadata from the catalog named pg_locks.
select * from pg_locks ;
Result:
locktype | database | relation | page | tuple | virtualxid | transactionid | classid | objid | objsubid | virtualtransaction | pid | mode | granted | fastpath | waitstart
------------+----------+----------+------+-------+------------+---------------+---------+------------+----------+--------------------+------+-----------------+---------+----------+-----------
relation | 5 | 12073 | | | | | | | | 3/84 | 5684 | AccessShareLock | t | t |
virtualxid | | | | | 3/84 | | | | | 3/84 | 5684 | ExclusiveLock | t | t |
advisory | 5 | | | | | | 0 | 1073741824 | 1 | 3/84 | 5684 | ExclusiveLock | t | f |
(3 rows)
Now we can see that a new advisory lock is created through the functionalities of the pg_dbms_lock extension.
Here, we combined these two functions of the pg_dbms_lock extension, named allocate_unique() and request() to generate a unique lock identifier, and we are passing the generated unique lock identifier to request(), and it creates an advisory lock based on this lock identifier.
3. dbms_lock.sleep()
Checking the metadata of this sleep().
\df+ dbms_lock.sleep()
Result:
List of functions
-[ RECORD 1 ]-------+---------------------------------------------------
Schema | dbms_lock
Name | sleep
Result data type |
Argument data types | IN seconds double precision
Type | proc
Volatility | volatile
Parallel | unsafe
Owner | postgres
Security | invoker
Leakproof? | no
Access privileges | postgres=X/postgres +
| =X/postgres
Language | plpgsql
Internal name |
Description | Amount of seconds, to suspend the current session.
Example:
CALL dbms_lock.sleep(5);
In the source code of pg_dbms_lock extension, you can find this code part.
----
-- DBMS_LOCK.SLEEP
----
CREATE OR REPLACE PROCEDURE dbms_lock.sleep( seconds double precision )
LANGUAGE PLPGSQL
AS $$
BEGIN
PERFORM pg_sleep(seconds);
END;
$$;
Here, this sleep procedure is actually doing the exact same thing as pg_sleep. It just executes pg_sleep inside this procedure.
4. dbms_lock.release()
Checking the metadata.
\df+ dbms_lock.release()
Result:
List of functions
-[ RECORD 1 ]-------+-------------------------------------
Schema | dbms_lock
Name | release
Result data type | integer
Argument data types | id integer
Type | func
Volatility | volatile
Parallel | unsafe
Owner | postgres
Security | invoker
Leakproof? | no
Access privileges | postgres=X/postgres +
| =X/postgres
Language | plpgsql
Internal name |
Description | Releases a previously acquired lock.
-[ RECORD 2 ]-------+-------------------------------------
Schema | dbms_lock
Name | release
Result data type | integer
Argument data types | lockhandle character varying
Type | func
Volatility | volatile
Parallel | unsafe
Owner | postgres
Security | invoker
Leakproof? | no
Access privileges | =X/postgres +
| postgres=X/postgres
Language | plpgsql
Internal name |
Description |
The only difference between these two functions is that one is passing a unique lock identifier and the other is the custom name of the lock.
The pg_sleep() is mainly used to hold the query execution of the current session.
select * from dbms_lock.release('1073741824');Result:
release
---------
0
(1 row)
Now check the pg_locks catalogue to see if the advisory lock is released or not.
select * from pg_locks;
Result:
locktype | database | relation | page | tuple | virtualxid | transactionid | classid | objid | objsubid | virtualtransaction | pid | mode | granted | fastpath | waitstart
------------+----------+----------+------+-------+------------+---------------+---------+-------+----------+--------------------+--------+-----------------+---------+----------+-----------
relation | 53522 | 12073 | | | | | | | | 6/51 | 122846 | AccessShareLock | t | t |
virtualxid | | | | | 6/51 | | | | | 6/51 | 122846 | ExclusiveLock | t | t |
(2 rows)
You can see the code part of dbms_lock.release(), and they also use the advisory locks related functions in the backend.
IF is_shared THEN
SELECT pg_advisory_unlock_shared(id) INTO ret;
ELSE
SELECT pg_advisory_unlock(id) INTO ret;
Actually, the pg_advisory locks are in two types
Shared advisory locks - multiple sessions can hold the locks.
Exclusive advisory locks - only one session can hold the advisory lock at a time.
By using this extension, the Oracle applications that use the DBMS_LOCK package can be migrated to postgres without rewriting their locking logic. But under the hood, it uses the postgres advanced features like advisory locks and pg_sleep(). But this extension solves a main bottleneck during the migration from Oracle to PostgreSQL.