DEV Community

Tanishka V
Tanishka V

Posted on

CA 27 - Isolation

#computerscience #database #postgres #sql

Simulating Concurrent Transactions and Understanding Isolation in a Digital Wallet System

In this experiment, I extended my digital wallet simulation (similar to PhonePe or GPay) to understand how databases handle concurrent transactions. In real-world systems, multiple users may try to access or modify the same data at the same time. If not handled properly, this can lead to serious issues like incorrect balances, duplicate deductions, or inconsistent data.

Initial Setup

I used the same accounts table and dummy data:

CREATE TABLE accounts (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    balance INT NOT NULL CHECK (balance >= 0),
    last_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

INSERT INTO accounts (name, balance)
VALUES
('Alice', 1000),
('Bob', 500);
Enter fullscreen mode Exit fullscreen mode

To verify:

SELECT * FROM accounts;
Enter fullscreen mode Exit fullscreen mode

Initial state:

  • Alice → 1000
  • Bob → 500

Simulating Concurrent Transactions

To simulate concurrency, I opened two query windows (sessions) in pgAdmin.

Session 1 (Transaction Not Committed)

BEGIN;

UPDATE accounts
SET balance = balance - 300
WHERE name = 'Alice';
Enter fullscreen mode Exit fullscreen mode
  • Alice’s balance becomes 700 (inside Session 1)
  • BUT changes are NOT committed yet

Session 2 (Concurrent Transaction)

Now, in another query window:

BEGIN;

SELECT * FROM accounts WHERE name = 'Alice';
Enter fullscreen mode Exit fullscreen mode
  • Session 2 still sees Alice = 1000
  • It cannot see uncommitted changes

Trying Another Deduction in Session 2

UPDATE accounts
SET balance = balance - 500
WHERE name = 'Alice';
Enter fullscreen mode Exit fullscreen mode
  • This query will WAIT (lock) until Session 1 finishes
  • PostgreSQL prevents conflicting updates

Commit Session 1

COMMIT;
Enter fullscreen mode Exit fullscreen mode

Now:

  • Alice → 700

Session 2 Continues

After Session 1 commits:

  • Session 2 resumes execution

Final result:

  • Alice → 200

Observations

  • Session 2 could not see uncommitted data → prevents Dirty Reads
  • PostgreSQL used locking to avoid simultaneous updates
  • No inconsistent or corrupted balance occurred
  1. READ COMMITTED (Default) 
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
Enter fullscreen mode Exit fullscreen mode
  • Cannot see uncommitted data
  • Prevents dirty reads
  • May allow non-repeatable reads
  1. REPEATABLE READ 
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
Enter fullscreen mode Exit fullscreen mode

✔ Data remains consistent within the transaction
✔ Prevents non-repeatable reads
✔ Stronger consistency than READ COMMITTED

  1. SERIALIZABLE 
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
Enter fullscreen mode Exit fullscreen mode
  • Highest level of isolation
  • Transactions behave as if executed one by one
  • Prevents all anomalies (dirty reads, non-repeatable reads, lost updates)

Conclusion

This experiment clearly showed how PostgreSQL handles concurrent transactions safely. Even when multiple sessions try to update the same account at the same time, the system ensures consistency using isolation levels and locking mechanisms.

Understanding isolation is crucial when building financial systems, because it ensures that:

  • No duplicate or conflicting transactions occur

Top comments (0)