Banking: Concurrent Withdrawals

1. Business Scenario

Account 101 belongs to the illustrative customer Rahul Sharma and holds ₹5,000. Request 7001 asks for ₹5,000; request 7002 asks for ₹10,000. Both arrive within milliseconds. They are separate authorized business requests, not duplicate deliveries.

The ₹10,000 request cannot succeed even if it acquires the lock first. In the illustrated order, request 7001 acquires the account lock first, commits its debit, and request 7002 then reads ₹0. The application must never decide from an earlier unlocked balance.

This lesson models an internal ledger withdrawal. Cash dispensing or an external bank debit would require an additional externally confirmed payment workflow; a local SUCCESS does not prove cash was delivered.

2. Real Database Tables

These are educational schema definitions, not tables to add to the job portal's live database. PK means primary key; FK means a foreign key to the named parent. Use DECIMAL(19,2) for stored money and integer minor units or decimal-safe arithmetic in application code.

Use MySQL InnoDB for these row-locking examples. NOT NULL, foreign keys and scoped UNIQUE constraints complement application validation. Monetary CHECK constraints require an engine/version that enforces them; the service must still reject invalid amounts.

accounts

Use InnoDB. Authorize customer ownership; all amounts in this scenario are INR.

withdrawal_requests

UNIQUE(account_id, idempotency_key). Reusing a key with a different amount is rejected.

account_transactions

No entry exists before the requests. The successful debit will create WD-7001. A full general ledger also has a balancing cash/clearing leg.

audit_logs

Append-only access policy; timestamps and actor identity explain who changed a result. Avoid full bank details or secrets in metadata.

outbox_events

Write in the financial transaction; a dispatcher retries unpublished events. Consumers also deduplicate because queue delivery may repeat.

3. Sample Table Data

The rows below are fictional starting records. NULL means no confirmed value is known. Empty ledger/document tables are intentional before processing; their inserted records appear in Final Database State.

Large-population examples show representative rows, not all 2,000 or 10,000 records. Run totals refer to the complete illustrative population, not the displayed subset.

4. User Action / Request

The request is a business intent, not an instruction to trust browser-supplied status, owner or balance. The controller authorizes the actor, validates shape and currency, and resolves the saved obligation.

Button click to recorded result

1. Customer presses Withdraw ₹5,000
2. Controller authenticates account owner
3. Create/reuse request 7001
4. Withdrawal service locks request
5. Lock account 101
6. Validate and debit
7. Commit ledger and outcome
8. Queue receipt

5. What Happens Inside the Application

The controller validates a positive decimal amount and a client request key. It resolves account ownership from the authenticated session; it never accepts another customer's account merely because its ID is supplied.

The service loads the persisted request under a row lock. Its saved account_id and amount are authoritative. If a previous delivery already completed, return that result. Next lock the account, check ACTIVE and current funds, and perform a guarded debit.

Insert a uniquely referenced ledger entry and an audit event in the same transaction. Commit before acknowledging success. A duplicate-key or database exception rolls back the debit; it does not leave money removed without a ledger record.

Application responsibility boundaries

1. Request
2. Controller: authorize / validate
3. Service: business rules / idempotency
4. Database: locks / constraints / atomic writes
5. Outbox and document worker

6. Database Transaction

Transaction boundaries

1. BEGIN
2. Lock withdrawal_requests 7001
3. Check PENDING and stored amount
4. Lock accounts 101
5. Check ACTIVE and balance ≥ 5000
6. Debit account and insert WD-7001
7. Set request SUCCESS; add audit/outbox
8. COMMIT

Atomicity applies to the writes enclosed by one BEGIN/COMMIT. Consistency means the amount, status, ledger/allocation and audit invariants agree. Isolation prevents conflicting decisions on locked rows. Durability means a committed result survives a client disconnect.

On unexpected database errors, roll back the local transaction. A known business rejection can instead be recorded and committed without a financial mutation. Never swallow a database exception and accidentally commit half of the intended changes.

All balance mutations here belong to the same local database transaction. External notifications are deferred until after commit; adding an external transfer changes the atomicity boundary.

