Transaction Schema, SQL & Laravel

1. Business Scenario

Implementation lab: map the withdrawal scenario to MySQL constraints and a Laravel service. Examples are source code for a disposable teaching database, never migrations for this job portal.

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.

MySQL schema: accounts, requests and ledger

Teaching schema for a disposable MySQL 8.0.16+ InnoDB database. All example amounts use one currency. Production systems also need authorization, balanced ledger design, currency rules and bank integration controls. CHECK enforcement depends on the database version.

CREATE TABLE accounts (
 id BIGINT PRIMARY KEY AUTO_INCREMENT,
 account_no VARCHAR(50) NOT NULL UNIQUE,
 customer_id BIGINT NOT NULL,
 balance DECIMAL(19,2) NOT NULL DEFAULT 0,
 status VARCHAR(20) NOT NULL,
 created_at TIMESTAMP NULL,
 updated_at TIMESTAMP NULL,
 CHECK (balance >= 0)
) ENGINE=InnoDB;

CREATE TABLE withdrawal_requests (
 id BIGINT PRIMARY KEY AUTO_INCREMENT,
 account_id BIGINT NOT NULL,
 amount DECIMAL(19,2) NOT NULL,
 status VARCHAR(30) NOT NULL,
 idempotency_key VARCHAR(100) NOT NULL,
 failure_code VARCHAR(100) NULL,
 UNIQUE KEY uq_withdrawal_idempotency (account_id, idempotency_key),
 FOREIGN KEY (account_id) REFERENCES accounts(id),
 CHECK (amount > 0)
) ENGINE=InnoDB;

CREATE TABLE account_transactions (
 id BIGINT PRIMARY KEY AUTO_INCREMENT,
 account_id BIGINT NOT NULL,
 type VARCHAR(30) NOT NULL,
 amount DECIMAL(19,2) NOT NULL,
 status VARCHAR(30) NOT NULL,
 reference_no VARCHAR(100) NOT NULL UNIQUE,
 created_at TIMESTAMP NULL,
 FOREIGN KEY (account_id) REFERENCES accounts(id),
 CHECK (amount > 0)
) ENGINE=InnoDB;

Add scoped unique constraints for invoice_no, payment_reference, gateway/provider reference, idempotency_key and batch_reference. Constraints protect against concurrent requests and application bugs.

Withdrawal SQL: check affected rows before ledger insertion

The application must validate the amount and lock the idempotent request first. This fragment shows the account operation; do not run the successful branch after a zero-row update.

START TRANSACTION;
SELECT id, balance, status FROM accounts WHERE id = 101 FOR UPDATE;
UPDATE accounts SET balance = balance - 5000
WHERE id = 101 AND status = 'ACTIVE' AND balance >= 5000;
SELECT ROW_COUNT() AS debited;
-- Application: if debited != 1, ROLLBACK and stop.
-- Only the debited = 1 branch continues:
INSERT INTO account_transactions
(account_id, type, amount, status, reference_no, created_at)
VALUES (101, 'WITHDRAWAL', 5000, 'SUCCESS', 'WD-10001', NOW());
COMMIT;

If ledger insertion fails, roll back the debit. Repeated business references must return the recorded outcome rather than initiate a new debit.

Laravel: persisted amount, lock and guarded debit

Example assumes authenticated authorization for the request and validated decimal input when creating it. It performs a local ledger withdrawal, not an external bank payment. The persisted request supplies both amount and account.

DB::transaction(function () use ($requestId) {
    $request = WithdrawalRequest::whereKey($requestId)
        ->lockForUpdate()->firstOrFail();
    if ($request->status !== 'PENDING') return;

    $account = Account::whereKey($request->account_id)
        ->lockForUpdate()->firstOrFail();
    $amount = (string) $request->amount;
    if (!preg_match('/^[0-9]+(?:\.[0-9]{1,2})?$/D', $amount)
        || !preg_match('/[1-9]/', $amount)) {
        $request->update(['status' => 'FAILED', 'failure_code' => 'INVALID_AMOUNT']);
        return;
    }
    if ($account->status !== 'ACTIVE') {
        $request->update(['status' => 'FAILED', 'failure_code' => 'ACCOUNT_INACTIVE']);
        return;
    }
    // Bound SQL arithmetic preserves DECIMAL precision.
    $changed = DB::update(
        'UPDATE accounts SET balance = balance - ? WHERE id = ? AND balance >= ?',
        [$amount, $account->id, $amount]
    );
    if ($changed !== 1) {
        $request->update(['status' => 'FAILED', 'failure_code' => 'INSUFFICIENT_FUNDS']);
        return;
    }
    AccountTransaction::create([
        'account_id' => $account->id, 'type' => 'WITHDRAWAL',
        'amount' => $amount, 'status' => 'SUCCESS',
        'reference_no' => 'WD-'.$request->id,
    ]);
    $request->update(['status' => 'SUCCESS']);
});

Laravel: applying a verified student payment

Only run this flow after verifying the gateway event against the persisted payment intent, amount, currency and invoice. A durable inbox should retain the event for retries and reconciliation. Within DB::transaction: lock payment, return if SUCCESS, lock its invoice, atomically add the positive decimal amount to paid_amount and subtract from balance_amount with a balance >= amount guard, then set PAID or PARTIALLY_PAID and mark payment SUCCESS. Use bound decimal arithmetic as in the withdrawal example.

A zero-row invoice update after external success is an allocation exception, not proof of bank failure. Preserve the confirmed external payment for reconciliation or refund. Unique provider/reference constraints prevent duplicate callbacks from being credited repeatedly.

Laravel: payroll queues and receipts after commit

Approve a run only once under a row lock after checking the checker is authorized and different from the maker. Freeze totals and create durable outbox entries in the approval transaction. The outbox lets a dispatcher recover if the process crashes between commit and queue delivery.

For a simpler teaching example, Laravel supports afterCommit:

DB::transaction(function () use ($runId) {
    $run = PayrollRun::whereKey($runId)->lockForUpdate()->firstOrFail();
    if ($run->status !== 'CALCULATED') return;
    // Authorization, maker/checker and frozen-total validation precede approval.
    $run->update(['status' => 'APPROVED']);
    $run->items()->orderBy('id')->chunkById(200, function ($items) {
        foreach ($items as $item) {
            $item->update(['status' => 'READY']);
            ProcessSalaryPayment::dispatch($item->id)->afterCommit();
        }
    });
});
// In the transaction that records confirmed payment success:
GenerateFeeReceipt::dispatch($payment->id)->afterCommit();

Workers must be idempotent. afterCommit prevents workers from seeing uncommitted data, but by itself is not a durable outbox. Never blindly resubmit UNKNOWN bank payments. Keep financial PDFs private and authorize every download.

HTTP/API -> Laravel -> MySQL -> Redis Queue -> Payment / Receipt / Notification / Reconciliation workers.

References: https://laravel.com/docs/10.x/queues#jobs-and-database-transactions and https://dev.mysql.com/doc/refman/8.0/en/innodb-locking-reads.html

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.