MySQL Transactions: START TRANSACTION, COMMIT and ROLLBACK

This article is a localized edition of the Japanese original. Read the Japanese original

A MySQL transaction groups several SQL statements so you can either apply all of them (COMMIT) or undo all of them (ROLLBACK). Use one whenever applying only part of the work would be a problem, such as a money transfer where only one of two UPDATEs goes through. Everything in this article was checked against the MySQL 8.4 reference manual as of November 2026, and the SQL was run on MySQL 8.4.

Prerequisite: transactions only work on InnoDB tables

Transactions are supported by transactional storage engines such as InnoDB. InnoDB has been the default engine since MySQL 5.5. Changes to MyISAM tables are saved immediately and are not undone by ROLLBACK (you only get a warning).

-- Check the engine of each table
SELECT TABLE_NAME, ENGINE
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'testdb';

If you still have MyISAM tables, see The difference between InnoDB and MyISAM.

The examples below use this table.

CREATE DATABASE IF NOT EXISTS testdb;
USE testdb;

CREATE TABLE accounts (
  id      INT PRIMARY KEY,
  name    VARCHAR(50) NOT NULL,
  balance INT NOT NULL
) ENGINE=InnoDB;

INSERT INTO accounts VALUES (1, 'Alice', 5000), (2, 'Bob', 1000);

The basic flow: START TRANSACTION → SQL → COMMIT/ROLLBACK

Connect with the mysql client (mysql -u username -p testdb) and run the following.

START TRANSACTION;

UPDATE accounts SET balance = balance - 1000 WHERE id = 1;
UPDATE accounts SET balance = balance + 1000 WHERE id = 2;

COMMIT;

Until you run COMMIT, the changes are visible only to your own connection. To abandon the work, run ROLLBACK instead of COMMIT, and every change made since START TRANSACTION is undone.

START TRANSACTION;
UPDATE accounts SET balance = 0 WHERE id = 1;
SELECT * FROM accounts;   -- this connection sees 0
ROLLBACK;
SELECT * FROM accounts;   -- back to the original values

BEGIN and BEGIN WORK are aliases for START TRANSACTION. Inside stored procedures and triggers, however, BEGIN is parsed as the start of a BEGIN ... END block, so you start a transaction there with START TRANSACTION. If in doubt, it is simplest to use START TRANSACTION everywhere, since it works in every context.

If a connection is closed without committing, InnoDB rolls back its transaction.

autocommit: by default every statement commits on its own

MySQL has autocommit enabled by default. A statement you run without START TRANSACTION is committed automatically as soon as it finishes, and you cannot undo it later with ROLLBACK.

SELECT @@autocommit;   -- 1 means enabled

With SET autocommit = 0;, nothing is final on that connection until you run COMMIT. However, a connection that forgets to commit keeps holding its locks, so we recommend wrapping only the work that needs it in START TRANSACTION instead.

SAVEPOINT: roll back part of the way

You can create a named point inside a transaction and undo only the work done after it.

START TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
SAVEPOINT step1;
UPDATE accounts SET balance = balance + 100 WHERE id = 99;  -- mistake
ROLLBACK TO SAVEPOINT step1;   -- undo only what came after step1
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

Statements that cannot be rolled back (implicit commits)

DDL statements such as CREATE TABLE, ALTER TABLE, DROP TABLE and TRUNCATE TABLE, and account-management statements such as CREATE USER and GRANT, automatically commit the current transaction before they run. If you put DDL in the middle of a transaction, the changes made up to that point can no longer be undone with ROLLBACK.

Also, running START TRANSACTION while a transaction is open commits the open one. Transactions do not nest (use SAVEPOINT if you need to roll back part of the way).

Concurrent updates: row locks and SELECT ... FOR UPDATE

InnoDB locks the rows changed by UPDATE or DELETE and holds those locks until COMMIT or ROLLBACK. Another connection that tries to update the same rows waits until the locks are released.

When you read a value and then change it, such as checking a balance before debiting it, add FOR UPDATE to the SELECT that does the check so that no other connection can change the same row at the same time.

START TRANSACTION;
SELECT balance FROM accounts WHERE id = 1 FOR UPDATE;  -- lock this row
-- after the application confirms the balance is sufficient
UPDATE accounts SET balance = balance - 1000 WHERE id = 1;
COMMIT;

A plain SELECT without FOR UPDATE reads a snapshot without taking locks. InnoDB's default isolation level is REPEATABLE READ, so repeating a plain SELECT within the same transaction returns the data as it was at the first read.

SELECT @@transaction_isolation;   -- REPEATABLE-READ

Lock wait timeouts and deadlocks

