MySQL Banking Database Project: Accounts, Transfers

Project goal: move money between accounts so that no money is created or lost, even when something fails half way, or two transfers happen at the same time. This project is a tour of transactions, locking and constraints. (A teaching model: real banking needs regulation, audit, and much more.)

Requirements

Customers have one or more accounts, each with a balance in a currency.
A transfer takes money from one account and adds the same amount to another, atomically.
An account balance must never be negative (no overdraft in this model).
Every movement is recorded in a ledger: money in and money out, with the balance after the movement. Ledger rows are never updated or deleted.
An audit query must prove that the ledger agrees with the balances, and that total money is unchanged by transfers.

Tables and relationships

Table	Relationship
`customers` 1 — N `accounts`	A customer owns accounts
`accounts` 1 — N `ledger`	Each movement belongs to one account
`transfers` 1 — 2 `ledger`	A transfer creates two ledger rows: a debit and a credit

The schema

query.sql

SQL

CREATE TABLE customers (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(60) NOT NULL
);

CREATE TABLE accounts (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    customer_id INT UNSIGNED NOT NULL,
    number CHAR(10) NOT NULL,
    balance DECIMAL(14,2) NOT NULL DEFAULT 0,
    UNIQUE KEY uq_accounts_number (number),
    CONSTRAINT chk_balance_not_negative CHECK (balance >= 0),
    CONSTRAINT fk_accounts_customer FOREIGN KEY (customer_id) REFERENCES customers (id)
);

CREATE TABLE transfers (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    from_account INT UNSIGNED NOT NULL,
    to_account INT UNSIGNED NOT NULL,
    amount DECIMAL(14,2) NOT NULL,
    created_at DATETIME NOT NULL,
    CONSTRAINT chk_transfer_amount CHECK (amount > 0),
    CONSTRAINT chk_transfer_different CHECK (from_account <> to_account),
    CONSTRAINT fk_transfers_from FOREIGN KEY (from_account) REFERENCES accounts (id),
    CONSTRAINT fk_transfers_to FOREIGN KEY (to_account) REFERENCES accounts (id)
);

CREATE TABLE ledger (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    account_id INT UNSIGNED NOT NULL,
    transfer_id INT UNSIGNED NULL,
    amount DECIMAL(14,2) NOT NULL,
    balance_after DECIMAL(14,2) NOT NULL,
    created_at DATETIME NOT NULL,
    note VARCHAR(60) NOT NULL,
    KEY idx_ledger_account (account_id, id),
    CONSTRAINT fk_ledger_account FOREIGN KEY (account_id) REFERENCES accounts (id),
    CONSTRAINT fk_ledger_transfer FOREIGN KEY (transfer_id) REFERENCES transfers (id)
);

Output

OUTPUT

Query OK, 0 rows affected

Query OK, 0 rows affected

Query OK, 0 rows affected

Query OK, 0 rows affected

ledger.amount is positive for money in and negative for money out. CHECK (balance >= 0) is the safety net that makes an overdraft impossible, whatever the code does.

Sample data

query.sql

SQL

INSERT INTO customers (name) VALUES ('Maya'), ('Rohan'), ('Isha');
INSERT INTO accounts (customer_id, number, balance) VALUES (1, 'ACC0000001', 0), (2, 'ACC0000002', 0), (3, 'ACC0000003', 0);

-- opening deposits go through the ledger too (no transfer id)
INSERT INTO ledger (account_id, amount, balance_after, created_at, note) VALUES
(1, 1000.00, 1000.00, '2026-03-01 09:00:00', 'opening deposit'),
(2, 500.00, 500.00, '2026-03-01 09:01:00', 'opening deposit'),
(3, 250.00, 250.00, '2026-03-01 09:02:00', 'opening deposit');
UPDATE accounts SET balance = 1000.00 WHERE id = 1;
UPDATE accounts SET balance = 500.00 WHERE id = 2;
UPDATE accounts SET balance = 250.00 WHERE id = 3;

Output

OUTPUT

Query OK, 3 rows affected
Records: 3  Duplicates: 0  Warnings: 0

Query OK, 3 rows affected
Records: 3  Duplicates: 0  Warnings: 0

Query OK, 0 rows affected

