MySQL Transaction Example with COMMIT and ROLLBACK

Problem: Transfer Money Without Partial Updates

We will transfer 1000.00 from Asha's account 101 to Ravi's account 102. The correct result requires four guarantees: the source exists and has enough balance; both balances change together; the combined money remains 9200.00; and one ledger row records the committed transfer.

This worked example extends the MySQL transactions foundation. It uses DECIMAL values and InnoDB, not FLOAT or a nontransactional engine.

Reproducible InnoDB Setup

Run this setup once, outside the business transaction. DDL may implicitly commit an active transaction.

DROP TABLE IF EXISTS transfer_ledger;
DROP TABLE IF EXISTS bank_accounts;

CREATE TABLE bank_accounts (
  account_id INT PRIMARY KEY,
  holder_name VARCHAR(60) NOT NULL,
  balance DECIMAL(12,2) NOT NULL,
  CONSTRAINT chk_balance CHECK (balance >= 0)
) ENGINE = InnoDB;

CREATE TABLE transfer_ledger (
  transfer_id BIGINT PRIMARY KEY,
  from_account INT NOT NULL,
  to_account INT NOT NULL,
  amount DECIMAL(12,2) NOT NULL,
  status VARCHAR(20) NOT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT chk_amount CHECK (amount > 0),
  FOREIGN KEY (from_account) REFERENCES bank_accounts(account_id),
  FOREIGN KEY (to_account) REFERENCES bank_accounts(account_id)
) ENGINE = InnoDB;

INSERT INTO bank_accounts VALUES
(101, 'Asha',  5000.00),
(102, 'Ravi',  3000.00),
(103, 'Meera', 1200.00);

SELECT account_id, holder_name, balance
FROM bank_accounts ORDER BY account_id;

SELECT SUM(balance) AS total_money
FROM bank_accounts;

The Correct Transaction Algorithm

1. Receive a unique transfer ID, source, destination and positive amount.
2. Start a transaction and lock both account rows in a deterministic order.
3. Confirm that exactly two distinct accounts exist and the source has enough funds.
4. Debit with a balance condition and require exactly one affected row.
5. Credit the destination and require exactly one affected row.
6. Insert the unique ledger record.
7. Verify invariants when appropriate, then COMMIT.
8. On any error, insufficient funds, timeout or failed check, ROLLBACK and report failure.

Successful Transfer: 101 to 102

START TRANSACTION;

SELECT account_id, balance
FROM bank_accounts
WHERE account_id IN (101, 102)
ORDER BY account_id
FOR UPDATE;
-- Returns 101 = 5000.00 and 102 = 3000.00.

UPDATE bank_accounts
SET balance = balance - 1000.00
WHERE account_id = 101
  AND balance >= 1000.00;

SELECT ROW_COUNT() AS debit_rows;
-- Must return 1. Otherwise ROLLBACK.

UPDATE bank_accounts
SET balance = balance + 1000.00
WHERE account_id = 102;

SELECT ROW_COUNT() AS credit_rows;
-- Must return 1. Otherwise ROLLBACK.

INSERT INTO transfer_ledger
  (transfer_id, from_account, to_account, amount, status)
VALUES
  (9001, 101, 102, 1000.00, 'COMMITTED');

COMMIT;

FOR UPDATE gives the transaction current locking reads. Competing sessions that need conflicting locks wait or fail according to their operation and options until this transaction ends.

Insufficient Funds: Required ROLLBACK

Meera has 1200.00, so a request for 2000.00 must leave every table unchanged.

START TRANSACTION;

SELECT account_id, balance
FROM bank_accounts
WHERE account_id IN (102, 103)
ORDER BY account_id
FOR UPDATE;

UPDATE bank_accounts
SET balance = balance - 2000.00
WHERE account_id = 103
  AND balance >= 2000.00;

SELECT ROW_COUNT() AS debit_rows;
-- Returns 0: application detects insufficient funds.

ROLLBACK;

Do not execute the credit or ledger insert after a zero-row debit. The conditional UPDATE is a database-level guard, while the application supplies the branch: success continues; failure rolls back.

Application-Side Transaction Control

A SQL console demonstrates the statements, but a real application decides whether to commit. Use the transaction methods provided by PDO, JDBC or your client library and keep one database connection for the entire unit.

begin transaction
try
    lock both account rows in sorted order
    validate two different accounts and positive amount
    debit source with balance condition
    if affected rows is not 1: throw insufficient_funds
    credit destination
    if affected rows is not 1: throw destination_error
    insert unique ledger reference
    commit
catch any error
    if transaction is active: rollback
    classify error and return a safe response

• Never catch an exception and continue to COMMIT.
• Do not return success before commit succeeds.
• Use parameterized prepared statements; never concatenate account IDs or amounts from user input.
• Do not put external API calls or long user interaction inside the locked transaction.
• Retry a deadlock with a small bounded policy and a fresh transaction, not by reusing uncertain state.

Verification and Audit Queries

SELECT account_id, holder_name, balance
FROM bank_accounts
ORDER BY account_id;

SELECT SUM(balance) AS total_money,
       MIN(balance) AS minimum_balance
FROM bank_accounts;

SELECT transfer_id, from_account, to_account,
       amount, status
FROM transfer_ledger
ORDER BY transfer_id;

The total confirms conservation of money in this closed demonstration. The CHECK constraint rejects a negative stored balance, foreign keys reject missing accounts, and the primary key rejects a duplicate transfer ID. These controls complement the transaction; they do not replace its application logic.

Production Checklist and Common Mistakes

Keep database credentials least-privileged, protect logs from sensitive data, reconcile ledger totals, monitor failed transactions and test connection loss immediately before commit. A classroom example proves SQL logic; financial production systems additionally require authentication, authorization, idempotency, fraud controls and regulatory review.

Official References

• MySQL 8.4: InnoDB Locking Reads
• MySQL 8.4: START TRANSACTION, COMMIT and ROLLBACK
• MySQL 8.4: InnoDB Deadlocks

Locking, transaction boundaries and deadlock guidance were checked against the official MySQL 8.4 manual.