pg_dbms_lock

PostgreSQL extension to manages advisory locks in a way compatible to Oracle DBMS_LOCK package.

This extension uses PostgreSQL advisory locks to emulate the same behavior following the lock mode (exclusive or shared), the timeout and the on commit release settings.

More information about the Oracle DBMS_LOCK package can be found here

• Description
• Installation
• Manage the extension
• Configuration
• Procedures
  • ALLOCATE_UNIQUE
  • REQUEST
  • RELEASE
  • SLEEP
• Authors
• License

Description

This PostgreSQL extension provided compatibility with the DBMS_LOCK Oracle package except the DBMS_LOCK.CONVERT() function and some unsupported lock modes. The following stored procedures are implemented:

• ALLOCATE_UNIQUE() Allocates a unique lock ID to a named lock.
• REQUEST() Requests a lock of a specific mode.
• RELEASE() Releases a lock.
• SLEEP() Puts a procedure to sleep for a specific time.

For instance, user locks can be used to do the following:

• Provide exclusive access to an external device or service (like a printer).
• Coordinate or synchronize parallelized applications.
• Disable or enable execution of programs at specific times.
• Detect whether a session has ended a transaction using COMMIT or ROLLBACK.

Installation

To be able to run this extension, your PostgreSQL version must support extensions (>= 9.1) and the pg_background extension must be created in each database where you planned to use pg_dbms_lock.

To install the extension execute

    make
    sudo make install

Test of the extension can be done using:

    make installcheck

Advisory locks are stored in a shared memory pool whose size is defined by the configuration variables max_locks_per_transaction and max_connections. Care must be taken to adjust this memory or the server will be unable to grant any locks at all. The value of max_locks_per_transaction should be increase in consequence.

Manage the extension

Each database that needs to use pg_dbms_lock must creates the extension:

    psql -d mydb -c "CREATE EXTENSION pg_dbms_lock"

To upgrade to a new version execute:

    psql -d mydb -c 'ALTER EXTENSION pg_dbms_lock UPDATE TO "1.1.0"'

If you doesn't have the privileges to create an extension and that the pg_background extension is available, you can just import the extension file into the database, for example:

psql -d mydb -f sql/pg_dbms_lock--1.0.0.sql

This is especially useful for database in DBaas cloud services, supposing that pg_background is supported. To upgrade just import the extension upgrade files using psql.

Procedures

ALLOCATE_UNIQUE

Allocates a unique lock identifier (in the range of 1073741824 to 1999999999) given a lock name. Lock identifiers are used to enable applications to coordinate their use of locks. This is provided because it may be easier for applications to coordinate their use of locks based on lock names rather than lock numbers.

If you choose to identify locks by name, you can use ALLOCATE_UNIQUE() to generate a unique lock identification number for these named locks.

The first session to call ALLOCATE_UNIQUE() with a new lock name causes a unique lock ID to be generated and stored in the dbms_lock.dbms_lock_allocated table. Subsequent calls (usually by other sessions) return the lock ID previously generated.

A lock name is associated with the returned lock ID for at least expiration_secs (defaults to 10 days) past the last call to ALLOCATE_UNIQUE() with the given lock name. After this time, the row in the dbms_lock.dbms_lock_allocated table for this lock name may be deleted in order to recover space. ALLOCATE_UNIQUE() performs a commit.

Syntax:

dbms_lock.allocate_unique (
   lockname         IN    varchar,
   lockhandle       INOUT varchar,
   expiration_secs  IN    integer DEFAULT 864000);

Parameters:

• lockname: Name of the lock for which you want to generate a unique ID.
• lockhandle: Returns the handle to the lock ID generated by ALLOCATE_UNIQUE(). You can use this handle in subsequent calls to REQUEST() and RELEASE(). All sessions using a lock handle returned by ALLOCATE_UNIQUE() with the same lock name are referring to the same lock. Therefore, do not pass lock handles from one session to another, only use the lock name to get the lock handle.
• expiration_secs: Number of seconds to wait after the last ALLOCATE_UNIQUE() has been performed on a given lock, before permitting that lock to be deleted from the DBMS_LOCK_ALLOCATED table. The default waiting period is 10 days. You should not delete locks from this table. Subsequent calls to ALLOCATE_UNIQUE() may delete expired locks to recover space.

Example:

DO $$
DECLARE
    printer_lockhandle varchar;
BEGIN
    CALL dbms_lock.allocate_unique (lockname => 'printer_lock', lockhandle => printer_lockhandle);
    IF ( printer_lockhandle IS NULL ) THEN
        RAISE EXCEPTION 'DBMS_LOCK.ALLOCATE_UNIQUE() FAIL';
    END IF;
END;
$$;

Complete example:

DO $$
DECLARE
    lock_res int;
    printer_lockhandle varchar;
    DBMS_LOCK_X_MODE int := 6;
    rec record;