Query OK, 3 rows affected
Records: 3  Duplicates: 0  Warnings: 0

Query OK, 1 row affected
Rows matched: 1  Changed: 1  Warnings: 0

Query OK, 1 row affected
Rows matched: 1  Changed: 1  Warnings: 0

Query OK, 1 row affected
Rows matched: 1  Changed: 1  Warnings: 0

The transfer procedure

The procedure locks both accounts in a fixed order (the lower id first), checks the balance, changes both balances, writes the transfer and two ledger rows, and commits. Locking in a fixed order prevents deadlocks between a transfer A to B and one B to A (Deadlocks).

query.sql

SQL

DELIMITER //

CREATE PROCEDURE transfer_money(IN p_from INT UNSIGNED, IN p_to INT UNSIGNED, IN p_amount DECIMAL(14,2), IN p_when DATETIME)
BEGIN
    DECLARE v_from_balance DECIMAL(14,2);
    DECLARE v_to_balance DECIMAL(14,2);
    DECLARE v_transfer_id INT UNSIGNED;
    DECLARE v_first INT UNSIGNED;
    DECLARE v_second INT UNSIGNED;
    DECLARE v_locked INT UNSIGNED;

    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        RESIGNAL;
    END;

    SET v_first = LEAST(p_from, p_to);
    SET v_second = GREATEST(p_from, p_to);

    START TRANSACTION;

    -- lock in a fixed order (lowest id first)
    SELECT id INTO v_locked FROM accounts WHERE id = v_first FOR UPDATE;
    SELECT id INTO v_locked FROM accounts WHERE id = v_second FOR UPDATE;

    SELECT balance INTO v_from_balance FROM accounts WHERE id = p_from;
    SELECT balance INTO v_to_balance FROM accounts WHERE id = p_to;

    IF v_from_balance < p_amount THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Insufficient funds';
    END IF;

    INSERT INTO transfers (from_account, to_account, amount, created_at) VALUES (p_from, p_to, p_amount, p_when);
    SET v_transfer_id = LAST_INSERT_ID();

    UPDATE accounts SET balance = balance - p_amount WHERE id = p_from;
    UPDATE accounts SET balance = balance + p_amount WHERE id = p_to;

    INSERT INTO ledger (account_id, transfer_id, amount, balance_after, created_at, note)
    VALUES (p_from, v_transfer_id, -p_amount, v_from_balance - p_amount, p_when, 'transfer out'),
           (p_to, v_transfer_id, p_amount, v_to_balance + p_amount, p_when, 'transfer in');

    COMMIT;
END//

DELIMITER ;

Output

OUTPUT

Query OK, 0 rows affected

Run some transfers:

query.sql

SQL

CALL transfer_money(1, 2, 300.00, '2026-03-02 10:00:00');
CALL transfer_money(2, 3, 150.50, '2026-03-02 11:00:00');
CALL transfer_money(3, 1, 75.25, '2026-03-03 09:30:00');

SELECT a.number, c.name, a.balance FROM accounts a JOIN customers c ON c.id = a.customer_id ORDER BY a.id;

Output

OUTPUT

+------------+-------+---------+
| number     | name  | balance |
+------------+-------+---------+
| ACC0000001 | Maya  |  775.25 |
| ACC0000002 | Rohan |  649.50 |
| ACC0000003 | Isha  |  325.25 |
+------------+-------+---------+
3 rows in set

Failures leave no trace

A transfer larger than the balance is refused, and nothing changes:

query.sql

SQL

CALL transfer_money(3, 1, 9999.00, '2026-03-04 08:00:00');

Output

OUTPUT

ERROR 1644 (45000): Insufficient funds

The CHECK constraint is a second layer. Even a direct, careless update cannot overdraw an account:

query.sql

SQL

UPDATE accounts SET balance = balance - 5000 WHERE id = 3;

Output

OUTPUT

ERROR 3819 (HY000): Check constraint 'chk_balance_not_negative' is violated.

A transfer to the same account, or of a negative amount, is stopped by the checks on transfers:

query.sql

SQL

CALL transfer_money(1, 1, 10.00, '2026-03-04 09:00:00');

Output

OUTPUT

ERROR 3819 (HY000): Check constraint 'chk_transfer_different' is violated.

