Advisory Locks

Advisory locks are a database feature that lets applications define their own locking semantics: PostgreSQL's advisory locks, and MySQL's named locks (GET_LOCK). 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 or MySQL database) and a key string.

# Load the configuration, then your models (preferred: hot-reload-friendly, self-registering)
PormG.Configuration.load("db"; env = "dev")
PormG.@import_models "db/models.jl" models
import .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:

1. Non-blocking (wait=false): Immediately raises OperationalError if the lock cannot be acquired. The same type is raised when a :poll or :block acquisition exceeds timeout_ms.
2. 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.
3. Server Blocking (strategy=:block): PormG tells the server (PostgreSQL, or MySQL through GET_LOCK's timeout) 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 lock_timeout for that specific acquisition and restores it when the call returns. lock_timeout bounds only the wait for the lock, so a statement_timeout you set on the session is left alone. When the wait runs out, the server reports it as a lock timeout, and PormG raises the same OperationalError as :poll, naming the session that holds the lock.
• If the session's statement_timeout is shorter than timeout_ms, it ends the :block wait first. That surfaces as a query-canceled OperationalError (SQLSTATE 57014) without the lock-holder detail, because PostgreSQL cancelled the statement rather than the lock wait.
• On MySQL, the :block wait is GET_LOCK's own timeout, so nothing is set on the session. It is a SELECT, so a session max_execution_time shorter than timeout_ms ends the wait first, as an OperationalError without the lock-holder detail. MySQL takes whole seconds and rounds a fraction to the nearest one, so PormG rounds timeout_ms up to the next second: the wait is never shorter than asked, and at most a second longer.
• On both, timeout_ms = 0 with strategy = :block waits with no bound.

Implementation Details

• PostgreSQL: Implementation uses pg_try_advisory_lock (non-blocking) or pg_advisory_lock (blocking).
• MySQL: GET_LOCK and RELEASE_LOCK, session-level like PostgreSQL's, so a dropped connection releases the lock. A MySQL lock name is server-wide, where a PostgreSQL advisory lock is scoped to its database, so PormG hashes the database name in with the key: two databases on one server do not block each other, as on PostgreSQL. A contention error names the holder by its MySQL connection id.
• SQLite: with_advisory_lock is a no-op — see the warning below.
• Async Safety: with_advisory_lock runs its lock queries through the backend's async execution (LibPQ.async_execute on PostgreSQL, a task of its own on MySQL) and fetch(), so the Julia task yields while waiting for the database, keeping the event loop unblocked.

Choosing what SQLite does: on_missing_lock

The no-op stays the default — it is what lets one codebase run PostgreSQL or MySQL in production and SQLite in tests. But because what degrades here is a guarantee rather than a query, the SQLite path is not silent, and on_missing_lock lets you pick (#277):

The same call site works on every backend — that is the point of the keyword:

# Default: a real lock on PostgreSQL and MySQL, a warning once per key on SQLite.
PormG.with_advisory_lock(connection_key, "driver_update_$(driver_id)") do
    # rebuild this driver's standings
end

# SQLite gives no protection here and that is acceptable — stay quiet.
PormG.with_advisory_lock(connection_key, "circuit_cache_warm"; on_missing_lock = :ignore) do
    # …
end

# This MUST be exclusive. Refuse to run on a backend that cannot promise it.
PormG.with_advisory_lock(connection_key, "season_points_recalc"; on_missing_lock = :error) do
    # never reached on SQLite — BackendCapabilityError is raised instead
end

On PostgreSQL and MySQL on_missing_lock is accepted and ignored: a real lock is always taken, so there is no missing-lock case to have a policy about. An unrecognised value raises InvalidValueError on every backend, so a typo cannot lie in wait until you happen to run on SQLite.

The warning fires once per distinct lock key, tracked in-process and independently of the logger in use, so a scheduled job taking the same lock in a loop logs one line rather than one per iteration. Tracking is capped at 64 distinct keys — a fair ceiling for a genuinely per-entity key like "driver_update_$(driver_id)" — and the warning that reaches the cap says so rather than going quiet without telling you.

API Reference

with_advisory_lock(f::Function, pool::PormGPostgres, key::AbstractString; wait::Bool=false, timeout_ms::Int=5_000, interval_ms::Int=100, strategy::Symbol=:poll, on_missing_lock::Symbol=:warn)
with_advisory_lock(f::Function, pool::PormGMySQL, key::AbstractString; ...)

# 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