Banking Ledger

Safely transfer funds between accounts using transactions.

TransactionsStored ProceduresConstraints

Overview

Transferring money between two accounts is really two updates — subtract from one account, add to the other — and both must succeed or neither should happen. If the debit succeeds but the server crashes before the credit runs, money vanishes from the system entirely. That failure mode is exactly what a database transaction exists to prevent: wrapping both updates in `START TRANSACTION` and `COMMIT` makes them behave as a single, indivisible operation, and `ROLLBACK` undoes everything cleanly if anything goes wrong partway through — including a simple business-rule failure like insufficient funds.

By the end of this tutorial you will have an `accounts` table protected by a `CHECK` constraint that makes a negative balance structurally impossible, a `transactions` table recording every debit and credit as an immutable ledger entry, and a `CREATE PROCEDURE` that performs an entire transfer — balance check, debit, credit, and two ledger rows — as one atomic transaction, rolling back completely if the sender does not have enough funds.

What You'll Build

An `accounts` table with a `CHECK (balance >= 0)` constraint preventing negative balances.
A `transactions` table acting as an append-only ledger of every debit and credit.
A `transfer_funds` stored procedure wrapping a transfer in `START TRANSACTION` / `COMMIT`.
A `ROLLBACK` path inside the procedure that cancels the whole transfer on insufficient funds.
Verification queries proving account balances and the ledger stay consistent after a transfer.

Prerequisites

CREATE TABLE basics — columns, data types, `NOT NULL`, and `DEFAULT` values.
Primary keys and foreign keys — how `FOREIGN KEY ... REFERENCES` links two tables.
Basic SELECT, INSERT, and UPDATE statements.
The general idea of a transaction — a group of statements that should all succeed or all fail together.
Basic stored procedure syntax — `CREATE PROCEDURE`, `IN` parameters, and the `DELIMITER` change needed to define one.

Project Structure

`accounts` holds the current balance for every account — the single source of truth for "how much money does this account have right now." `transactions` never gets updated once a row is written; it only ever grows, recording history the way a real bank statement does. The stored procedure is what keeps the two in sync: every change to a balance in `accounts` happens at the same time as, and inside the same transaction as, the ledger rows that explain why.

Table	Purpose	Key Columns
accounts	Current balance per account	account_id (PK), balance (CHECK >= 0)
transactions	Append-only ledger of every debit/credit	transaction_id (PK), account_id (FK), transfer_id, type, amount

Step 1: Design the Accounts Table

The `CHECK (balance >= 0)` constraint is the last line of defense against a negative balance — even if every other safeguard in the application layer somehow failed, MySQL itself will refuse an `UPDATE` that would push `balance` below zero. `DECIMAL(12,2)`, not `FLOAT` or `DOUBLE`, is used for money throughout this schema, because binary floating-point types cannot represent most decimal fractions exactly and will eventually produce off-by-a-cent errors that a bank cannot tolerate.

CREATE TABLE accounts (
    account_id INT AUTO_INCREMENT PRIMARY KEY,   -- Surrogate key referenced by transactions.account_id
    owner_name VARCHAR(100) NOT NULL,
    balance DECIMAL(12,2) NOT NULL DEFAULT 0.00, -- DECIMAL, not FLOAT: exact arithmetic, no rounding drift on money
    CHECK (balance >= 0)                          -- The database itself refuses to let balance go negative
);

Step 2: Design the Transactions Ledger

Every transfer produces exactly two ledger rows — a `DEBIT` on the sending account and a `CREDIT` on the receiving account — sharing the same `transfer_id` so they can always be found and matched back together later. `amount` is stored as a positive number in every row; whether it increased or decreased a balance is what the `type` column records, not the sign of `amount`, which keeps every value in this append-only table simple to reason about.

CREATE TABLE transactions (
    transaction_id INT AUTO_INCREMENT PRIMARY KEY,  -- Own surrogate key for this individual ledger entry
    account_id INT NOT NULL,                         -- FK: which account this entry affects
    transfer_id INT NOT NULL,                        -- Links the DEBIT and CREDIT rows from the same transfer together
    type ENUM('DEBIT','CREDIT') NOT NULL,            -- Whether this entry decreased or increased account_id's balance
    amount DECIMAL(12,2) NOT NULL,                   -- Always positive; 'type' says the direction, not the sign
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (account_id) REFERENCES accounts(account_id) ON DELETE RESTRICT,  -- History can't be orphaned by deleting an account
    CHECK (amount > 0)                                -- A zero or negative ledger entry would be meaningless
);