The transfers insert failed its CHECK (from_account <> to_account), the procedure rolled back, and the locks were released. (The transfers row insert comes before the balance updates in the procedure, so the failure leaves the accounts untouched.)

Query 1: an account statement

query.sql

SQL

SELECT l.created_at, l.note, l.amount, l.balance_after
FROM ledger l JOIN accounts a ON a.id = l.account_id
WHERE a.number = 'ACC0000001'
ORDER BY l.id;

Output

OUTPUT

+---------------------+-----------------+---------+---------------+
| created_at          | note            | amount  | balance_after |
+---------------------+-----------------+---------+---------------+
| 2026-03-01 09:00:00 | opening deposit | 1000.00 |       1000.00 |
| 2026-03-02 10:00:00 | transfer out    | -300.00 |        700.00 |
| 2026-03-03 09:30:00 | transfer in     |   75.25 |        775.25 |
+---------------------+-----------------+---------+---------------+
3 rows in set

Query 2: the audit that proves the books balance

Three checks that must all come back empty or equal:

query.sql

SQL

-- 1. every account's balance equals the sum of its ledger rows
SELECT a.id, a.balance, COALESCE(SUM(l.amount), 0) AS ledger_sum
FROM accounts a LEFT JOIN ledger l ON l.account_id = a.id
GROUP BY a.id, a.balance
HAVING a.balance <> COALESCE(SUM(l.amount), 0);

-- 2. the last balance_after of each account equals its balance
SELECT a.id FROM accounts a
JOIN ledger l ON l.id = (SELECT MAX(id) FROM ledger WHERE account_id = a.id)
WHERE l.balance_after <> a.balance;

-- 3. each transfer has exactly two ledger rows that add up to zero
SELECT transfer_id, COUNT(*) AS rows_, SUM(amount) AS net FROM ledger WHERE transfer_id IS NOT NULL GROUP BY transfer_id HAVING COUNT(*) <> 2 OR SUM(amount) <> 0;

Output

OUTPUT

Empty set

Empty set

Empty set

All three return no rows. The total money in the system is unchanged by the transfers:

query.sql

SQL

SELECT SUM(balance) AS total_money FROM accounts;

Output

OUTPUT

+-------------+
| total_money |
+-------------+
|     1750.00 |
+-------------+
1 row in set

It is still 1750.00 (the sum of the three opening deposits). Transfers move money; they never create or destroy it.

Common mistakes

Reading the balance, computing in the application and writing it back. Two transfers at the same time then use the same old balance. Use balance = balance - :amount inside a locked transaction.
No row locks, or locks in a random order. Concurrent transfers corrupt balances or deadlock. Lock accounts in a fixed order (here, lowest id first).
Storing only the balance. A balance with no history cannot be audited or fixed. Keep the ledger.
Updating or deleting ledger rows. Corrections are new rows (a reversal), never edits. Give the application account INSERT and SELECT on ledger, and no UPDATE or DELETE (Privileges).
FLOAT for money. Use DECIMAL, or integer minor units.
A long transaction. The locks are held until COMMIT. Do the work first, and keep the locked part short.
Retrying nothing. Deadlocks and lock timeouts happen, and the caller should retry the whole transfer (Transactions in PHP).

Final result

query.sql

SQL

SELECT (SELECT COUNT(*) FROM transfers) AS transfers, (SELECT COUNT(*) FROM ledger) AS ledger_rows, (SELECT SUM(balance) FROM accounts) AS total_money, (SELECT MIN(balance) FROM accounts) AS lowest_balance;

Output

OUTPUT

+-----------+-------------+-------------+----------------+
| transfers | ledger_rows | total_money | lowest_balance |
+-----------+-------------+-------------+----------------+
|         3 |           9 |     1750.00 |         325.25 |
+-----------+-------------+-------------+----------------+
1 row in set

Three successful transfers were recorded (the failed attempts left nothing), the ledger has 9 rows (3 opening deposits and 2 rows for each transfer), the total money is unchanged, and the lowest balance is not negative. Next steps: different currencies with an exchange rate table, a daily balance snapshot table, a hold mechanism for pending payments, and partitioning the ledger by month for large volumes (Partitioning).