MySQL TCL Commands — COMMIT, ROLLBACK & SAVEPOINT Guide (2026)

Introduction#

Why This Matters#

Data integrity is non-negotiable in production systems. Imagine a bank transfer: you debit one account and credit another. If a power failure occurs between those two operations, one account loses money that the other never received. Transactions prevent this by wrapping multiple SQL statements into an all-or-nothing unit — either every statement succeeds and is committed, or none of them takes effect.

MySQL's Transaction Control Language (TCL) gives you precise control over this behaviour. Every developer writing INSERT, UPDATE, or DELETE statements that depend on each other must understand TCL commands.

What Is a Transaction?#

A transaction is a sequence of one or more SQL operations treated as a single logical unit of work. Transactions follow the ACID properties:

MySQL's default storage engine, InnoDB, fully supports ACID transactions. The older MyISAM engine does not support transactions.

START TRANSACTION#

START TRANSACTION begins a new transaction block. All subsequent SQL statements are held in a pending state until you issue COMMIT or ROLLBACK.

START TRANSACTION;
-- SQL statements go here

You can also use BEGIN as an alias:

BEGIN;
-- SQL statements go here

Starting a transaction also implicitly commits any previous uncommitted transaction.

COMMIT — Saving Changes#

COMMIT permanently writes all changes made since START TRANSACTION to the database. After a commit, the changes are visible to all other sessions and cannot be undone with ROLLBACK.

START TRANSACTION;
UPDATE accounts SET balance = balance - 5000 WHERE account_id = 101;
UPDATE accounts SET balance = balance + 5000 WHERE account_id = 202;
COMMIT; -- Both updates are now permanent

ROLLBACK — Undoing Changes#

ROLLBACK cancels all changes made since the last START TRANSACTION or SAVEPOINT. The database reverts to its state before the transaction began.

START TRANSACTION;
DELETE FROM orders WHERE order_id = 55;
-- Realise this was a mistake
ROLLBACK; -- The deleted row is restored

ROLLBACK works only within an active transaction. After COMMIT, changes cannot be rolled back.

SAVEPOINT — Partial Rollbacks#

SAVEPOINT places a named marker inside a transaction. You can roll back to any savepoint without abandoning the entire transaction, which is useful in complex multi-step operations.

START TRANSACTION;
 
INSERT INTO orders (order_id, product, qty) VALUES (101, 'Laptop', 2);
SAVEPOINT after_order;
 
INSERT INTO payments (order_id, amount) VALUES (101, 2000.00);
SAVEPOINT after_payment;
 
-- Something goes wrong with shipping record
INSERT INTO shipments (order_id, address) VALUES (101, NULL);
 
-- Roll back only the shipment insert, keep order and payment
ROLLBACK TO SAVEPOINT after_payment;
 
-- Fix and re-insert
INSERT INTO shipments (order_id, address) VALUES (101, '42 Main St');
 
COMMIT;

To release a savepoint (remove it without rolling back):

RELEASE SAVEPOINT after_order;

SET AUTOCOMMIT — Controlling Auto-Commit#

By default, MySQL runs in autocommit mode: every individual SQL statement is automatically committed immediately after execution. Setting AUTOCOMMIT = 0 disables this behaviour, requiring an explicit COMMIT to save changes.

-- Disable autocommit
SET AUTOCOMMIT = 0;
 
-- Now statements are not committed until you say so
UPDATE inventory SET stock = stock - 1 WHERE product_id = 5;
UPDATE orders SET status = 'processed' WHERE order_id = 88;
COMMIT;
 
-- Re-enable autocommit
SET AUTOCOMMIT = 1;

Important: DDL commands (CREATE TABLE, ALTER TABLE, DROP TABLE) cause an implicit COMMIT in MySQL even when AUTOCOMMIT = 0. Mixing DDL inside a transaction effectively commits everything up to that point.

Complete Example — Bank Transfer Transaction#

-- Accounts table
CREATE TABLE accounts (
    account_id INT PRIMARY KEY,
    holder     VARCHAR(40),
    balance    DECIMAL(12,2) NOT NULL DEFAULT 0.00
);
 
INSERT INTO accounts VALUES (101, 'Alice', 10000.00), (202, 'Bob', 5000.00);
 
-- Transfer 3000 from Alice to Bob
START TRANSACTION;
 
UPDATE accounts SET balance = balance - 3000 WHERE account_id = 101;
 
-- Verify Alice has enough funds before proceeding
-- (In application code, check the affected rows or re-query balance here)
 
UPDATE accounts SET balance = balance + 3000 WHERE account_id = 202;
 
COMMIT;
 
-- Verify final state
SELECT account_id, holder, balance FROM accounts;

ROLLBACK with SAVEPOINT — E-Commerce Order Example#

START TRANSACTION;
 
SAVEPOINT start_order;
 
INSERT INTO orders (order_id, customer_id, total) VALUES (500, 7, 1500.00);
 
SAVEPOINT order_inserted;
 
INSERT INTO order_items (order_id, product_id, qty, price)
    VALUES (500, 12, 3, 500.00);
 
-- Simulate an error (product out of stock)
-- Application detects the problem and rolls back to before item was inserted
ROLLBACK TO SAVEPOINT order_inserted;
 
-- Try a different product
INSERT INTO order_items (order_id, product_id, qty, price)
    VALUES (500, 99, 2, 750.00);
 
COMMIT;

Common Mistakes#

• Issuing DDL inside a transaction. CREATE TABLE or ALTER TABLE inside a START TRANSACTION block triggers an implicit COMMIT, ending the transaction prematurely.
• Relying on ROLLBACK after COMMIT. Once committed, changes are permanent — there is no undo.
• Forgetting COMMIT with AUTOCOMMIT = 0. Changes remain invisible to other sessions and are lost if the connection drops.
• Not naming savepoints meaningfully. SAVEPOINT s1 is hard to read; use SAVEPOINT after_order_insert.
• Long-running open transactions. Uncommitted transactions hold locks that block other sessions. Always commit or roll back as soon as possible.

Best Practices#

• Keep transactions as short as possible to minimise lock contention.
• Always include error handling in application code: if an exception occurs, call ROLLBACK before exiting.
• Use SAVEPOINT in complex workflows where intermediate steps may fail independently.
• Never mix DDL and DML in the same transaction — DDL triggers an implicit commit.
• Test transactions in a development database and verify rollback behaviour before deploying to production.
• Use SHOW ENGINE INNODB STATUS to diagnose deadlocks and long-running transactions in production.

Key Takeaways#

• A transaction groups multiple SQL statements into one atomic unit: all succeed or none take effect.
• START TRANSACTION (or BEGIN) begins a transaction; all changes are held pending until COMMIT or ROLLBACK.
• COMMIT permanently saves all changes made in the transaction; they become visible to all sessions and cannot be rolled back.
• ROLLBACK discards all changes made since the last START TRANSACTION or SAVEPOINT, restoring the previous state.
• SAVEPOINT name creates a named checkpoint inside a transaction; ROLLBACK TO SAVEPOINT name undoes only changes made after that point.
• SET AUTOCOMMIT = 0 disables automatic per-statement commits, requiring an explicit COMMIT to persist data.
• DDL statements (CREATE TABLE, ALTER TABLE) trigger an implicit COMMIT in MySQL, even inside a transaction block.
• Only the InnoDB storage engine supports full ACID transactions in MySQL; MyISAM does not support ROLLBACK.