Repeatable read behavior in PostgreSQL - Mini Project: Build & Apply

Understanding Repeatable Read Behavior in PostgreSQL

📖 Scenario: You are working on a banking application where multiple transactions happen concurrently. You want to ensure that during a transaction, the data you read remains consistent even if other transactions modify the data at the same time.

🎯 Goal: Build a PostgreSQL transaction using the REPEATABLE READ isolation level to demonstrate how data remains stable during the transaction despite concurrent updates.

📋 What You'll Learn

Create a table called accounts with columns id (integer primary key) and balance (integer).

Insert two rows into accounts with id values 1 and 2, and balances 1000 and 2000 respectively.

Start a transaction with REPEATABLE READ isolation level.

Within the transaction, select the balance of account with id = 1 twice, showing that the value does not change even if updated outside the transaction.

Commit the transaction.

💡 Why This Matters

🌍 Real World

Banking and financial systems require consistent reads during transactions to avoid errors like double spending or incorrect balances.

💼 Career

Understanding transaction isolation levels is essential for database developers and backend engineers to ensure data integrity in concurrent environments.

1

Create the accounts table and insert initial data

Create a table called accounts with columns id as integer primary key and balance as integer. Then insert two rows with id 1 and 2, and balances 1000 and 2000 respectively.

PostgreSQL

-- Your code here

Need a hint?

Use CREATE TABLE to define the table and INSERT INTO to add the two rows.

2

Start a transaction with REPEATABLE READ isolation level

Begin a transaction and set the isolation level to REPEATABLE READ using the exact command BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;.

PostgreSQL

CREATE TABLE accounts (
    id INTEGER PRIMARY KEY,
    balance INTEGER
);

INSERT INTO accounts (id, balance) VALUES (1, 1000), (2, 2000);

-- Your code here

Need a hint?

Use the BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ; statement to start the transaction.

3

Select the balance of account 1 twice inside the transaction

Inside the transaction, write two SELECT statements to get the balance from accounts where id = 1. Use the exact query SELECT balance FROM accounts WHERE id = 1; twice.

PostgreSQL

CREATE TABLE accounts (
    id INTEGER PRIMARY KEY,
    balance INTEGER
);

INSERT INTO accounts (id, balance) VALUES (1, 1000), (2, 2000);

BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;

-- Your code here

Need a hint?

Use the same SELECT query twice to check the balance inside the transaction.

4

Commit the transaction

End the transaction by writing the exact command COMMIT;.

PostgreSQL

CREATE TABLE accounts (
    id INTEGER PRIMARY KEY,
    balance INTEGER
);

INSERT INTO accounts (id, balance) VALUES (1, 1000), (2, 2000);

BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;

SELECT balance FROM accounts WHERE id = 1;
SELECT balance FROM accounts WHERE id = 1;

-- Your code here

Need a hint?

Use COMMIT; to finish the transaction and save changes.