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