7. SQL Query

SQL fragments show the successful branch of an application-controlled transaction. Comments describing checks are real control-flow requirements: the handler must stop/rollback or record the specified exception when a guard fails. Do not paste all branches as one unconditional money-moving script.

Representative SQL and guarded updates

START TRANSACTION;
SELECT id, account_id, amount, status FROM withdrawal_requests WHERE id = 7001 FOR UPDATE;
SELECT id, balance, status FROM accounts WHERE id = 101 FOR UPDATE;
-- Handler stops if request is already final, amount <= 0, or account inactive.
UPDATE accounts SET balance = balance - 5000.00
WHERE id = 101 AND status = 'ACTIVE' AND balance >= 5000.00;
SELECT ROW_COUNT() AS debited;
-- Continue ONLY if debited = 1; otherwise mark request FAILED, with no ledger insert.
INSERT INTO account_transactions (id, account_id, type, amount, reference_no, status)
VALUES (9001, 101, 'WITHDRAWAL', 5000.00, 'WD-7001', 'SUCCESS');
UPDATE withdrawal_requests SET status = 'SUCCESS' WHERE id = 7001;
-- Insert audit/outbox rows in this same transaction.
COMMIT;

Use prepared parameters in application code; literal IDs are used here only so learners can track the sample rows. Verify affected-row counts before the next financial write. Unique-reference violations require inspection of the existing saved outcome, not a new debit.

8. Concurrent Request Scenario

A waiting worker must re-read the row after it obtains the lock. A value read before waiting cannot authorize a later debit/allocation. A second delivery of one business request differs from a genuinely new request: the former returns the same outcome, while the latter must recheck current funds or remaining obligation.

9. Locking / Isolation

SELECT ... FOR UPDATE is a locking read inside an explicit InnoDB transaction. The row remains protected until COMMIT or ROLLBACK. Use indexed primary/unique lookups so the operation does not scan and lock unrelated data.

Every handler touching multiple entities must follow the same lock hierarchy. Define it once for workers, callbacks and reconcilers; opposite order can create deadlocks. A deadlock victim must restart the whole local transaction and re-read data.

Lock the saved business request/payment first, then its mutable account/invoice/item. Keep the transaction short and use current locked values, even if an earlier page showed a different balance.

Lock lifetime

1. BEGIN
2. Indexed locking read
3. Validate current row
4. Guarded mutation
5. COMMIT / ROLLBACK
6. Lock released

10. Calculation

Amounts shown in tables use two decimal places; commas and ₹ are display formatting, not database values. Round according to the documented currency/payroll rule once at the defined boundary. Do not add salary strings using floating-point arithmetic or silently change approved historical totals.

11. Table Update — Before / After

The after rows describe only committed outcomes. A rollback preserves the before financial rows; a separately committed failure/audit record can still describe the rejected request. Background document status changes do not rewrite the financial success.

12. Status Transition

Success branch

1. PENDING
2. LOCK + VALIDATE
3. SUCCESS

Failure / investigation branch

1. PENDING
2. Business validation fails
3. FAILED; no debit

13. Success Flow

Success means the defined financial operation and its associated local records are committed. Return the persisted reference and outcome to the client; do not generate a new success on each retry.

A successful response is sent after account/ledger/request writes commit together. If the response is lost, the next delivery reads the same committed success.

Committed success to user

1. Saved financial result
2. COMMIT
3. Outbox dispatcher
4. Idempotent document job
5. Private document ready
6. Notification / authorized download

14. Failure Flow

Insufficient funds is a confirmed business rejection: persist FAILED / INSUFFICIENT_FUNDS without a debit. An exception before COMMIT rolls back both debit and ledger. A deadlock victim may retry its database-only transaction; another client's completed withdrawal remains committed.

15. Retry Flow

A repeated client delivery uses WD-CLIENT-001 and returns request 7001's SUCCESS. Retry a rolled-back database attempt only with the same request and bounded attempts. Do not create a fresh request merely because the HTTP response was lost.

Safe retry decision

