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

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

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

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

Hint

Use BEGIN and COMMIT to wrap the UPDATE statement.

Practice

1. Why is concurrency control important in PostgreSQL databases?

easy

A. It prevents data conflicts when multiple users access the database simultaneously.

B. It speeds up the database by skipping transactions.

C. It deletes old data automatically to save space.

D. It allows users to edit the same data without restrictions.

Solution

1. Step 1: Understand concurrency control purpose

   Concurrency control ensures data consistency when many users work at the same time.

2. Step 2: Identify correct effect in PostgreSQL

   PostgreSQL uses locks and transactions to prevent conflicts and keep data safe.

3. Final Answer:

   It prevents data conflicts when multiple users access the database simultaneously. -> Option A

4. Quick Check:

   Concurrency control = prevent conflicts [OK]

Hint: Concurrency control = avoid data conflicts [OK]

Common Mistakes:

• Thinking concurrency control speeds up by skipping transactions
• Believing it deletes data automatically
• Assuming users can edit data without restrictions

2. Which of the following is the correct way to start a transaction in PostgreSQL?

easy

A. BEGIN TRANSACTION

B. START TRANSACTION

C. BEGIN;

D. OPEN TRANSACTION;

Solution

1. Step 1: Recall PostgreSQL transaction syntax

   PostgreSQL uses BEGIN; to start a transaction block.

2. Step 2: Compare options with PostgreSQL syntax

   Only BEGIN; is valid; others are either invalid or not standard in PostgreSQL.

3. Final Answer:

   BEGIN; -> Option C

4. Quick Check:

   Start transaction = BEGIN; [OK]

Hint: Use BEGIN; to start transactions in PostgreSQL [OK]

Common Mistakes:

• Using START TRANSACTION which is MySQL syntax
• Using OPEN TRANSACTION; which is invalid
• Confusing BEGIN TRANSACTION with BEGIN;

3. Consider this sequence in PostgreSQL:

BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

What is the main reason these statements are grouped in a transaction?

medium

A. To ensure both updates happen together or not at all.

B. To speed up the updates by running them in parallel.

C. To allow other users to see partial changes immediately.

D. To automatically create a backup before updates.

Solution

1. Step 1: Understand transaction grouping

   Grouping updates in a transaction ensures atomicity -- all changes succeed or none do.

2. Step 2: Identify why atomicity matters here

   If one update fails, the other should not apply to avoid inconsistent balances.

3. Final Answer:

   To ensure both updates happen together or not at all. -> Option A

4. Quick Check:

   Transaction = atomic updates [OK]

Hint: Transactions ensure all-or-nothing changes [OK]

Common Mistakes:

• Thinking transactions speed up updates by parallelism
• Believing partial changes are visible immediately
• Assuming transactions create automatic backups

4. You run this PostgreSQL code:

BEGIN;
UPDATE products SET stock = stock - 1 WHERE id = 10;
-- forgot to COMMIT or ROLLBACK

What problem can occur if you forget to commit or rollback?

medium

A. The update will be saved automatically after a timeout.

B. The update will never be saved and locks may block other users.

C. The database will delete the product with id 10.

D. Other users can edit the same product simultaneously without issues.

Solution

1. Step 1: Understand transaction finalization

   Without COMMIT or ROLLBACK, the transaction stays open and changes are not saved.

2. Step 2: Identify impact on locks and other users

   Open transactions hold locks, blocking others from accessing the updated row.

3. Final Answer:

   The update will never be saved and locks may block other users. -> Option B

4. Quick Check:

   Unfinished transaction = no save + locks [OK]

Hint: Always COMMIT or ROLLBACK to release locks [OK]

Common Mistakes:

• Assuming update saves automatically after timeout
• Thinking database deletes data on unfinished transactions
• Believing other users can edit locked rows freely

5. In a high-traffic PostgreSQL system, two transactions try to update the same row simultaneously. What concurrency control mechanism helps avoid data corruption?

hard

A. Automatic rollback of all transactions after 1 second.

B. Creating duplicate rows to avoid conflicts.

C. Ignoring conflicts and overwriting data silently.

D. Row-level locking to serialize access to the row.

Solution

1. Step 1: Identify concurrency control methods in PostgreSQL

   PostgreSQL uses row-level locks to control access to individual rows during updates.

2. Step 2: Understand how row-level locking prevents corruption

   Locks serialize access so only one transaction modifies a row at a time, avoiding conflicts.

3. Final Answer:

   Row-level locking to serialize access to the row. -> Option D

4. Quick Check:

   Row-level locks = safe concurrent updates [OK]

Hint: Row-level locks prevent simultaneous conflicting updates [OK]

Common Mistakes:

• Thinking transactions auto-rollback after timeout
• Assuming conflicts are ignored silently
• Believing duplicates are created to avoid conflicts