Read committed behavior in PostgreSQL - Mini Project: Build & Apply

Understanding Read Committed Behavior in PostgreSQL

📖 Scenario: You are working with a simple banking database where multiple transactions happen concurrently. You want to understand how PostgreSQL's Read Committed isolation level affects the visibility of data changes during transactions.

🎯 Goal: Build a step-by-step SQL project that demonstrates how the READ COMMITTED isolation level works by creating a table, inserting initial data, setting the isolation level, and running queries to observe committed data visibility.

📋 What You'll Learn

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

Insert two rows into accounts with exact values: (1, 1000) and (2, 1500).

Set the transaction isolation level to READ COMMITTED.

Write a query to select all rows from accounts.

💡 Why This Matters

🌍 Real World

Banks and financial systems use transaction isolation levels to ensure data consistency when multiple users access and modify data at the same time.

💼 Career

Understanding transaction isolation levels is essential for database administrators and backend developers to prevent data anomalies and ensure reliable applications.

1

Create the accounts table

Write a SQL statement to create a table called accounts with two columns: id as an integer primary key and balance as an integer.

PostgreSQL

-- Your code here to create the accounts table

Need a hint?

Use CREATE TABLE with column definitions and specify PRIMARY KEY for id.

2

Insert initial data into accounts

Insert exactly two rows into the accounts table with these values: (1, 1000) and (2, 1500).

PostgreSQL

CREATE TABLE accounts (
    id INTEGER PRIMARY KEY,
    balance INTEGER
);
-- Your code here to insert two rows

Need a hint?

Use a single INSERT INTO statement with multiple value tuples.

3

Set the transaction isolation level to READ COMMITTED

Write a SQL statement to set the transaction isolation level to READ COMMITTED.

PostgreSQL

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

INSERT INTO accounts (id, balance) VALUES (1, 1000), (2, 1500);
-- Your code here to set isolation level

Need a hint?

Use the SET TRANSACTION ISOLATION LEVEL command followed by READ COMMITTED.

4

Query all rows from accounts

Write a SQL query to select all columns and rows from the accounts table.

PostgreSQL

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

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

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
-- Your code here to select all rows

Need a hint?

Use SELECT * FROM accounts; to retrieve all data.