1. Find original saved reference
2. Was any external request sent?
3. Only local transaction → inspect committed result
4. Confirmed success → return; never repeat effect
5. Confirmed safe failure → bounded authorized retry
6. Exhausted / unresolved → investigation queue

Use bounded exponential backoff for transient technical errors and expose retry age/attempt count. An exhausted job becomes a failed/dead-letter item for an operator; a queue retry counter is not evidence of bank failure. A corrected obligation or authorized refund is a distinct linked business operation, not a stealth retry.

16. Idempotency

Persist a unique business key before processing and compare the request payload on key reuse. Apply another unique constraint to the external event/reference and to the document/outbox event. A unique key alone is not enough: the status check and financial mutation must be within the same transaction.

Callback authentication and idempotency solve different problems. Verify signatures/merchant context first; an attacker-controlled reference does not become trusted just because it is unique. Lock the request row before applying its result.

17. Queue / Background Processing

Durable background work

1. Financial transaction inserts outbox event
2. COMMIT
3. Dispatcher reads unpublished events
4. Queue accepts event
5. Idempotent worker executes
6. Mark completed / retry with same event key

Laravel afterCommit() prevents a worker from reading an uncommitted row, but a process can still crash between database commit and sending to a separate queue. A transactional outbox closes that publication gap; consumers must still handle redelivery.

Separate payment, document, notification and reconciliation queues allow independent retries and limits. Payroll/corporate schedulers paginate or chunk work rather than loading all 10,000 payments into one transaction. Do not submit external payments again just because their notification jobs failed.

Laravel dispatch after commit (illustrative job class)

GenerateWithdrawalReceipt::dispatch($request->id)->afterCommit();
// Job class is illustrative. Use a durable outbox for reliable publication.
// Deduplicate by the saved payment/request/document identity.

18. Receipt / Document Generation

Withdrawal acknowledgement WD-7001 shows ₹5,000, account ending 101, date 28 Sep 2026 and ledger status SUCCESS. It must not claim an external bank or ATM settlement that this example has not modeled.

Private document lifecycle

1. Confirmed SUCCESS
2. Database COMMIT
3. Pending document / outbox
4. Queue PDF renderer
5. Private storage
6. GENERATED
7. Authorized download

Render from the committed immutable amount/reference and approved payroll snapshot, not client input. Reserve a unique receipt/slip number and link one document to one payment. A failed write to private storage cannot turn the already-confirmed financial payment into FAILED.

19. Reconciliation

Check opening balance 5000 minus the one successful debit 5000 equals closing balance 0. Request 7002 has no debit ledger entry. If an external dispenser exists, reconcile its journal separately; internal ledger success is insufficient evidence of dispensing.

Three-way evidence check

1. Internal request / ledger / allocation
2. Compare provider confirmation by reference
3. Compare bank settlement by date/currency/net policy
4. Create discrepancy record
5. Investigate with evidence
6. Apply idempotent authorized correction
7. Close exception with audit

Investigate outcomes, never blindly overwrite

SELECT account_id, type, SUM(amount) AS total
FROM account_transactions WHERE status = 'SUCCESS'
GROUP BY account_id, type;

Count mismatches are clues, not sufficient proof. Match identifiers, amounts, currency, beneficiary and dates. Handle settlement lag, fees and refunds explicitly; never change thousands of rows to SUCCESS merely to make dashboard totals agree.

20. Audit Log

Each financial status change appends an audit event inside the same local transaction. Metadata includes the request ID, correlation ID, provider evidence reference and reason, with secrets and full beneficiary details removed.

Restrict update/delete permissions on audit history and define retention. A manual reconciliation resolution records the authorized operator, evidence and correction reference. Audit explains who changed a value; the ledger explains the financial entries.

21. Final Database State

These tables describe the completed illustrative main scenario. Independent failure/overpayment exercises above do not silently alter these rows. Batch counts below are derived reporting values where marked.

22. Interview Questions

23. Related SQL Concepts

Connect the sample to ACID, row locks, isolation levels, foreign keys, UNIQUE constraints, conditional UPDATE, SUM/GROUP BY, joins, NULL external references and state transitions. Use window functions to review ordered attempts while preserving each payment's business identity.