Transactions in MySQL

What Is a Transaction in MySQL?

A transaction groups related database operations into one logical unit. A school fee payment, order checkout or bank transfer may touch several rows and tables; partial completion would leave the database wrong. A transaction gives the application one final decision: COMMIT all valid changes or ROLLBACK all uncommitted changes.

Verified Bank Transaction Lab

Run schema creation before the transaction because DDL can cause an implicit commit. Both tables explicitly use InnoDB.

CREATE TABLE bank_accounts (
  account_id INT PRIMARY KEY,
  holder_name VARCHAR(60) NOT NULL,
  balance DECIMAL(12,2) NOT NULL,
  CONSTRAINT chk_nonnegative_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_positive_amount CHECK (amount > 0),
  CONSTRAINT fk_transfer_from FOREIGN KEY (from_account)
    REFERENCES bank_accounts(account_id),
  CONSTRAINT fk_transfer_to 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);

The total is a useful invariant: transferring money may change individual balances but must not create or destroy the combined 9200.00.

Transaction Commands and Boundaries

START TRANSACTION;
-- SELECT, INSERT, UPDATE and DELETE statements
COMMIT;

START TRANSACTION;
-- statements that should not be kept
ROLLBACK;

BEGIN is an alias for starting an ad-hoc transaction, but START TRANSACTION is clearer and supports transaction characteristics. In stored programs, BEGIN normally opens a compound block, so use START TRANSACTION where transaction control is permitted and appropriate.

Safe Transfer with COMMIT

Lock both account rows in a consistent order, validate the source balance, then perform the debit, credit and audit insert as one unit.

START TRANSACTION;

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

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

-- Application must confirm ROW_COUNT() = 1.
UPDATE bank_accounts
SET balance = balance + 1000.00
WHERE account_id = 102;

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

COMMIT;

The application must not blindly continue if the conditional debit affects zero rows. It should throw an error and execute ROLLBACK. The database cannot infer every business rule from SQL text alone.

Prove ROLLBACK with a Controlled Test

START TRANSACTION;

UPDATE bank_accounts
SET balance = balance - 300.00
WHERE account_id = 103;

SELECT balance FROM bank_accounts
WHERE account_id = 103;
-- Current session sees 900.00.

ROLLBACK;

SELECT balance FROM bank_accounts
WHERE account_id = 103;
-- Final balance is again 1200.00.

A full ROLLBACK also releases InnoDB locks held by the transaction. Never use rollback as an ordinary correction mechanism after data has already been committed; instead, record a compensating business transaction when history must remain auditable.

Autocommit, Session Scope and Storage Engines

MySQL sessions normally begin with autocommit = 1. Outside an explicit transaction, each successful statement is its own transaction and cannot later be grouped with the next statement.

SELECT @@autocommit;       -- normally 1
SET SESSION autocommit = 0;
-- A transaction remains open until COMMIT or ROLLBACK.
COMMIT;
SET SESSION autocommit = 1;

• Prefer explicit START TRANSACTION for a clearly bounded unit while leaving normal session behavior unchanged.
• Autocommit is per session; changing it in one connection does not change another.
• Use InnoDB tables. A ROLLBACK cannot undo changes made to a nontransactional table.
• Do not mix storage engines inside one critical transaction.
• If a connection with autocommit disabled closes without its final COMMIT, MySQL rolls back that open transaction.

Implicit Commits and Non-Rollback Traps

Many DDL and administrative statements end an active transaction implicitly. Typical examples include CREATE TABLE, ALTER TABLE, DROP TABLE, TRUNCATE TABLE and CREATE INDEX. MySQL's atomic DDL protects the DDL operation itself, but it does not make DDL part of your surrounding business transaction.

START TRANSACTION;
UPDATE bank_accounts SET balance = balance + 50
WHERE account_id = 101;

ALTER TABLE bank_accounts ADD COLUMN branch_code CHAR(4);
-- Do not expect a later ROLLBACK to undo the earlier UPDATE.

Concurrency, Error Handling and Best Practice

A normal SELECT does not reserve rows for a later update. SELECT ... FOR UPDATE takes locking reads on matching InnoDB records until COMMIT or ROLLBACK. This closes the gap between checking a balance and changing it.

1. Keep transactions short; never wait for user input while locks are held.
2. Access shared rows in the same order to reduce deadlocks.
3. Index predicates used by FOR UPDATE and UPDATE.
4. Check affected-row counts after debit, credit and ledger statements.
5. Catch every exception, ROLLBACK, and retry only transient failures such as selected deadlocks.
6. Use DECIMAL for money, never FLOAT.
7. Commit only after all business invariants are verified.

Continue with the complete transaction example, ACID properties and COMMIT, ROLLBACK and SAVEPOINT.

Official References

• MySQL 8.4: START TRANSACTION, COMMIT and ROLLBACK
• MySQL 8.4: InnoDB Autocommit, Commit and Rollback
• MySQL 8.4: Statements That Cause an Implicit Commit

Syntax and behavior were checked against the official MySQL 8.4 manual. Test the workflow, permissions and failure paths on your own server before production use.