Ensuring Safe Concurrent Updates in Real-Time Systems

-- Lost update scenario at National Bank of India
-- Account SAV2001 has balance = 100000.00

-- Teller A reads balance
SELECT balance FROM accounts WHERE account_id = 'SAV2001';  -- 100000.00

-- Teller B reads balance (same value)
SELECT balance FROM accounts WHERE account_id = 'SAV2001';  -- 100000.00

-- Teller A debits 30000
UPDATE accounts SET balance = 100000.00 - 30000.00 WHERE account_id = 'SAV2001';
-- balance = 70000.00

-- Teller B debits 20000 (uses stale read of 100000)
UPDATE accounts SET balance = 100000.00 - 20000.00 WHERE account_id = 'SAV2001';
-- balance = 80000.00  ← Teller A's debit is LOST!
-- Should be 50000.00

-- Pessimistic locking: safe concurrent debit
BEGIN;

SELECT balance FROM accounts
WHERE account_id = 'SAV2001'
FOR UPDATE;  -- row is locked until COMMIT

-- Other transactions trying to read FOR UPDATE on SAV2001 block here

UPDATE accounts
SET balance = balance - 30000.00
WHERE account_id = 'SAV2001';

COMMIT;  -- lock released, next transaction proceeds

-- Optimistic locking with a version column
ALTER TABLE accounts ADD COLUMN version INTEGER DEFAULT 0;

-- Step 1: read the row with its version
SELECT balance, version FROM accounts WHERE account_id = 'SAV2001';
-- balance = 100000.00, version = 5

-- Step 2: update only if version has not changed
UPDATE accounts
SET balance = balance - 30000.00, version = version + 1
WHERE account_id = 'SAV2001' AND version = 5;
-- If 1 row affected: success
-- If 0 rows affected: someone else updated first, RETRY

package com.bank.service;

import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class AccountService {

    private static final int MAX_RETRIES = 3;

    public void debit(Connection conn, String accountId,
                      BigDecimal amount) throws SQLException {
        for (int attempt = 0; attempt < MAX_RETRIES; attempt++) {
            conn.setAutoCommit(false);
            try (PreparedStatement read = conn.prepareStatement(
                    "SELECT balance, version FROM accounts WHERE account_id = ?")) {
                read.setString(1, accountId);
                ResultSet rs = read.executeQuery();
                if (!rs.next()) throw new IllegalArgumentException("Account not found");

                BigDecimal balance = rs.getBigDecimal("balance");
                int version = rs.getInt("version");

                if (balance.compareTo(amount) < 0) {
                    throw new IllegalStateException("Insufficient balance");
                }

                try (PreparedStatement update = conn.prepareStatement(
                        "UPDATE accounts SET balance = balance - ?, version = version + 1 " +
                        "WHERE account_id = ? AND version = ?")) {
                    update.setBigDecimal(1, amount);
                    update.setString(2, accountId);
                    update.setInt(3, version);
                    int rows = update.executeUpdate();

                    if (rows == 1) {
                        conn.commit();
                        return;  // success
                    }
                    // rows == 0 means version changed, retry
                    conn.rollback();
                }
            } catch (SQLException e) {
                conn.rollback();
                throw e;
            }
        }
        throw new IllegalStateException("Failed after " + MAX_RETRIES + " retries");
    }
}

package com.bank.service;

import java.util.concurrent.atomic.AtomicLong;

public class TransactionCounter {
    private final AtomicLong counter = new AtomicLong(0);

    public long incrementAndGet() {
        return counter.incrementAndGet();
    }

    // CAS: only update if the value is still what we expect
    public boolean trySetIfExpected(long expected, long newValue) {
        return counter.compareAndSet(expected, newValue);
    }
}

-- SERIALIZABLE: strongest isolation, detects all conflicts
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN;
SELECT balance FROM accounts WHERE account_id = 'SAV2001';
UPDATE accounts SET balance = balance - 30000 WHERE account_id = 'SAV2001';
COMMIT;
-- If another transaction conflicted, this gets:
-- ERROR: could not serialize access due to concurrent update
-- Application must RETRY

package com.bank.service;

import redis.clients.jedis.Jedis;
import redis.clients.jedis.params.SetParams;
import java.util.UUID;

public class DistributedLockService {

    private static final int LOCK_TTL_MS = 5000;

    public String acquireLock(Jedis jedis, String accountId) {
        String lockKey = "lock:account:" + accountId;
        String lockValue = UUID.randomUUID().toString();
        String result = jedis.set(lockKey, lockValue,
                SetParams.setParams().nx().px(LOCK_TTL_MS));
        return "OK".equals(result) ? lockValue : null;
    }

    public void releaseLock(Jedis jedis, String accountId, String lockValue) {
        String lockKey = "lock:account:" + accountId;
        // Only release if we still own the lock
        String current = jedis.get(lockKey);
        if (lockValue.equals(current)) {
            jedis.del(lockKey);
        }
    }
}