Account A to B Transfer

1. Business Scenario

Account A (101) starts with ₹10,000 and B (202) starts with ₹3,000, both INR and ACTIVE. Transfer 8001 moves ₹5,000 A → B; a separate concurrent transfer 8002 moves ₹2,000 B → A. Both are authorized positive-amount internal transfers.

The combined balance must remain ₹13,000. In the illustrated order, the first transfer yields A=₹5,000/B=₹8,000; the second yields A=₹7,000/B=₹6,000. A partial debit without its matching credit must never commit.

This lesson is a transfer within one database and currency. Transfers to an external bank need the separate adapter/reconciliation workflow; database atomicity does not cross that boundary.

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

Validate ownership, both statuses and same currency; disallow source=destination.

transfer_requests

A request key cannot be reused with a different amount or account pair.

account_transactions

Each completed transfer has equal DEBIT and CREDIT amounts. Also enforce UNIQUE(transfer_id,type).

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. Authorize transfer 8001
2. Validate amount/currency/account pair
3. Persist idempotent request
4. Lock request
5. Lock account 101 then 202
6. Debit source / credit destination
7. Insert both ledger entries
8. COMMIT

5. What Happens Inside the Application

The transfer controller verifies the customer controls the source and the destination is a valid recipient. The service locks transfer_requests.id=8001 and returns the saved outcome on repeated delivery.

Read source/destination IDs from the saved request, sort their numeric IDs, and lock them with two point reads in that order. Both directions therefore lock 101 before 202. Check statuses, currency and funds after locks are acquired.

Guard the source debit, credit the destination, insert both uniquely referenced ledger entries, set request SUCCESS and append audit/outbox rows. Any error rolls back every local effect. Send no external HTTP call while these account locks are held.

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 transfer_requests 8001
3. Lock accounts 101 then 202
4. Check ACTIVE, currency, positive amount, funds
5. Debit 101 by 5000
6. Credit 202 by 5000
7. Insert matched debit/credit; SUCCESS + audit
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 * FROM transfer_requests WHERE id = 8001 FOR UPDATE;
-- Both transfer directions lock account IDs in ascending order.
SELECT * FROM accounts WHERE id = 101 FOR UPDATE;
SELECT * FROM accounts WHERE id = 202 FOR UPDATE;
-- Handler checks persisted request, currency, positive amount and account status.
UPDATE accounts SET balance = balance - 5000.00 WHERE id = 101 AND balance >= 5000.00;
-- If ROW_COUNT() != 1: do not credit; roll back/record confirmed business rejection.
UPDATE accounts SET balance = balance + 5000.00 WHERE id = 202;
INSERT INTO account_transactions (id,account_id,transfer_id,type,amount,reference_no,status) VALUES
(9101,101,8001,'DEBIT',5000.00,'TR-8001-D','SUCCESS'),
(9102,202,8001,'CREDIT',5000.00,'TR-8001-C','SUCCESS');
UPDATE transfer_requests SET status = 'SUCCESS' WHERE id = 8001;
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 transfer request first, then account 101 and 202 in ascending order for both directions. READ COMMITTED or REPEATABLE READ does not fix an application that performs unlocked balance decisions.

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

If the destination credit or either ledger insert fails, rollback the source debit as well. Inactive account, currency mismatch, same-account transfer and insufficient funds are distinct validation/business failures. Never record a debit-only SUCCESS.

15. Retry Flow

Use TR-CLIENT-001 again for a retry of transfer 8001. A bounded deadlock retry restarts the entire local transaction, re-reads locked balances and inserts no duplicate ledger entries. The original request ID and unique ledger pair prevent repeated effects.

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)

GenerateTransferReceipt::dispatch($transfer->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

Transfer acknowledgement TR-8001 shows source ending A, destination ending B, ₹5,000 INR and SUCCESS. Render from the committed request and its two ledger entries. Do not expose another customer's private account data.

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

For each transfer, sum DEBIT and CREDIT amounts and require equality. Across the two example accounts the balance remains ₹13,000. Investigate any successful request with fewer than two ledger entries or any unmatched account movement.

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.