Advisory locks in PostgreSQL are a powerful and lightweight mechanism for managing concurrency in applications. Unlike traditional locks that control access to database objects (e.g., tables or rows), advisory locks are application-defined locks that allow developers to implement custom synchronization logic. They are particularly useful for coordinating actions across multiple sessions or processes in a database-driven application, such as preventing race conditions or ensuring exclusive access to a resource.
In this blog, we’ll explore what advisory locks are, how they work, and how to use them effectively in PostgreSQL. We’ll provide practical examples, best practices, and tips to ensure you can implement advisory locks in your applications with confidence. This guide is designed to be clear, concise, and easy to understand, even for those new to PostgreSQL.
What are Advisory Locks?
Advisory locks are a PostgreSQL feature that allows applications to create custom locks that are not tied to specific database objects. They are called "advisory" because PostgreSQL does not enforce their usage; it’s up to the application to respect and manage these locks. This makes them flexible for scenarios where you need to synchronize access to resources or prevent concurrent operations without locking actual database rows or tables.
Key Characteristics of Advisory Locks
* Application-Managed: Advisory locks are not automatically enforced by PostgreSQL. Your application must explicitly acquire and release them.
* Lightweight: They have minimal overhead compared to row or table locks, making them efficient for concurrency control.
* Session-Based or Transaction-Based: Advisory locks can be held for the duration of a session or a transaction.
* Flexible Keys: Locks are identified by one or two integer keys, allowing for a wide range of unique lock identifiers.
* Non-Blocking Options: You can attempt to acquire a lock without waiting, making it easy to handle contention gracefully.
Advisory locks are commonly used in scenarios like:
* Preventing multiple processes from updating the same resource simultaneously.
* Coordinating background jobs or workers in a distributed system.
* Implementing application-level mutual exclusion (mutex).
Types of Advisory Locks
PostgreSQL provides two types of advisory locks:
1. Session-Level Locks: These locks are held until the session ends or the lock is explicitly released. They are useful for long-running processes.
2. Transaction-Level Locks: These locks are automatically released when the transaction commits or rolls back. They are ideal for short-lived operations.
Additionally, advisory locks can be acquired using:
Single-Key Locks: Identified by a single 64-bit integer.
Two-Key Locks: Identified by a pair of 32-bit integers, useful for categorizing locks (e.g., a class ID and an object ID).
PostgreSQL provides functions to acquire, release, and check advisory locks, which we’ll explore with examples.
Advisory Lock Functions in PostgreSQL
PostgreSQL offers several built-in functions for working with advisory locks. These functions are available in all PostgreSQL versions (since 8.2) and are easy to use. Below are the key functions:
1. Acquiring Locks
* pg_advisory_lock(key bigint): Acquires a session-level lock using a single 64-bit integer key. The function blocks until the lock is acquired.
* pg_advisory_lock(key1 integer, key2 integer): Acquires a session-level lock using two 32-bit integer keys.
* pg_try_advisory_lock(key bigint): Attempts to acquire a session-level lock without blocking. Returns true if successful, false if the lock is already held.
* pg_try_advisory_lock(key1 integer, key2 integer): Non-blocking version for two-key locks.
* pg_advisory_xact_lock(key bigint): Acquires a transaction-level lock using a single key.
* pg_advisory_xact_lock(key1 integer, key2 integer): Transaction-level lock for two keys.
* pg_try_advisory_xact_lock(key bigint): Non-blocking transaction-level lock for a single key.
* pg_try_advisory_xact_lock(key1 integer, key2 integer): Non-blocking transaction-level lock for two keys.
2. Releasing Locks
* pg_advisory_unlock(key bigint): Releases a session-level lock for a single key. Returns true if the lock was held and released, false otherwise.
* pg_advisory_unlock(key1 integer, key2 integer): Releases a session-level lock for two keys.
* pg_advisory_unlock_all(): Releases all session-level advisory locks held by the current session.
3. Checking Locks
* pg_advisory_lock_shared(key bigint) and pg_advisory_lock_shared(key1 integer, key2 integer): Acquires a shared (non-exclusive) advisory lock, allowing multiple sessions to hold the lock simultaneously.
* pg_try_advisory_lock_shared and pg_try_advisory_xact_lock_shared: Non-blocking versions of shared locks.
Practical Examples of Using Advisory Locks
Let’s dive into practical examples to demonstrate how advisory locks can be used in real-world scenarios. We’ll use SQL queries and explain their purpose, ensuring clarity for beginners and experienced developers alike.
Example 1: Preventing Concurrent Updates to a Resource
Suppose you have an application where multiple processes update a customer’s balance in a customer_balance table. To prevent race conditions, you can use an advisory lock to ensure only one process updates a customer’s balance at a time.
Table Structure:
CREATE TABLE customer_balance (
customer_id BIGINT PRIMARY KEY,
balance NUMERIC
);
INSERT INTO customer_balance (customer_id, balance) VALUES (1, 100.00);
SQL with Advisory Lock:
BEGIN;
-- Acquire a transaction-level advisory lock for customer_id = 1
SELECT pg_advisory_xact_lock(1);
-- Update the balance
UPDATE customer_balance
SET balance = balance + 50.00
WHERE customer_id = 1;
COMMIT;
-- Lock is automatically released on COMMIT
Explanation:
* The pg_advisory_xact_lock(1) ensures that only one transaction can update customer_id = 1 at a time. If another session tries to acquire the same lock, it will wait until the first transaction commits or rolls back.
* The lock is automatically released when the transaction ends, making it ideal for short operations.
Testing Concurrency:
Open two PostgreSQL sessions (e.g., using psql) and run the above query in both, one after the other. The second session will wait until the first commits.
Example 2: Non-Blocking Lock Attempt
In some cases, you don’t want a process to wait for a lock. Instead, you want to check if the lock is available and proceed or skip the operation. This is where pg_try_advisory_lock is useful.
SQL Example:
DO $$
BEGIN
-- Try to acquire a session-level lock for key 42
IF pg_try_advisory_lock(42) THEN
RAISE NOTICE 'Lock acquired, performing operation...';
-- Perform your operation here
PERFORM pg_sleep(2); -- Simulate work
-- Release the lock
PERFORM pg_advisory_unlock(42);
RAISE NOTICE 'Lock released';
ELSE
RAISE NOTICE 'Could not acquire lock, skipping operation';
END IF;
END $$;
Explanation:
pg_try_advisory_lock(42) attempts to acquire the lock without blocking. If successful, it returns true, and the operation proceeds. If the lock is already held, it returns false, and the operation is skipped.
The lock is explicitly released with pg_advisory_unlock(42) since this is a session-level lock.
Use Case:
This is useful for background jobs where you want to skip processing if another job is already handling the same resource.
Example 3: Using Two-Key Locks for Resource Categories
Two-key locks are useful when you want to lock resources within a category. For example, in an e-commerce system, you might want to lock orders for a specific warehouse.
SQL Example:
BEGIN;
-- Acquire a transaction-level lock for warehouse_id = 1, order_id = 100
SELECT pg_advisory_xact_lock(1, 100);
-- Process the order
UPDATE orders
SET status = 'processed'
WHERE warehouse_id = 1 AND order_id = 100;
COMMIT;
Explanation:
The lock uses warehouse_id (1) and order_id (100) as a two-key pair, ensuring that only one process can handle a specific order in a specific warehouse.
This approach allows different orders in the same warehouse or orders in different warehouses to be processed concurrently.
Example 4: Shared Advisory Locks for Read-Only Access
Shared advisory locks allow multiple sessions to hold a lock simultaneously, useful for scenarios where read operations should proceed concurrently but write operations need exclusivity.
SQL Example:
BEGIN;
-- Acquire a shared transaction-level lock
SELECT pg_advisory_xact_lock_shared(42);
-- Perform read operation
SELECT * FROM customer_balance WHERE customer_id = 42;
COMMIT;
Explanation:
pg_advisory_xact_lock_shared(42) allows multiple sessions to acquire the shared lock, enabling concurrent reads.
A session attempting to acquire an exclusive lock (pg_advisory_xact_lock(42)) will wait until all shared locks are released.
Conclusion
Advisory locks in PostgreSQL are a versatile tool for managing concurrency in applications. By using functions like pg_advisory_lock, pg_try_advisory_lock, and pg_advisory_xact_lock, you can implement custom synchronization logic to prevent race conditions, coordinate background jobs, or ensure exclusive access to resources. With their lightweight nature and flexibility, advisory locks are ideal for scenarios where traditional database locks are too restrictive.
By following the examples and best practices in this guide, you can confidently integrate advisory locks into your PostgreSQL-based applications. Whether you’re building a simple script or a complex distributed system, advisory locks provide a robust solution for concurrency control. Optimize your database. Learn to use pgbench to accurately benchmark and analyze PostgreSQL query performance for better database efficiency.