Postgres Advisory Locks

This post is part of the series Postgres for Everything.

Nowadays, we typically develop stateless applications, making it easier to scale them horizontally. However, locking is an essential tool to prevent race conditions in software development. In applications with multiple instances, programming language-level locks are insufficient because they only work locally within an instance. A centralized locking mechanism, valid across all instances, is required.

Redis

SETNX

You can use SETNX to implement a simple lock. SETNX sets a value for a key only if the key does not already exist.

The key acts as the lock. Initially, it does not exist, so the fastest client can acquire it by setting a value. Other clients must wait until the key no longer exists to acquire the lock. Once the job is complete, the client releases the lock by deleting the key, giving other clients a chance to acquire it.

1

SETNX key value

SETNX does not allow setting an expiration time atomically when the key is created. Adding an expiration time is crucial to mitigate deadlocks and prevent crashed clients from holding locks indefinitely. You can achieve this using SET:

1

SET key value NX EX 10

This approach works well when your Redis deployment consists of a single master node. However, issues arise in a master-replica setup. If the master crashes before a key is replicated to its replicas, the promoted replica might allow a new client to SETNX on the same key, resulting in two clients holding the same lock.

Redlock

To overcome the limitations of SETNX, Redis offers Redlock. Redlock works on multi-master clusters, improving the availability of the locking mechanism.

In a cluster with N nodes, Redlock requires locking N/2 + 1 nodes, tolerating up to N/2 - 1 node failures. For example, in a 5-node cluster, Redlock remains functional even if 2 nodes fail.

However, Redlock is not guaranteed to be safe in all scenarios, as discussed in Martin Kleppmann’s blog. Therefore, Redlock might not be suitable for critical systems like banking or financial applications.

Postgres

Redis locks are convenient but may lack the safety required for critical tasks. If you already use Postgres in your project, it can be a great choice for centralized locking.

1
2
3
4
5

CREATE TABLE user_balance (
    name TEXT PRIMARY KEY,       -- Name of the user, serves as the primary key
    balance NUMERIC(15, 2) NOT NULL DEFAULT 0.00 -- Balance with two decimal places
);
INSERT INTO user_balance (name, balance) VALUES ('Alice', 100), ('Bob', 200);

Row-Level Locks

Using the SELECT ... FOR UPDATE syntax, you can acquire an exclusive lock on rows. These locks are automatically released at the end of the transaction. This mechanism is often combined with transactions for atomic updates.

For more details, see PostgreSQL Row-Level Locks.

Demonstration

Deadlock Example

Deadlocks can occur when transactions acquire locks in an inconsistent order. For instance:

To avoid deadlocks, acquire locks in a deterministic order (e.g., by ascending username).

Optimistic Locking

Optimistic locking assumes no concurrent modifications. It checks data integrity at the end of the transaction, rejecting changes if data has been altered. This approach is implemented using a version column, incremented on updates.

Successful Scenario

Failure Scenario

Optimistic locking can also be implemented without a version column by validating data directly in the UPDATE query:

1

UPDATE user_balance SET name = 'Alice Watson' WHERE id = 123 AND name = 'Alice';

Advisory Locks

Row-level locks in PostgreSQL are useful, but what if the resource you need to lock doesn’t correspond to a database row? PostgreSQL provides an alternative: advisory locks. These locks are application-defined and can operate independently of the database schema. Advisory locks can be acquired at two levels: transaction and session.

Transaction-Level Locks

Exclusive Locks:
You can acquire an exclusive lock at the transaction level using the pg_advisory_xact_lock function. This is a blocking function, meaning that if another transaction already holds the lock, the current transaction will wait until the lock becomes available:

1
2
3

BEGIN;
SELECT pg_advisory_xact_lock(123);
COMMIT;

For a non-blocking version, use pg_try_advisory_xact_lock. It returns TRUE if the lock is acquired and FALSE otherwise.

Shared Locks:
The pg_advisory_xact_lock function provides exclusive locks, allowing only one transaction to hold the lock at a time. For shared access, use pg_advisory_xact_lock_shared. This allows multiple transactions to hold the lock simultaneously, making it suitable for read-only tasks. However, while shared locks are held, no exclusive lock can be acquired on the same key.

Lock Release:
All transaction-level advisory locks are automatically released when the transaction ends, whether through COMMIT or ROLLBACK.

Locking Costs:
Advisory locks are lightweight and impose less overhead compared to row-level locks. For example:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17

postgres=# explain analyze select pg_advisory_lock(123);
                                     QUERY PLAN
------------------------------------------------------------------------------------
 Result  (cost=0.00..0.01 rows=1 width=4) (actual time=0.010..0.012 rows=1 loops=1)
 Planning Time: 0.111 ms
 Execution Time: 0.046 ms
(3 rows)

postgres=# explain analyze select * from user_balance where name = 'Alice' for update;
                                                              QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------
 LockRows  (cost=0.15..8.18 rows=1 width=60) (actual time=0.201..0.207 rows=1 loops=1)
   ->  Index Scan using user_balance_pkey on user_balance  (cost=0.15..8.17 rows=1 width=60) (actual time=0.061..0.066 rows=1 loops=1)
         Index Cond: (name = 'Alice'::text)
 Planning Time: 0.218 ms
 Execution Time: 0.258 ms
(5 rows)

Handling Non-Integer Keys

Advisory locks only accept integer keys. To lock on a string key, hash it into an integer first:

1

SELECT pg_advisory_xact_lock(hashtext('my_unique_lock'));

Session-Level Locks

Session-level locks persist across multiple transactions until explicitly released or the session ends. They are less commonly used but can be explored further in the PostgreSQL documentation.

Monitoring Locks

To view active advisory locks, query the pg_locks system catalog:

1

SELECT * FROM pg_locks WHERE locktype = 'advisory';

Conclusion

If Postgres is part of your tech stack, it’s an excellent choice for centralized locking, offering robust transaction management. Redis SETNX is fast but limited to single-node deployments. Redis Redlock provides high availability but lacks safety for critical applications. For highly available and safe distributed locking, consider Etcd, Consul, or ZooKeeper.