MySQL Transactions: START TRANSACTION, COMMIT, and ROLLBACK

A transaction groups related database changes so they can be committed together or rolled back. In MySQL, use START TRANSACTION to begin, then COMMIT to make the changes permanent or ROLLBACK to cancel them. For standalone row-update syntax, see MySQL UPDATE.

Transactions can undo changes only on a transactional storage engine such as InnoDB. MySQL 8.4 uses InnoDB by default unless the server is configured otherwise; see the InnoDB introduction.

MySQL transaction syntax

START TRANSACTION;

-- one or more INSERT, UPDATE, or DELETE statements

COMMIT;

If the work should be cancelled, run ROLLBACK instead of COMMIT:

ROLLBACK;

By default, MySQL has autocommit enabled, so each statement outside an explicit transaction is committed independently. START TRANSACTION begins a multi-statement transaction; autocommit returns to its previous state after COMMIT or ROLLBACK.

Example: transfer money between accounts

The example uses an InnoDB table with two accounts:

CREATE TABLE bank_accounts (
    account_id INT PRIMARY KEY,
    owner VARCHAR(50) NOT NULL,
    balance DECIMAL(10, 2) NOT NULL
) ENGINE=InnoDB;

INSERT INTO bank_accounts (account_id, owner, balance) VALUES
    (1, 'Ava', 1000.00),
    (2, 'Noah', 500.00);

Transfer 100 from Ava’s account to Noah’s account as one transaction:

START TRANSACTION;

UPDATE bank_accounts
SET balance = balance - 100.00
WHERE account_id = 1;

UPDATE bank_accounts
SET balance = balance + 100.00
WHERE account_id = 2;

COMMIT;

Assuming both accounts exist and the debit passes the application’s business checks, the balances become 900.00 and 600.00. In application code, verify the affected-row counts and any rules such as sufficient funds before committing. If a check fails, run ROLLBACK instead.

To see what rollback does, start another transaction and then cancel it:

START TRANSACTION;

UPDATE bank_accounts
SET balance = balance - 50.00
WHERE account_id = 1;

ROLLBACK;

The debit is undone, so Ava’s balance remains 900.00.

Autocommit and transaction limits

• START TRANSACTION is a clear way to group several statements while leaving the connection’s normal autocommit setting intact. You can also change autocommit for a session, but remember to finish each transaction explicitly.
• Use InnoDB or another transactional engine for changes that must be rolled back. Changes to nontransactional tables are not undone by ROLLBACK.
• Do not assume every statement error automatically rolls back the whole transaction. MySQL’s behavior depends on the error; for example, an InnoDB deadlock rolls back the transaction, while some errors roll back only the failed statement. Handle errors in the application and roll back the transaction when the overall operation must be cancelled. See InnoDB error handling.
• Statements such as CREATE TABLE and ALTER TABLE can cause an implicit commit. Create or alter tables before starting a transaction if those operations must not break the transaction boundary.
• Keep transactions focused and short so locks are not held longer than needed; see InnoDB transaction management.

Summary

• Use START TRANSACTION to begin a multi-statement unit of work.
• Use COMMIT when all operations and checks succeed; use ROLLBACK when the unit must be cancelled.
• Choose a transactional storage engine such as InnoDB and account for autocommit and implicit commits.

For exact syntax, see the MySQL 8.4 transaction statements manual, InnoDB autocommit and rollback, and statements that cause implicit commits.