CA 36 – Isolation (ACID)

This one was about Isolation, basically what happens when two people try to use the same account at the same time.

I used two sessions (like opening two terminals).

First I checked data:

SELECT * FROM accounts;

Alice -> 1000
Bob -> 500

Now in Session 1:

BEGIN;

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

I did not commit yet.

Now in Session 2, I tried:

SELECT * FROM accounts WHERE name = 'Alice';

It still shows 1000, not 200.
So uncommitted change is not visible → no dirty read.

Then in Session 2 I tried to update:

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

This got stuck (waiting).
Because Session 1 is still holding the lock.

Then I commit in Session 1:

COMMIT;

Now Session 2 continues and updates based on new value.

So final balance becomes:
1000 - 800 - 300 = -100 (but this fails due to CHECK constraint)

So transaction fails and rollback happens.

Then I tried changing isolation level:

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

Default level, works like above (no dirty reads).

Then tried:

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

Now if two transactions conflict, one will fail with error instead of waiting silently.

So what I understood:

Isolation makes sure one transaction does not mess with another
Uncommitted data is not visible
Database uses locks to control access
Prevents issues like dirty read and lost update

So even if two users try to send money at same time, DB keeps things safe.