Dirty Read Prevention: Banking Transfer with Read Committed

intermediate

Bank needs to display account balance to customer. Requirements: Must show only committed balance (not in-progress transfers). Traditional approach: Read Uncommitted (fastest). Problem: Customer sees balance including uncommitted transfer, makes withdrawal based on wrong balance, account goes negative. Solution: Use Read Committed to prevent dirty reads.

-- Scenario: Customer A has $1000, Transfer $500 to Customer B in progress

-- Transaction 1: Transfer in progress (not committed yet)

BEGIN;

UPDATE accounts SET balance = balance - 500 WHERE account_id = 'A';

-- Balance is now $500 (not committed)

UPDATE accounts SET balance = balance + 500 WHERE account_id = 'B';

-- Simulate long-running transaction (network delay, validation)

SELECT pg_sleep(5); -- Wait 5 seconds before commit

-- Transaction 2: Customer A checks balance (Read Uncommitted - WRONG)

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

BEGIN;

SELECT balance FROM accounts WHERE account_id = 'A';

-- Sees $500 (dirty read - uncommitted change from Transaction 1)

COMMIT;

-- Customer A thinks balance is $500, tries to withdraw $400

BEGIN;

UPDATE accounts SET balance = balance - 400 WHERE account_id = 'A';

-- Balance would be $100 ($500 - $400)

COMMIT;

-- Transaction 1: Validation fails, rollback transfer

ROLLBACK; -- Balance back to $1000

-- Result: Customer A balance should be $1000, but is now $600

-- Lost $400 due to dirty read (saw uncommitted $500, withdrew $400)

-- CORRECT APPROACH: Use Read Committed

-- Transaction 1: Transfer in progress (not committed yet)

BEGIN;

UPDATE accounts SET balance = balance - 500 WHERE account_id = 'A';

-- Balance is now $500 (not committed)

UPDATE accounts SET balance = balance + 500 WHERE account_id = 'B';

SELECT pg_sleep(5); -- Wait 5 seconds

-- Transaction 2: Customer A checks balance (Read Committed - CORRECT)

SET TRANSACTION ISOLATION LEVEL READ COMMITTED; -- Default

BEGIN;

SELECT balance FROM accounts WHERE account_id = 'A';

-- Sees $1000 (committed value, ignores uncommitted change)

COMMIT;

-- Customer A sees correct balance $1000, makes informed decision