ACID Transactions & Isolation Levels in Core Banking: Race Conditions, Locking & Concurrency

The Core Problem: Concurrency

Imagine Customer A’s account has 1,000,000 VND. Two events happen at exactly the same time:

• Event 1: The customer withdraws 800,000 VND at an ATM.
• Event 2: An Auto-Debit system deducts a 500,000 VND service fee.

If both transactions read the balance as 1,000,000 simultaneously and then overwrite each other’s result, one of them will be lost — the customer might effectively withdraw 1,300,000 VND from an account that only had 1,000,000 VND. This is a Lost Update error — a catastrophic failure in any financial system.

ACID — The Four Mandatory Properties

Isolation Levels

This is knowledge that many developers overlook but is absolutely critical in Core Banking:

1. READ UNCOMMITTED (Never used in Core Banking)

Allows reading uncommitted data from other transactions. Causes Dirty Reads — reading data that might eventually be rolled back.

2. READ COMMITTED (The absolute minimum acceptable)

Only reads committed data. However, you can still experience a Non-Repeatable Read — reading the same record twice in the same transaction might yield different results if another transaction commits in between.

3. REPEATABLE READ (Default in MySQL/InnoDB)

Data read within a transaction will not change during its lifecycle, preventing Non-Repeatable Reads. However, it can still suffer from Phantom Reads (new rows appearing).

4. SERIALIZABLE (Strongest — used for critical transactions)

Transactions execute sequentially. Prevents all anomalies including Phantom Reads. Offers the lowest performance but the highest safety.

-- PostgreSQL: Setting isolation level for a transaction
BEGIN;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
-- ... execute financial operations ...
COMMIT;

Locking Strategies

Pessimistic Locking

Lock the record immediately upon reading, preventing any other transaction from touching it until the current transaction completes.

-- PostgreSQL: SELECT FOR UPDATE
BEGIN;
SELECT balance, available_balance 
FROM accounts 
WHERE account_number = 'ACC001'
FOR UPDATE;  -- Locks this row until COMMIT or ROLLBACK

-- Check balances
-- UPDATE balance...
-- INSERT into ledger_entries...
COMMIT;

Pros: Simple, guarantees no race conditions. Cons: Causes high contention when many transactions fight for the same account. High risk of deadlocks if lock ordering isn’t carefully designed.

Optimistic Locking

Does not lock upon reading. Before writing, it checks if the data has been altered since it was read.

-- Add a version column to the accounts table
ALTER TABLE accounts ADD COLUMN version BIGINT NOT NULL DEFAULT 1;

-- When updating, include the version condition
UPDATE accounts
SET 
    current_balance = current_balance - 500000,
    version = version + 1
WHERE 
    account_number = 'ACC001'
    AND version = 5;  -- Must match the version read earlier

-- If affected_rows = 0, another transaction modified it first
-- → Retry or return a Conflict error

Pros: Higher throughput in low-conflict environments. Cons: Requires retry logic built into the application layer.

When to use which?

Idempotency — Preventing Duplicate Transactions

Networks can timeout. Clients can retry requests. How do you guarantee a “transfer 1 million” command only happens exactly once, even if the request is sent 5 times?

The solution is an Idempotency Key:

CREATE TABLE transaction_requests (
    idempotency_key VARCHAR(64) PRIMARY KEY,  -- UUID generated by client
    status          VARCHAR(20) NOT NULL DEFAULT 'PROCESSING',
    result          JSONB,
    created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    expires_at      TIMESTAMPTZ NOT NULL
);

Processing Logic:
1. Receive request with Idempotency-Key header
2. Check key in transaction_requests table
   → If exists and status = 'COMPLETED': return old result immediately
   → If PROCESSING: return 409 Conflict (currently processing)
   → If not exists: INSERT new key, proceed with processing
3. After completion: UPDATE status = 'COMPLETED', save result

Deadlocks — The Silent Enemy

A deadlock occurs when Transaction A locks Account X and waits for Account Y, while Transaction B locks Account Y and waits for Account X.

The only prevention method in Core Banking: Always lock multiple accounts in a deterministic order:

// WRONG: Can cause deadlock
func transfer(fromID, toID string) {
    lockAccount(fromID)  // Tx A locks A first
    lockAccount(toID)    // Tx B locks B first → deadlock
}

// CORRECT: Always lock the smaller ID first
func transfer(fromID, toID string) {
    first, second := fromID, toID
    if fromID > toID {
        first, second = toID, fromID
    }
    lockAccount(first)
    lockAccount(second)
}

Database Checklist for Core Banking

• Store currency as BIGINT (smallest unit), never use FLOAT
• Wrap all financial operations in a single Database Transaction
• Implement an Idempotency Key mechanism to prevent duplicates
• Enforce consistent lock ordering to prevent deadlocks
• Ensure Ledger entries are immutable (INSERT only, never UPDATE/DELETE)
• Run periodic invariant checks: SUM(DEBIT) = SUM(CREDIT)

Next, we will look at how modern digital banks solve this problem at the scale of hundreds of millions of transactions per day using Microservices architecture. Continue reading Part 4 — Banking Microservices Architecture (Modern Core Banking).