Distributed Locking With PostgreSQL

Learn how to use Postgres native feature to coordinate work across several processes, acting as a mutex.

Skip To Implementation

What Are Advisory Locks?

Distinct from traditional row-level locks, Advisory Locks offer a unique approach to managing concurrency synchronization in your PostgreSQL database. Advisory locks focus on facilitating coordination and synchronization across different components of an application. In our case, we’ll be discussing their application in a stateless Go binary that can be deployed multiple times, all connected to the same underlying database.

Advisory locks are an excellent choice for concurrency synchronization for two key reasons:

1. User-Defined Lock Identification: Unlike traditional locks that are tied to specific data objects, advisory locks utilize a user-defined 64-bit integer to identify the lock. This characteristic empowers you to create multiple locks for various purposes, enabling fine-grained coordination between different parts of your application.

2. Session Scope: An essential feature of advisory locks is their session-level scope. This means that if a node drops its session connection to the database, the associated advisory lock is automatically released. This behavior ensures that locks are not held indefinitely, preventing potential resource contention and improving system resilience.

By leveraging these attributes, advisory locks offer a lightweight yet robust mechanism for managing concurrency in scenarios where strict data isolation is not the primary concern. Their session-based nature and flexibility make them particularly well-suited for orchestrating tasks across multiple instances of a stateless application.

Implementing Advisory Locks in Go

In it’s most simple form, this is what the locking mechanism looks like.

To obtain a lock

SELECT pg_try_advisory_lock(1234)

And to release that same lock.

SELECT pg_advisory_unlock(1234)

In Go, we can utilize the github.com/lib/pq library to incorporate this behavior into our applications.

First, we’ll define a constant that represents the lock we’ll be trying to obtain. It’s important that any application you want to share this lock must use this same integer.

const WorkerLockID = 8675309 // ~(-1) Lock ID

func main() {
	client, err := sql.Open("postgres", dbString) // ~(-1) Postgres Connection
	if err != nil {
		panic(err)
	}

    for {
        ok := tryObtainLock(client, WorkerLockID)
        if !ok {
            // If we can't obtain a lock, we sleep and try again
            // later. This can be adjusted based on how often your
            // task needs to be completed.
            time.Sleep(5 * time.Second)
            continue
        }

        // Do Some Work Work
        someBackgroundTaskFunction()

        releaseLock(client, WorkerLockID)
    }
}

func releaseLock(client *sql.DB, lockID int) error {
	_, err := client.Exec(fmt.Sprintf("SELECT pg_advisory_unlock(%d)", lockID))
	return err
}

func tryObtainLock(client *sql.DB, lockID int) (bool) {
	var lockObtained bool
	err := client.
        QueryRow(
            fmt.Sprintf(`SELECT pg_try_advisory_lock(%d)`, lockID)
        ).
		Scan(&lockObtained)
	if err != nil {
		return false
	}

	return lockObtained
}

If you’d like to see an example of multiple goroutines working against the same PostgreSQL database to coordinate work, this example repository will show you how I’ve used this mechanism to coordinate the change of a global variable without race conditions.

Use Cases and Best Practices

One of the best use cases for Advisory Locks in applications is to coordinate the executaion of short lived background tasks like.

• Removing expired API Tokens from the database
• Executing scheduled tasks stored in the database like sending Webhooks, Emails, or Text messages.
• Coordinate cache invalidation
• Coordinate search indexing

Whatever you choose to use advisory locks for, it’s important to keep the following in mind

1. Keep Lock Durations Short: Advisory locks should be held for the minimum time necessary to complete the associated task. Long-held locks can lead to performance bottlenecks and hinder concurrency. Release locks as soon as their purpose is fulfilled to allow other sessions to acquire them.
2. Avoid Nested Locking: While it’s possible to acquire multiple advisory locks within the same session, it’s crucial to avoid nested locking scenarios that might lead to deadlocks. Plan your locking strategy carefully to prevent circular dependencies.
3. Monitor and Tune: Keep an eye on your application’s performance and behavior when using advisory locks. Use PostgreSQL’s monitoring tools to identify contention issues or potential bottlenecks and adjust your locking strategy if needed.

Conclusion

Advisory locks provide a nuanced method for managing concurrency in PostgreSQL. As you fine-tune your application’s structure and concurrency management strategies, considering the incorporation of advisory locks can yield valuable insights. By thoughtfully evaluating your application’s demands and leveraging the practical examples and best practices discussed in this article, you can tap into the potential of advisory locks to refine synchronization, optimize performance, and fortify your applications.