Step 3: Insert Sample Accounts

Two accounts to transfer money between — one with a healthy balance, and one deliberately low, so the stored procedure's insufficient-funds path can be demonstrated in Step 5.

INSERT INTO accounts (owner_name, balance) VALUES
('Vikram Singh', 5000.00),
('Ananya Desai', 150.00);

Step 4: Write a Fund Transfer Stored Procedure

The `DELIMITER $$` line is needed because a stored procedure's body contains its own semicolons — without switching the delimiter, MySQL would treat the first semicolon inside the procedure as the end of the whole `CREATE PROCEDURE` statement. `SELECT ... FOR UPDATE` locks the sender's row for the rest of the transaction, so a second transfer running at the same time cannot read a stale balance and cause two transfers to both succeed against funds that only cover one of them. If the balance check fails, `ROLLBACK` and `SIGNAL SQLSTATE` undo everything and raise a clear error; if it passes, both balance updates and both ledger rows commit together as one atomic unit.

DELIMITER $$

CREATE PROCEDURE transfer_funds(
    IN p_from_account INT,
    IN p_to_account INT,
    IN p_amount DECIMAL(12,2)
)
BEGIN
    DECLARE current_balance DECIMAL(12,2);
    DECLARE new_transfer_id INT;

    START TRANSACTION;   -- Everything from here to COMMIT/ROLLBACK happens as one atomic unit

    -- FOR UPDATE locks this row until COMMIT/ROLLBACK, so a concurrent transfer
    -- from the same account can't read the balance before this transfer finishes
    SELECT balance INTO current_balance
    FROM accounts
    WHERE account_id = p_from_account
    FOR UPDATE;

    IF current_balance < p_amount THEN
        ROLLBACK;   -- Undo the transaction; nothing written so far takes effect
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Insufficient funds for this transfer';
    ELSE
        -- Debit the sender
        UPDATE accounts SET balance = balance - p_amount WHERE account_id = p_from_account;
        -- Credit the receiver
        UPDATE accounts SET balance = balance + p_amount WHERE account_id = p_to_account;

        -- Use this transfer's max transaction_id + 1 as a shared transfer_id linking both ledger rows
        SELECT IFNULL(MAX(transfer_id), 0) + 1 INTO new_transfer_id FROM transactions;

        INSERT INTO transactions (account_id, transfer_id, type, amount)
        VALUES (p_from_account, new_transfer_id, 'DEBIT', p_amount);

        INSERT INTO transactions (account_id, transfer_id, type, amount)
        VALUES (p_to_account, new_transfer_id, 'CREDIT', p_amount);

        COMMIT;   -- Both balance updates and both ledger rows become permanent together
    END IF;
END$$

DELIMITER ;

Why the balance CHECK constraint still matters here

The `IF current_balance < p_amount` check stops most overdrafts, but the table-level `CHECK (balance >= 0)` constraint from Step 1 is what guarantees it structurally — even a future bug in this procedure, or a totally different piece of code updating `accounts` directly, still cannot push a balance negative.

Step 5: Call the Procedure and Verify the Ledger

A successful transfer from Vikram to Ananya, followed by a failed one attempted from Ananya's now-smaller balance against too large an amount — showing both the commit path and the rollback path in the same procedure.

CALL transfer_funds(1, 2, 500.00);   -- Vikram (id 1) sends 500.00 to Ananya (id 2): succeeds

SELECT account_id, owner_name, balance FROM accounts ORDER BY account_id;

account_id	owner_name	balance
1	Vikram Singh	4500.00
2	Ananya Desai	650.00

CALL transfer_funds(2, 1, 10000.00);  -- Ananya tries to send far more than her balance: rejected

-- Error 1644 (45000): Insufficient funds for this transfer
-- Both accounts.balance values above are unchanged, and no new transactions rows were written,
-- because ROLLBACK undid the transaction before COMMIT was ever reached.

