Row-level locking (FOR UPDATE, FOR SHARE) in PostgreSQL - Mini Project: Build & Apply

Row-level Locking with FOR UPDATE and FOR SHARE in PostgreSQL

📖 Scenario: You are managing a small online bookstore database. Multiple users can view and buy books at the same time. To avoid conflicts when updating stock quantities, you need to use row-level locking.

🎯 Goal: Build a simple PostgreSQL transaction that selects a book row with a lock using FOR UPDATE and another with FOR SHARE. This will help you understand how to lock rows to prevent conflicts during concurrent updates.

📋 What You'll Learn

Create a table called books with columns id (integer primary key), title (text), and stock (integer).

Insert three specific books into the books table with given ids, titles, and stock values.

Write a query that selects the book with id = 1 using FOR UPDATE locking.

Write a query that selects the book with id = 2 using FOR SHARE locking.

💡 Why This Matters

🌍 Real World

Row-level locking is used in real-world databases to prevent data conflicts when multiple users or applications access and modify the same data concurrently.

💼 Career

Understanding row-level locking is important for database administrators and backend developers to ensure data consistency and avoid race conditions in multi-user environments.

1

Create the books table

Create a table called books with columns id as integer primary key, title as text, and stock as integer.

PostgreSQL

-- Create the books table with id, title, and stock columns
-- Your code here

Need a hint?

Use CREATE TABLE books and define the columns with their types and primary key.

2

Insert three books into the books table

Insert these three rows into the books table exactly: (1, 'Learn SQL', 10), (2, 'PostgreSQL Basics', 5), and (3, 'Advanced Databases', 2).

PostgreSQL

CREATE TABLE books (
    id INTEGER PRIMARY KEY,
    title TEXT,
    stock INTEGER
);
-- Insert the three books below
-- Your code here

Need a hint?

Use a single INSERT INTO books (id, title, stock) VALUES statement with all three rows.

3

Select the book with id = 1 using FOR UPDATE

Write a query to select all columns from books where id = 1 and lock the row using FOR UPDATE.

PostgreSQL

CREATE TABLE books (
    id INTEGER PRIMARY KEY,
    title TEXT,
    stock INTEGER
);

INSERT INTO books (id, title, stock) VALUES
(1, 'Learn SQL', 10),
(2, 'PostgreSQL Basics', 5),
(3, 'Advanced Databases', 2);

-- Select the book with id = 1 using FOR UPDATE
-- Your code here

Need a hint?

Use SELECT * FROM books WHERE id = 1 FOR UPDATE; to lock the row for update.

4

Select the book with id = 2 using FOR SHARE

Write a query to select all columns from books where id = 2 and lock the row using FOR SHARE.

PostgreSQL

CREATE TABLE books (
    id INTEGER PRIMARY KEY,
    title TEXT,
    stock INTEGER
);

INSERT INTO books (id, title, stock) VALUES
(1, 'Learn SQL', 10),
(2, 'PostgreSQL Basics', 5),
(3, 'Advanced Databases', 2);

SELECT * FROM books WHERE id = 1 FOR UPDATE;

-- Select the book with id = 2 using FOR SHARE
-- Your code here

Need a hint?

Use SELECT * FROM books WHERE id = 2 FOR SHARE; to lock the row for shared access.