BEGIN, COMMIT, ROLLBACK in MySQL

Introduction

These commands help you control changes in your database. You can start a group of changes, save them all at once, or undo them if something goes wrong.

When you want to make several changes to the database but only save them if all succeed.

When updating multiple tables and you want to keep data correct if one update fails.

When testing changes and you want to undo them easily without affecting the data.

When you want to ensure data stays consistent during complex operations.

When you want to avoid partial updates that could cause errors or confusion.

Syntax

MySQL

START TRANSACTION;
-- your SQL statements here
COMMIT;
-- or if something goes wrong
ROLLBACK;

START TRANSACTION starts a transaction.

COMMIT saves all changes made since START TRANSACTION.

ROLLBACK undoes all changes made since START TRANSACTION.

Examples

This moves 100 from account 1 to account 2 and saves the changes.

MySQL

START TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

This tries to add an order but then cancels it, so no change happens.

MySQL

START TRANSACTION;
INSERT INTO orders (product, quantity) VALUES ('Book', 3);
ROLLBACK;

Sample Program

This example creates a simple accounts table, moves 200 from account 1 to account 2 inside a transaction, and then shows the updated balances.

MySQL

CREATE TABLE IF NOT EXISTS test_account (id INT PRIMARY KEY, balance INT);
INSERT INTO test_account (id, balance) VALUES (1, 500), (2, 300) ON DUPLICATE KEY UPDATE balance=VALUES(balance);

START TRANSACTION;
UPDATE test_account SET balance = balance - 200 WHERE id = 1;
UPDATE test_account SET balance = balance + 200 WHERE id = 2;
COMMIT;

SELECT * FROM test_account ORDER BY id;

Important Notes

Always use transactions when you need to keep data accurate across multiple changes.

If you forget COMMIT, changes may not be saved.

ROLLBACK can only undo changes after START TRANSACTION and before COMMIT.

Summary

START TRANSACTION starts a safe group of changes.

COMMIT saves all changes made in the group.

ROLLBACK cancels all changes made in the group.