ISOLATION

Isolation in Wallet Transfer System

In this task, I understood the Isolation property of ACID using a simple wallet system. In applications like GPay or PhonePe, many users perform transactions at the same time. Isolation ensures that one transaction does not affect another transaction in a wrong way.

I created a table called accounts to store user balances.

CREATE TABLE accounts (
id SERIAL PRIMARY KEY,
name TEXT NOT NULL,
balance INT NOT NULL CHECK (balance >= 0),
last_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Then I inserted sample data.

INSERT INTO accounts (name, balance)
VALUES
('Alice', 1000),
('Bob', 500);

To check initial data:

SELECT * FROM accounts;

At this stage, Alice had 1000 and Bob had 500.

Testing Isolation using two sessions
To understand isolation, I opened two query windows (Session 1 and Session 2).

Session 1
In the first session, I started a transaction and deducted money from Alice, but I did not commit.

BEGIN;

UPDATE accounts
SET balance = balance - 700
WHERE id = 1;

SELECT * FROM accounts WHERE id = 1;
Here, Session 1 shows Alice’s balance as 300.

Session 2
In another session, I checked Alice’s balance.

SELECT * FROM accounts WHERE id = 1;
It still showed 1000, not 300.

This means Session 2 cannot see the uncommitted changes of Session 1. This prevents dirty reads.

Trying concurrent update
In Session 2, I tried to update the same row.

BEGIN;

UPDATE accounts
SET balance = balance - 500
WHERE id = 1;
This query did not execute immediately. It waited because Session 1 was already using that row.

Commit in Session 1
Now I committed the first transaction.

COMMIT;
After this, Session 2 continued execution. This shows that PostgreSQL uses locking to avoid conflicts.

Testing Isolation Levels
By default, PostgreSQL uses Read Committed isolation level.

BEGIN;
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

SELECT * FROM accounts WHERE id = 1;
This level only shows committed data.

Repeatable Read
BEGIN;
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

SELECT * FROM accounts WHERE id = 1;
In this level, the same query will give the same result within the transaction, even if another transaction changes the data.

Serializable
BEGIN;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
This is the highest level. It ensures transactions behave like they are executed one after another.