Banking Transaction Locking Strategies

Context

In American Express payment and account systems, concurrent updates to balances, authorizations, and ledger entries must be handled safely. Interviewers ask this to assess whether you understand how databases prevent lost updates and inconsistent transaction states.

Core Question

Explain the difference between optimistic locking and pessimistic locking in the context of a banking transaction. Your answer should cover:

1. How each locking approach works
2. What problem each approach is trying to prevent
3. When you would choose one over the other in a financial system such as American Express
4. The tradeoffs in concurrency, latency, retries, and data consistency
5. How PostgreSQL features such as row locks or version checks support each pattern

Scope Guidance

Keep the discussion practical rather than academic. The interviewer expects you to connect the concepts to real transaction flows like debits, credits, balance updates, or payment authorization, and to explain why one approach may be safer or more scalable depending on contention levels.

Pessimistic Locking

Pessimistic locking assumes conflicts are likely, so a transaction locks the row before updating it. Other transactions must wait, which reduces concurrency but prevents two sessions from modifying the same balance at the same time.

BEGIN;
SELECT account_id, balance
FROM amex_accounts
WHERE account_id = 101
FOR UPDATE;

UPDATE amex_accounts
SET balance = balance - 100.00
WHERE account_id = 101;
COMMIT;

Optimistic Locking

Optimistic locking assumes conflicts are relatively rare, so transactions proceed without taking an early lock. At update time, the application checks whether the row changed since it was read, usually with a version number or last_updated value.

UPDATE amex_accounts
SET balance = balance - 100.00,
    version = version + 1
WHERE account_id = 101
  AND version = 7;

Lost Update Prevention

Both approaches aim to prevent lost updates, where two concurrent transactions read the same starting balance and one overwrites the other's change. In banking, this can lead to incorrect balances, duplicate debits, or broken ledger reconciliation.

Retry vs Wait Behavior

With pessimistic locking, a conflicting transaction usually waits for the lock to be released. With optimistic locking, the conflicting transaction typically fails its version check and must retry or return an error to the application.

PostgreSQL Support

PostgreSQL supports pessimistic locking through row-level locks such as FOR UPDATE. It supports optimistic locking through application-managed version columns, conditional UPDATE statements, and transaction isolation rules.

SELECT *
FROM amex_accounts
WHERE account_id = 101
FOR UPDATE;