Design a Reliable Wallet Transfer System with ACID Guarantees pt - 3 (Isolation)

Source: Dev.to

Isolation

Ensures that multiple transactions execute independently without interfering with each other, even when running concurrently.

Setup

-- Create the accounts table
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 dummy data
INSERT INTO accounts (name, balance)
VALUES ('Alice', 1000), ('Bob', 500);

Initial balances:

• Alice = 1000
• Bob = 500

Testing Isolation

Transaction 1

BEGIN;

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

Transaction 2 (runs concurrently)

BEGIN;

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

Without Proper Isolation

• Both transactions may read the same initial balance (1000).
• Each update proceeds based on outdated data.
• This can lead to an incorrect final balance (race condition).

With Proper Isolation

• One transaction is executed or completed before the other can affect the same row.
• The second transaction will either wait or see the updated value.
• Inconsistent updates are prevented, and the final balance remains correct.

Mechanisms

Isolation is maintained through mechanisms such as:

• Optimistic locking – checks for conflicts before committing.
• Pessimistic locking – acquires locks that block other transactions until the lock is released.

When the database prevents both transactions from interfering with each other and maintains correct results, isolation is working as intended.