Isolation

Today I tried to understand how isolation works in a database by simulating multiple users using the same account at the same time. This is very important for applications like PhonePe or GPay because many users can perform transactions at once.

I used the same accounts table where Alice has 1000 balance. Then I opened two database sessions to act like two different users.

In the first session, I started a transaction and deducted money from Alice’s account but did not commit it.

BEGIN;

UPDATE accounts
SET balance = balance - 700
WHERE name = 'Alice';

At this point, the balance is reduced inside this transaction, but it is not yet saved permanently.

Now in the second session, I also started a transaction and tried to read Alice’s balance.

BEGIN;

SELECT * FROM accounts WHERE name = 'Alice';

I observed that the second session still showed the old balance as 1000. It did not see the uncommitted change from the first session. This means dirty read is prevented.

Then I tried to update again in the second session.

UPDATE accounts
SET balance = balance - 500
WHERE name = 'Alice';

This query did not execute immediately. It waited until the first transaction was either committed or rolled back. This showed that PostgreSQL is preventing conflicts between transactions.

Next, I committed the first transaction.

COMMIT;

After that, the second transaction continued and used the updated balance. This ensured that the final result was correct and no money was lost.

I repeated this experiment with different isolation levels. In the default level, which is read committed, I saw that uncommitted data is not visible. This prevents dirty reads.

When I thought about lower isolation like read uncommitted, such problems could happen, but PostgreSQL does not actually allow dirty reads even in that mode.

I understood that isolation ensures that multiple transactions do not interfere with each other. It prevents problems like reading incomplete data, updating wrong values, or losing updates.