Deadlocks become more likely when different code paths update multiple rows in different orders. Update rows in a consistent order, keep transactions short, and make the application restart the whole transaction when it gets error 1213. You can see the details of the most recent deadlock in the LATEST DETECTED DEADLOCK section of SHOW ENGINE INNODB STATUS\G. For more, see How to resolve MySQL deadlocks.

Writing rollback-on-error logic

SQL IF ... THEN ... END IF works only inside stored programs such as stored procedures. Typing it directly into the mysql client gives a syntax error. If you need to choose between commit and rollback based on a condition, use a stored procedure or write the logic in your application.

In a stored procedure

The procedure below raises an error when the balance is insufficient or the destination account does not exist, and when any error occurs it rolls back and passes the error on to the caller.

DELIMITER //

CREATE PROCEDURE transfer(IN from_id INT, IN to_id INT, IN amount INT)
BEGIN
  DECLARE cur_balance INT DEFAULT NULL;

  -- on any error, roll back and re-raise the error unchanged
  DECLARE EXIT HANDLER FOR SQLEXCEPTION
  BEGIN
    ROLLBACK;
    RESIGNAL;
  END;

  START TRANSACTION;

  SELECT balance INTO cur_balance FROM accounts WHERE id = from_id FOR UPDATE;
  IF cur_balance IS NULL OR cur_balance < amount THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'insufficient balance or no account';
  END IF;

  UPDATE accounts SET balance = balance - amount WHERE id = from_id;
  UPDATE accounts SET balance = balance + amount WHERE id = to_id;
  IF ROW_COUNT() = 0 THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'destination account not found';
  END IF;

  COMMIT;
END//

DELIMITER ;

CALL transfer(1, 2, 500);    -- succeeds and commits
CALL transfer(1, 2, 99999);  -- ERROR 1644: insufficient balance or no account (no changes)
CALL transfer(1, 99, 100);   -- ERROR 1644: destination account not found (the debit is undone too)

DELIMITER // is a feature of the mysql client that stops it from treating each ; inside the procedure as the end of the statement. In tools such as phpMyAdmin, set the delimiter in the "Delimiter" field on the screen instead.

In PHP (PDO)

From an application, use the driver's transaction methods. Since PHP 8.0, PDO throws exceptions on errors by default (the example sets it explicitly anyway).

<?php
$pdo = new PDO(
	'mysql:host=localhost;dbname=testdb;charset=utf8mb4',
	'app_user',
	'password',
	[ PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION ]
);

function transfer( PDO $pdo, int $from, int $to, int $amount ): void {
	$pdo->beginTransaction();
	try {
		$stmt = $pdo->prepare( 'SELECT balance FROM accounts WHERE id = ? FOR UPDATE' );
		$stmt->execute( [ $from ] );
		$balance = $stmt->fetchColumn();
		if ( false === $balance || (int) $balance < $amount ) {
			throw new RuntimeException( 'insufficient balance or no account' );
		}

		$pdo->prepare( 'UPDATE accounts SET balance = balance - ? WHERE id = ?' )
			->execute( [ $amount, $from ] );
		$stmt = $pdo->prepare( 'UPDATE accounts SET balance = balance + ? WHERE id = ?' );
		$stmt->execute( [ $amount, $to ] );
		if ( 0 === $stmt->rowCount() ) {
			throw new RuntimeException( 'destination account not found' );
		}

		$pdo->commit();
	} catch ( Throwable $e ) {
		$pdo->rollBack();
		throw $e;
	}
}

transfer( $pdo, 1, 2, 500 );

Pass values through placeholders (?) rather than embedding them directly in the SQL string.

Operational notes

• Keep transactions short. While a transaction is open it keeps holding its row locks and makes other work wait. Do not wait for user input or call external APIs inside a transaction.
• You can find transactions that have been open for a long time with the following SQL.

SELECT trx_id, trx_started, trx_mysql_thread_id, trx_query
FROM information_schema.INNODB_TRX
ORDER BY trx_started;

• Once you know which connection is responsible, decide whether to stop it (disconnecting it with KILL thread_id; rolls back that connection's transaction).

Summary

• To make several updates "all or nothing", start with START TRANSACTION and finish with COMMIT or ROLLBACK. The tables involved must be InnoDB.
• autocommit is enabled by default, so each statement is committed on its own.
• DDL and similar statements commit implicitly, so do not put them in the middle of a transaction.
• When you read and then change data, lock the rows with SELECT ... FOR UPDATE. If you get a deadlock (1213), retry.
• Write conditional rollback logic in a stored procedure (DECLARE ... HANDLER) or in the application (PDO's beginTransaction/commit/rollBack).

Questions about WordPress? The publisher of this site is based in Japan and replies in Japanese. Contact (Japanese)