Advisory locks in PostgreSQL - Mini Project: Build & Apply

Using Advisory Locks in PostgreSQL

📖 Scenario: You are managing a PostgreSQL database where multiple processes might try to update the same resource at the same time. To avoid conflicts, you want to use advisory locks to control access.

🎯 Goal: Learn how to create and use advisory locks in PostgreSQL to safely manage concurrent access to a shared resource.

📋 What You'll Learn

Create a table to simulate a shared resource

Define a lock key to use for advisory locking

Acquire an advisory lock before updating the resource

Release the advisory lock after the update

💡 Why This Matters

🌍 Real World

Advisory locks help prevent multiple processes from changing the same data at the same time, avoiding data corruption or conflicts.

💼 Career

Database administrators and backend developers use advisory locks to manage concurrency safely in multi-user environments.

1

Create a table to represent a shared resource

Create a table called shared_resource with two columns: id as an integer primary key and value as an integer. Insert one row with id = 1 and value = 100.

PostgreSQL

-- Create the shared_resource table and insert one row
-- Your code here

Need a hint?

Use CREATE TABLE to define the table and INSERT INTO to add the initial row.

2

Define a lock key for advisory locking

Create a variable or note a constant integer lock_key with the value 12345 to use as the advisory lock key.

PostgreSQL

CREATE TABLE shared_resource (
    id INTEGER PRIMARY KEY,
    value INTEGER
);

INSERT INTO shared_resource (id, value) VALUES (1, 100);

-- Define the lock key
-- Your code here

Need a hint?

Use the psql command \set to define a variable for the lock key.

3

Acquire the advisory lock before updating

Use the function pg_advisory_lock with the lock_key to acquire the advisory lock before updating the value in shared_resource. Increase the value by 10 where id = 1.

PostgreSQL

CREATE TABLE shared_resource (
    id INTEGER PRIMARY KEY,
    value INTEGER
);

INSERT INTO shared_resource (id, value) VALUES (1, 100);

-- Define the lock key
\set lock_key 12345

-- Acquire advisory lock and update value
-- Your code here

Need a hint?

Use SELECT pg_advisory_lock(:lock_key) to lock, then update the table.

4

Release the advisory lock after update

Use the function pg_advisory_unlock with the lock_key to release the advisory lock after updating the shared_resource.

PostgreSQL

CREATE TABLE shared_resource (
    id INTEGER PRIMARY KEY,
    value INTEGER
);

INSERT INTO shared_resource (id, value) VALUES (1, 100);

-- Define the lock key
\set lock_key 12345

-- Acquire advisory lock and update value
SELECT pg_advisory_lock(:lock_key);

UPDATE shared_resource SET value = value + 10 WHERE id = 1;

-- Release advisory lock
-- Your code here

Need a hint?

Use SELECT pg_advisory_unlock(:lock_key) to release the lock.