ISOLATION

Initial Check
SELECT * FROM accounts;

Explanation:
I checked the initial data.
Alice has 1000 and Bob has 500.

Case 1: READ COMMITTED (default)
Session 1
BEGIN;

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

Explanation:
I started a transaction and deducted 800 from Alice.
I did not commit, so this change is temporary.

Session 2
BEGIN;

SELECT balance
FROM accounts
WHERE name = 'Alice';

Explanation:
I checked Alice’s balance in another session.
It still shows 1000.

What should happen:
It should show old value

What should not happen:
It should not show updated value (200)

Reason:
Uncommitted data is not visible

Session 2 tries update
UPDATE accounts
SET balance = balance - 500
WHERE name = 'Alice';

Explanation:
This query does not run immediately. It waits.

What should happen:
It should wait

What should not happen:
It should not update instantly

Reason:
Row is locked by session 1

Session 1 commit
COMMIT;

Explanation:
Now the change becomes permanent.
Session 2 will now continue.

Session 2 commit
COMMIT;

Explanation:
Second transaction also completes.

Case 2: REPEATABLE READ
Session 1
BEGIN ISOLATION LEVEL REPEATABLE READ;

UPDATE accounts
SET balance = balance - 300
WHERE name = 'Alice';
Session 2
BEGIN ISOLATION LEVEL REPEATABLE READ;

SELECT balance
FROM accounts
WHERE name = 'Alice';

Explanation:
Session 2 reads the balance once.
Even if session 1 commits later, this value does not change.

What should happen:
Same value should be seen inside transaction

What should not happen:
Value should not change in same transaction

Reason:
Data must remain consistent

Case 3: SERIALIZABLE
Session 1
BEGIN ISOLATION LEVEL SERIALIZABLE;

UPDATE accounts
SET balance = balance - 200
WHERE name = 'Alice';
Session 2
BEGIN ISOLATION LEVEL SERIALIZABLE;

UPDATE accounts
SET balance = balance - 200
WHERE name = 'Alice';
Commit
COMMIT;

Explanation:
When both try to update same data, one may fail.

What should happen:
One transaction may give error

What should not happen:
Both should not succeed if it causes wrong balance

Reason:
Database avoids conflicts

Final understanding

In this experiment, I understood that transactions and isolation levels help to control concurrent operations.
They prevent reading wrong data and avoid incorrect balance updates.
This is important for systems like payment apps where accuracy is very important****