BEGIN
    CALL dbms_lock.allocate_unique (lockname => 'printer_lock', lockhandle => printer_lockhandle);
    IF ( printer_lockhandle IS NULL ) THEN
        RAISE EXCEPTION 'DBMS_LOCK.ALLOCATE_UNIQUE() FAIL';
    END IF;

    RAISE NOTICE 'Found lockhandle => %', printer_lockhandle;

    lock_res := dbms_lock.request(      lockhandle => printer_lockhandle,
                                        lockmode => DBMS_LOCK_X_MODE,
                                        timeout => 5,
                                        release_on_commit => false);

    IF ( lock_res <> 0 ) THEN
        RAISE EXCEPTION 'DBMS_LOCK.REQUEST() FAIL: %', lock_res;
    END IF;

    FOR rec IN SELECT objid, mode FROM pg_locks WHERE objid IS NOT NULL
    LOOP
        RAISE NOTICE 'objid => % | mode => %', rec.objid, rec.mode;
    END LOOP;

    lock_res := dbms_lock.release(lockhandle => printer_lockhandle);

    IF ( lock_res <> 0 ) THEN
        RAISE EXCEPTION 'DBMS_LOCK.RELEASE() FAIL: %', lock_res;
    END IF;

END;
$$;

SELECT objid, mode FROM pg_locks WHERE objid IS NOT NULL AND locktype = 'advisory';

SELECT name, lockid, expiration FROM dbms_lock.dbms_lock_allocated;

REQUEST

This function requests a lock with a given mode. REQUEST() is an overloaded function that accepts either a user-defined lock identifier, or the lock handle returned by the ALLOCATE_UNIQUE() procedure.

Syntax:

dbms_lock.request(
   id                 IN  integer
   lockmode           IN  integer DEFAULT 6,
   timeout            IN  integer DEFAULT 32767,
   release_on_commit  IN  boolean DEFAULT false
)
  RETURN integer;

dbms_lock.request(
   lockhandle         IN  varchar,
   lockmode           IN  integer DEFAULT 6,
   timeout            IN  integer DEFAULT 32767,
   release_on_commit  IN  boolean DEFAULT false
)
  RETURN integer;

Parameters:

• id or lockhandle: User assigned lock identifier, from 0 to 1073741823, or the lock handle, returned by ALLOCATE_UNIQUE().
• lockmode: Mode that you are requesting for the lock. This PostgreSQL implementation only supports the Exclusive mode (6) and the Shared mode (4).
• timeout: Number of seconds to continue trying to grant the lock. If the lock cannot be granted within this time period, then the call returns a value of 1 (timeout). Default is to wait 32767 seconds.
• release_on_commit: Set this parameter to TRUE to release the lock on commit or rollback. Otherwise, the lock is held until it is explicitly released or until the end of the session.

Return Values:

• 0: Success
• 1: Timeout
• 3: Parameter error
• 4: Already own lock specified by id or lockhandle
• 5: Illegal lock handle

Example:

DO $$
DECLARE
    lock_res int;
    DBMS_LOCK_X_MODE int := 6;
BEGIN
    lock_res := DBMS_LOCK.REQUEST( 123, DBMS_LOCK_X_MODE, 300, FALSE );
    IF ( lock_res <> 0 ) THEN
        RAISE EXCEPTION 'DBMS_LOCK.REQUEST() FAIL: %', lock_res;
    END IF;
END;
$$;

RELEASE

This function explicitly releases a lock previously acquired using the REQUEST() function. Locks are automatically released at the end of a session. RELEASE() is an overloaded function that accepts either a user-defined lock identifier, or the lock handle returned by the ALLOCATE_UNIQUE() procedure.

Syntax:

dbms_lock.release (
   id         IN integer)
  RETURN integer;

dbms_lock.release (
   lockhandle IN varchar)
  RETURN integer;

Parameters:

• id or lockhandle: User assigned lock identifier, from 0 to 1073741823, or the lock handle, returned by ALLOCATE_UNIQUE().

Return Values:

• 0: Success
• 3: Parameter error
• 4: Do not own lock specified by id or lockhandle
• 5: Illegal lock handle

Example:

DO $$
DECLARE
    lock_res int;
BEGIN
    -- release lock
    lock_res := DBMS_LOCK.RELEASE( 123 );
    IF ( lock_res <> 0 ) THEN
        RAISE EXCEPTION 'DBMS_LOCK.RELEASE() FAIL: %', lock_res;
    END IF;
END;
$$;

SLEEP

This procedure suspends the session for a given period of time.

Syntax:

DBMS_LOCK.SLEEP (
   seconds  IN double precision);

Parameters:

• seconds: Amount of time, in seconds, to suspend the session. The smallest increment can be entered in hundredths of a second; for example, 1.95 is a legal time value.

Example:

CALL DBMS_LOCK.SLEEP(0.70);

Authors

• Gilles Darold
• Akhil Reddy Banappagari

License

This extension is free software distributed under the PostgreSQL License.

Copyright (c) 2023 HexaCluster Corp.