Advisory Locks

Advisory locks are a unique feature of PostgreSQL that allows applications to define their own locking semantics. Unlike row-level or table-level locks, advisory locks have no inherent meaning to the database; they are simply a mechanism for application-level synchronization.

PormG provides high-level support for advisory locks through the with_advisory_lock function.

Why use Advisory Locks?

In a distributed environment (especially with the Async-First architecture of PormG), you might need to ensure that only one task or one process is performing a specific action at a time.

Common use cases include:

Generating unique report files.
Synchronizing access to external non-SQL APIs.
Preventing race conditions in complex logic that spans multiple tables but doesn't have a single "parent" row to lock.

Usage

The with_advisory_lock function requires a connection key (pointing to a PostgreSQL database) and a key string.

import PormG.models as M

# Example: Ensuring only one task updates a specific Driver's statistics
driver_id = 1
lock_key = "driver_update_$(driver_id)"

PormG.with_advisory_lock("db_2", lock_key; wait=true, timeout_ms=10000) do
  # Critical section code here
  # While inside this block, no other process using this lock_key 
  # can enter its own with_advisory_lock block.
  
  driver = M.Driver.objects.filter("driverid" => driver_id) |> DataFrame
  @info "Updating stats for $(driver[1, :surname])"
  sleep(2) # Simulate work
end

Configuration and Strategies

Waiting Strategies

When a lock is already held by another session, you can choose how PormG should behave:

Non-blocking (wait=false): Immediately throws an error if the lock cannot be acquired.
Client Polling (strategy=:poll): (Default) PormG will try to acquire the lock, wait for a few milliseconds, and try again until the timeout_ms is reached.
Server Blocking (strategy=:block): PormG tells PostgreSQL to block the connection until the lock is granted. This is more efficient as it reduces network traffic, but it ties up a database connection from the pool.

Timeouts

The timeout_ms parameter ensures your application doesn't hang indefinitely.

In :poll strategy, the timeout is managed by Julia.
In :block strategy, PormG temporarily sets the PostgreSQL statement_timeout for that specific acquisition.

Implementation Details

PostgreSQL: Implementation uses pg_try_advisory_lock (non-blocking) or pg_advisory_lock (blocking).
SQLite: Since SQLite does not support advisory locks, with_advisory_lock is a no-op (it simply executes the function without any locking). This allows you to write database-agnostic code that behaves correctly on production PostgreSQL while still running on SQLite for simple tests.
Async Safety: with_advisory_lock uses LibPQ.async_execute and fetch() to ensure that the Julia task yields while waiting for the database, keeping the event loop unblocked.

API Reference

PormG.AdvisoryLock.with_advisory_lock — Function

with_advisory_lock(f::Function, pool::PormGPostgres, key::AbstractString; wait::Bool=false, timeout_ms::Int=5_000, strategy::Symbol=:poll)

Execute a function f while holding a PostgreSQL session-level advisory lock identified by key.

Advisory locks are an application-level locking mechanism provided by PostgreSQL. They are useful for ensuring exclusivity for tasks that don't map directly to a database row, such as synchronizing external API calls or preventing concurrent expensive calculations.

Arguments

f::Function: The function to execute while holding the lock.
pool::PormGPostgres: The PostgreSQL connection pool.
key::AbstractString: A unique string identifying the lock. It will be hashed to a 64-bit integer.

Keywords

wait::Bool=false: If true, the function will wait until the lock becomes available or the timeout is reached. If false, it throws an error immediately if the lock is already held.
timeout_ms::Int=5_000: Maximum time to wait for the lock (in milliseconds).
strategy::Symbol=:poll: The waiting strategy:
- :poll: (Default) Periodically retries lock acquisition from the Julia client. Safe and recommended for most cases.
- :block: Uses PostgreSQL's server-side blocking mechanism. Efficient but holds a connection and uses statement_timeout.
interval_ms::Int=100: Retry interval for the :poll strategy.

Examples

# Lock around a critical update for a specific constructor
PormG.with_advisory_lock(M.Constructor.objects.object.model.connect_key, "update_constructor_1") do
    # This block is protected by the lock "update_constructor_1"
    # Perform complex logic here...
    @info "Exclusive access granted"
end

source