pglock

pglock : Lightweight distributed lock service inside PostgreSQL

Overview

Packages

Source

pig build pkg pglock;		# build rpm/deb

Install

Make sure PGDG and PIGSTY repo available:

pig repo add pgsql -u   # add both repo and update cache

Install this extension with pig:

pig install pglock;		# install via package name, for the active PG version

pig install pglock -v 18;   # install for PG 18
pig install pglock -v 17;   # install for PG 17
pig install pglock -v 16;   # install for PG 16
pig install pglock -v 15;   # install for PG 15
pig install pglock -v 14;   # install for PG 14

Create this extension with:

CREATE EXTENSION pglock CASCADE; -- requires pg_cron

Usage

Syntax:

SELECT pglock.lock('b3d8a762-3a0e-495b-b6a1-dc8609839f7b', 'users');
SELECT pglock.unlock('b3d8a762-3a0e-495b-b6a1-dc8609839f7b', 'users');
SELECT pglock.ttl();

Source: README

pglock is a lightweight distributed lock service implemented inside PostgreSQL. It stores locks in a table and supports TTL-based expiration for stale locks.

Basic Functions

The README documents four core functions:

• pglock.lock(id, resource) to acquire a lock
• pglock.unlock(id, resource) to release a lock
• pglock.ttl() to expire stale locks
• pglock.set_serializable() to switch to serializable isolation

Acquire a lock:

SELECT pglock.lock('worker-1', 'users');

Release it:

SELECT pglock.unlock('worker-1', 'users');

Isolation

The upstream docs recommend serializable isolation for correctness under concurrency:

SELECT pglock.set_serializable();

or:

BEGIN ISOLATION LEVEL SERIALIZABLE;
SELECT pglock.lock('my-id', 'my-resource');
SELECT pglock.unlock('my-id', 'my-resource');
COMMIT;

TTL Expiration

Locks have a configurable TTL with a documented default of 5 minutes. pglock.ttl() unlocks records whose updated_at is older than the TTL:

SELECT pglock.ttl();

If pg_cron is installed, the README says a cron job can run pglock.ttl() every minute.

Schema

The lock table is pglock.locks with columns:

• id
• resource
• locked
• ttl
• created_at
• updated_at

The primary key is (id, resource).