Ledger After the Successful Transfer

Click Run to see what this code prints.

Complete Schema

The full schema and stored procedure, ready to run top to bottom.

CREATE TABLE accounts (
    account_id INT AUTO_INCREMENT PRIMARY KEY,
    owner_name VARCHAR(100) NOT NULL,
    balance DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    CHECK (balance >= 0)
);

CREATE TABLE transactions (
    transaction_id INT AUTO_INCREMENT PRIMARY KEY,
    account_id INT NOT NULL,
    transfer_id INT NOT NULL,
    type ENUM('DEBIT','CREDIT') NOT NULL,
    amount DECIMAL(12,2) NOT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (account_id) REFERENCES accounts(account_id) ON DELETE RESTRICT,
    CHECK (amount > 0)
);

DELIMITER $$

CREATE PROCEDURE transfer_funds(
    IN p_from_account INT,
    IN p_to_account INT,
    IN p_amount DECIMAL(12,2)
)
BEGIN
    DECLARE current_balance DECIMAL(12,2);
    DECLARE new_transfer_id INT;

    START TRANSACTION;

    SELECT balance INTO current_balance
    FROM accounts
    WHERE account_id = p_from_account
    FOR UPDATE;

    IF current_balance < p_amount THEN
        ROLLBACK;
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Insufficient funds for this transfer';
    ELSE
        UPDATE accounts SET balance = balance - p_amount WHERE account_id = p_from_account;
        UPDATE accounts SET balance = balance + p_amount WHERE account_id = p_to_account;

        SELECT IFNULL(MAX(transfer_id), 0) + 1 INTO new_transfer_id FROM transactions;

        INSERT INTO transactions (account_id, transfer_id, type, amount)
        VALUES (p_from_account, new_transfer_id, 'DEBIT', p_amount);

        INSERT INTO transactions (account_id, transfer_id, type, amount)
        VALUES (p_to_account, new_transfer_id, 'CREDIT', p_amount);

        COMMIT;
    END IF;
END$$

DELIMITER ;

Sample Queries

Two queries that prove the ledger and the account balances agree with each other — which is exactly what the atomic procedure guarantees will always be true.

-- Full transaction history for one account, most recent first
SELECT transaction_id, transfer_id, type, amount, created_at
FROM transactions
WHERE account_id = 2
ORDER BY created_at DESC;

transaction_id	transfer_id	type	amount	created_at
2	1	CREDIT	500.00	2026-08-09 10:15:00

-- Reconcile: an account's stored balance should always equal its starting balance
-- plus every CREDIT minus every DEBIT recorded against it in the ledger
SELECT
    a.account_id,
    a.owner_name,
    a.balance AS stored_balance,
    COALESCE(SUM(CASE WHEN t.type = 'CREDIT' THEN t.amount ELSE -t.amount END), 0) AS net_ledger_movement
FROM accounts a
LEFT JOIN transactions t ON a.account_id = t.account_id
GROUP BY a.account_id, a.owner_name, a.balance;

account_id	owner_name	stored_balance	net_ledger_movement
1	Vikram Singh	4500.00	-500.00
2	Ananya Desai	650.00	500.00

Extend This Project

Add an `account_type` column (`'checking'`/`'savings'`) and enforce different overdraft rules per type.
Add a `daily_transfer_limit` column to `accounts` and check it inside `transfer_funds` before allowing a transfer.
Wrap the procedure call itself in application-level retry logic to handle lock-wait timeouts under heavy concurrency.
Add a composite index on `transactions(account_id, created_at)` to speed up per-account statement queries.
Add a `transfer_funds` audit trigger that writes a row to a separate `audit_log` table on every call.

Summary

You built a schema where a `CHECK` constraint makes a negative balance structurally impossible, and a stored procedure makes a two-account transfer atomic: `START TRANSACTION` groups the balance check, both updates, and both ledger inserts into one unit, `COMMIT` makes them permanent together, and `ROLLBACK` undoes all of them together the moment the balance check fails. That combination — a business-rule check, a lock to prevent a race with concurrent transfers, and an all-or-nothing commit — is the same shape every real payment or transfer system relies on, whether it moves money, inventory, or any other value that must never be created or destroyed by a half-finished operation.