Why concurrency control matters in PostgreSQL - See It in Action

Why Concurrency Control Matters in PostgreSQL

📖 Scenario: You are managing a small online bookstore database where multiple users can buy books at the same time. To keep the stock count accurate, you need to understand how concurrency control helps prevent errors when many people update the database simultaneously.

🎯 Goal: Build a simple PostgreSQL setup that shows how to track book stock and why concurrency control is important to keep the data correct when multiple updates happen at once.

📋 What You'll Learn

Create a table called books with columns id, title, and stock

Insert initial data for three books with exact stock values

Set a variable to simulate a stock threshold

Write a query to select books with stock below the threshold

Add a transaction block that updates stock safely

💡 Why This Matters

🌍 Real World

Online stores and inventory systems must handle many users updating stock at the same time without errors.

💼 Career

Database administrators and backend developers use concurrency control to keep data accurate and consistent in multi-user environments.

1

Create the books table and insert initial data

Create a table called books with columns id (integer primary key), title (text), and stock (integer). Then insert these exact rows: (1, 'Learn SQL', 10), (2, 'PostgreSQL Basics', 5), and (3, 'Concurrency Control', 2).

PostgreSQL

-- Create the books table and insert initial data
-- Your code here

Need a hint?

Use CREATE TABLE to define the structure and INSERT INTO to add the rows exactly as given.

2

Set a stock threshold variable

Create a variable called stock_threshold and set it to 5 to represent the minimum stock level before restocking is needed.

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, 'Concurrency Control', 2);

-- Set the stock_threshold variable
-- Your code here

Need a hint?

Use the \set command in psql to create a variable.

3

Select books with stock below the threshold

Write a SQL query to select id, title, and stock from books where stock is less than the variable stock_threshold.

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, 'Concurrency Control', 2);

\set stock_threshold 5

-- Select books with stock less than stock_threshold
-- Your code here

Need a hint?

Use WHERE stock < :stock_threshold to compare with the variable.

4

Update stock safely using a transaction

Write a transaction block that decreases the stock of the book with id = 3 by 1. Use BEGIN, UPDATE, and COMMIT to ensure the update is safe when multiple users buy the book at the same time.

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, 'Concurrency Control', 2);

\set stock_threshold 5

SELECT id, title, stock FROM books WHERE stock < :stock_threshold;

-- Begin transaction to safely update stock
-- Your code here

Need a hint?

Use BEGIN and COMMIT to wrap the UPDATE statement.