BEGIN, COMMIT, ROLLBACK in MySQL - Deep Dive

Overview - BEGIN, COMMIT, ROLLBACK

What is it?

BEGIN, COMMIT, and ROLLBACK are commands used to control transactions in a database. A transaction is a group of operations treated as a single unit. BEGIN starts a transaction, COMMIT saves all changes made during the transaction, and ROLLBACK undoes those changes if something goes wrong. These commands help keep data accurate and consistent.

Why it matters

Without transactions, partial changes could be saved if an error happens, leaving data incorrect or broken. For example, transferring money between bank accounts needs both withdrawal and deposit to succeed together. BEGIN, COMMIT, and ROLLBACK ensure either all changes happen or none do, preventing mistakes and keeping trust in data.

Where it fits

Before learning transactions, you should understand basic SQL commands like SELECT, INSERT, UPDATE, and DELETE. After mastering transactions, you can learn about advanced topics like isolation levels, locking, and concurrency control to handle multiple users safely.

Mental Model

Core Idea

A transaction is like a safe box where you put changes; BEGIN opens the box, COMMIT locks it saving changes, and ROLLBACK throws away everything inside if needed.

Think of it like...

Imagine writing a letter with a pencil. BEGIN is when you start writing, COMMIT is when you press 'save' or finalize the letter, and ROLLBACK is when you erase everything if you decide not to send it.

┌─────────────┐
│ BEGIN       │  Start transaction
├─────────────┤
│ SQL Changes │  Multiple operations
├─────────────┤
│ COMMIT      │  Save all changes
│ or          │
│ ROLLBACK    │  Undo all changes
└─────────────┘

Build-Up - 7 Steps

1

Foundation

What is a Transaction in SQL

Concept: Introduce the idea of a transaction as a group of operations treated as one.

A transaction is a set of SQL commands that run together. Either all commands succeed, or none do. This keeps data safe from partial updates. For example, transferring money involves subtracting from one account and adding to another. Both must happen together.

Result

You understand that transactions group multiple steps into one safe action.

Understanding transactions is key to preventing data errors when multiple changes depend on each other.

2

Foundation

Starting a Transaction with BEGIN

Concept: Learn how to start a transaction explicitly using BEGIN.

In MySQL, you use BEGIN to tell the database you want to start a transaction. After BEGIN, all your changes are temporary until you say COMMIT or ROLLBACK. This means you can try multiple commands safely.

Result

You can control when a transaction starts and group commands together.

Knowing how to start a transaction lets you plan changes carefully before saving.

3

Intermediate

Saving Changes with COMMIT

🤔Before reading on: do you think COMMIT saves changes immediately or waits for more commands? Commit to your answer.

Concept: COMMIT finalizes all changes made since BEGIN, making them permanent.

After you finish your changes inside a transaction, you use COMMIT to save them permanently. Once committed, changes cannot be undone by ROLLBACK. This tells the database everything went well.

Result

All changes inside the transaction become permanent and visible to others.

Understanding COMMIT helps you control when data becomes official and visible.

4

Intermediate

Undoing Changes with ROLLBACK

🤔Before reading on: if you run ROLLBACK after some changes, do you think those changes stay or disappear? Commit to your answer.

Concept: ROLLBACK cancels all changes made since BEGIN, restoring data to its previous state.

If something goes wrong during a transaction, you can use ROLLBACK to undo all changes made since BEGIN. This prevents partial or incorrect data from being saved.

Result

The database returns to the state before the transaction started, as if changes never happened.

Knowing ROLLBACK protects data integrity by allowing safe recovery from errors.

5

Intermediate

Implicit vs Explicit Transactions

Concept: Explain the difference between automatic and manual transaction control.

Some SQL commands run in their own transaction automatically (implicit). Using BEGIN starts an explicit transaction where you control COMMIT or ROLLBACK. Explicit transactions give you more control over multiple related commands.

Result

You can decide when to group commands or let the database handle them one by one.

Understanding this difference helps you choose the right transaction style for your task.

6

Advanced

Transactions and Autocommit Mode

🤔Before reading on: do you think MySQL saves changes immediately by default or waits for COMMIT? Commit to your answer.

Concept: MySQL runs in autocommit mode by default, saving each command immediately unless a transaction is started.

By default, MySQL commits every single command right away. When you use BEGIN, autocommit is disabled temporarily until you COMMIT or ROLLBACK. This means you must explicitly control transactions to group commands.

Result

You learn how autocommit affects when changes are saved and how to disable it with BEGIN.

Knowing autocommit behavior prevents unexpected partial saves and helps manage transactions properly.

7

Expert

Nested Transactions and Savepoints

🤔Before reading on: do you think you can start a transaction inside another transaction in MySQL? Commit to your answer.

Concept: MySQL does not support true nested transactions but allows savepoints to partially rollback within a transaction.

You cannot start a new transaction inside an existing one. Instead, you use SAVEPOINT to mark a point inside a transaction. You can ROLLBACK to that savepoint without undoing the whole transaction. This helps manage complex operations safely.

Result

You gain fine control over parts of a transaction, improving error handling.

Understanding savepoints unlocks advanced transaction control beyond simple BEGIN/COMMIT/ROLLBACK.

Under the Hood

When you run BEGIN, MySQL marks the start of a transaction and tracks all changes in a temporary area. These changes are invisible to other users until COMMIT is called, which writes them permanently to the database files. If ROLLBACK is called, MySQL discards all tracked changes, restoring the previous state. Internally, this uses undo logs and locking to ensure data consistency and isolation.

Why designed this way?

Transactions were designed to solve problems of partial updates and concurrent access. Early databases risked data corruption if operations failed mid-way. The ACID principles (Atomicity, Consistency, Isolation, Durability) guided this design to guarantee reliable data even with crashes or multiple users. Alternatives like no transactions or manual error handling were error-prone and unsafe.

┌───────────────┐
│ BEGIN         │
│ (start txn)   │
└──────┬────────┘
       │
┌──────▼────────┐
│ Changes stored│
│ in temp area  │
└──────┬────────┘
       │
┌──────▼────────┐       ┌───────────────┐
│ COMMIT        │       │ ROLLBACK      │
│ (save to disk)│       │ (discard temp)│
└──────┬────────┘       └──────┬────────┘
       │                       │
┌──────▼────────┐       ┌──────▼────────┐
│ Changes visible│       │ Data restored │
│ to all users  │       │ to before txn │
└───────────────┘       └───────────────┘

Myth Busters - 4 Common Misconceptions

Quick: Does ROLLBACK undo changes made before BEGIN? Commit yes or no.

Common Belief: ROLLBACK can undo any changes made in the session, even before BEGIN.

Reality: ROLLBACK only undoes changes made after the most recent BEGIN or START TRANSACTION.

Why it matters: Assuming ROLLBACK undoes all changes can cause data loss or confusion when earlier changes remain committed.

Quick: Does COMMIT automatically start a new transaction? Commit yes or no.

Common Belief: COMMIT ends a transaction and immediately starts a new one.

Reality: COMMIT ends the current transaction, and no new transaction starts until BEGIN is called again.

Why it matters: Expecting automatic new transactions can lead to unintentional autocommit behavior and partial saves.

Quick: Can you nest BEGIN commands to create nested transactions? Commit yes or no.

Common Belief: You can start a transaction inside another transaction by using multiple BEGIN commands.

Reality: MySQL does not support nested transactions; multiple BEGIN commands inside a transaction have no effect.

Why it matters: Trying to nest transactions without savepoints can cause unexpected behavior and data inconsistency.

Quick: Does autocommit mode mean changes are saved only after COMMIT? Commit yes or no.

Common Belief: In autocommit mode, changes are saved only after an explicit COMMIT command.

Reality: In autocommit mode, each individual SQL command is saved immediately without needing COMMIT.

Why it matters: Misunderstanding autocommit can cause accidental partial saves and difficulty controlling transactions.

Expert Zone

1

Using SAVEPOINTs inside transactions allows partial rollbacks, which is crucial for complex error handling in multi-step operations.

2

Autocommit mode can be toggled per session or globally, affecting how transactions behave and requiring careful management in multi-user environments.

3

Some storage engines in MySQL, like MyISAM, do not support transactions, so BEGIN, COMMIT, and ROLLBACK have no effect with them.

When NOT to use

Avoid using transactions for simple, single-step queries where overhead is unnecessary. Also, do not rely on transactions with non-transactional storage engines like MyISAM; instead, use transactional engines like InnoDB.

Production Patterns

In production, transactions are used to ensure data integrity during critical operations like financial transfers, order processing, and batch updates. Developers often combine transactions with error handling and savepoints to recover gracefully from partial failures.

Connections

ACID Properties

Transactions implement the ACID principles to guarantee reliable database operations.

Understanding BEGIN, COMMIT, and ROLLBACK helps grasp how databases maintain atomicity and durability in real-world systems.

Version Control Systems

Both transactions and version control manage changes safely, allowing commits and rollbacks.

Seeing transactions like commits in version control clarifies how databases track and save changes incrementally.

Undo/Redo in Text Editors

ROLLBACK in databases is similar to undoing changes in a text editor before saving.

This connection helps understand how temporary changes can be discarded or saved permanently.

Common Pitfalls

#1 Forgetting to COMMIT after BEGIN causes changes to stay uncommitted and invisible.

Wrong approach:

BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
-- no COMMIT here

Correct approach:

BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
COMMIT;

Root cause:

Not realizing that changes inside a transaction are not saved until COMMIT is called.

#2 Using transactions with non-transactional storage engines expecting rollback support.

Wrong approach:

BEGIN;
DELETE FROM myisam_table WHERE id = 5;
ROLLBACK;

Correct approach:

Use InnoDB engine for transactional support:
ALTER TABLE myisam_table ENGINE=InnoDB;
BEGIN;
DELETE FROM myisam_table WHERE id = 5;
ROLLBACK;

Root cause:

Assuming all tables support transactions regardless of storage engine.

#3 Starting multiple BEGIN commands inside a transaction expecting nested transactions.

Wrong approach:

BEGIN;
UPDATE accounts SET balance = balance - 50 WHERE id = 1;
BEGIN;
UPDATE accounts SET balance = balance + 50 WHERE id = 2;
COMMIT;

Correct approach:

Use SAVEPOINT for partial rollback:
BEGIN;
UPDATE accounts SET balance = balance - 50 WHERE id = 1;
SAVEPOINT sp1;
UPDATE accounts SET balance = balance + 50 WHERE id = 2;
COMMIT;

Root cause:

Misunderstanding that MySQL does not support nested transactions.

Key Takeaways

BEGIN starts a transaction grouping multiple SQL commands into one unit.

COMMIT saves all changes made during the transaction permanently to the database.

ROLLBACK undoes all changes made since BEGIN, restoring the previous state.

MySQL runs in autocommit mode by default, so explicit transactions require BEGIN.

Advanced control inside transactions is possible using SAVEPOINTs for partial rollbacks.

Practice

1. What does the BEGIN statement do in MySQL transactions?

easy

A. It permanently saves all changes made to the database.

B. It starts a transaction block where changes can be grouped safely.

C. It cancels all changes made in the current transaction.

D. It deletes the current database table.

Solution

1. Step 1: Understand the role of BEGIN

   The BEGIN statement marks the start of a transaction block where multiple changes can be grouped.

2. Step 2: Differentiate from COMMIT and ROLLBACK

   COMMIT saves changes, ROLLBACK cancels changes, but BEGIN only starts the grouping.

3. Final Answer:

   It starts a transaction block where changes can be grouped safely. -> Option B

4. Quick Check:

   BEGIN starts transaction [OK]

Hint: BEGIN always starts a safe group of changes [OK]

Common Mistakes:

• Confusing BEGIN with COMMIT
• Thinking BEGIN saves changes
• Assuming BEGIN cancels changes

2. Which of the following is the correct syntax to start a transaction in MySQL?

easy

A. SET TRANSACTION;

B. BEGIN TRANSACTION;

C. BEGIN;

D. OPEN TRANSACTION;

Solution

1. Step 1: Recall MySQL transaction start syntax

   The simplest and most common syntax in MySQL to start a transaction is BEGIN;.

2. Step 2: Check options for exact syntax

   BEGIN TRANSACTION; and OPEN TRANSACTION; are not valid MySQL syntax.

3. Final Answer:

   BEGIN; -> Option C

4. Quick Check:

   BEGIN; is correct syntax [OK]

Hint: Use just BEGIN; to start a transaction in MySQL [OK]

Common Mistakes:

• Using BEGIN TRANSACTION; which is invalid in MySQL
• Confusing with other SQL dialects
• Adding extra keywords like OPEN

3. Consider this sequence of commands in MySQL:
BEGIN;
INSERT INTO users (name) VALUES ('Alice');
ROLLBACK;
SELECT * FROM users WHERE name = 'Alice';
What will the SELECT query return?

medium

A. It will return an empty result (no rows).

B. It will return the row with name 'Alice'.

C. It will cause a syntax error.

D. It will return all rows in the users table.

Solution

1. Step 1: Analyze the transaction commands

   The BEGIN; starts a transaction, then an INSERT adds 'Alice'. The ROLLBACK; cancels this change.

2. Step 2: Understand the effect of ROLLBACK

   Since the INSERT was rolled back, the row with 'Alice' was never saved.

3. Final Answer:

   It will return an empty result (no rows). -> Option A

4. Quick Check:

   ROLLBACK cancels changes [OK]

Hint: ROLLBACK undoes changes made after BEGIN [OK]

Common Mistakes:

• Assuming INSERT stays after ROLLBACK
• Thinking ROLLBACK saves changes
• Confusing ROLLBACK with COMMIT

4. You run this MySQL code:
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
COMMIT;
ROLLBACK;
What is the effect of the final ROLLBACK;?

medium

A. It undoes the UPDATE and restores the original balance.

B. It causes an error because there is no active transaction.

C. It commits the changes permanently.

D. It does nothing because COMMIT already saved changes.

Solution

1. Step 1: Follow the transaction flow

   BEGIN; starts a transaction, UPDATE changes balance, COMMIT; saves changes permanently.

2. Step 2: Understand ROLLBACK after COMMIT

   After commit, no active transaction exists, so ROLLBACK; does nothing.

3. Final Answer:

   It does nothing because COMMIT already saved changes. -> Option D

4. Quick Check:

   ROLLBACK after COMMIT has no effect [OK]

Hint: ROLLBACK after COMMIT does nothing [OK]

Common Mistakes:

• Thinking ROLLBACK can undo after COMMIT
• Expecting an error on ROLLBACK without active transaction
• Confusing order of COMMIT and ROLLBACK

5. You want to transfer $200 from account A (id=1) to account B (id=2) safely using transactions. Which sequence correctly ensures both updates happen together or not at all?

hard

A. BEGIN; UPDATE accounts SET balance = balance - 200 WHERE id = 1; UPDATE accounts SET balance = balance + 200 WHERE id = 2; COMMIT;

B. UPDATE accounts SET balance = balance - 200 WHERE id = 1; UPDATE accounts SET balance = balance + 200 WHERE id = 2;

C. BEGIN; UPDATE accounts SET balance = balance - 200 WHERE id = 1; ROLLBACK; UPDATE accounts SET balance = balance + 200 WHERE id = 2; COMMIT;

D. BEGIN; UPDATE accounts SET balance = balance - 200 WHERE id = 1; UPDATE accounts SET balance = balance + 200 WHERE id = 2; ROLLBACK;

Solution

1. Step 1: Identify the need for atomicity

   Both updates must succeed or fail together to keep balances consistent.

2. Step 2: Check each option's transaction handling

   BEGIN; UPDATE accounts SET balance = balance - 200 WHERE id = 1; UPDATE accounts SET balance = balance + 200 WHERE id = 2; COMMIT; uses BEGIN and COMMIT to group both updates safely. UPDATE accounts SET balance = balance - 200 WHERE id = 1; UPDATE accounts SET balance = balance + 200 WHERE id = 2; has no transaction, so partial updates can happen. BEGIN; UPDATE accounts SET balance = balance - 200 WHERE id = 1; ROLLBACK; UPDATE accounts SET balance = balance + 200 WHERE id = 2; COMMIT; rolls back after first update, so second update is outside transaction. BEGIN; UPDATE accounts SET balance = balance - 200 WHERE id = 1; UPDATE accounts SET balance = balance + 200 WHERE id = 2; ROLLBACK; rolls back after both updates, canceling changes.

3. Final Answer:

   BEGIN; UPDATE ...; UPDATE ...; COMMIT; -> Option A

4. Quick Check:

   Use BEGIN and COMMIT to group related updates [OK]

Hint: Group related updates between BEGIN and COMMIT [OK]

Common Mistakes:

• Not using transactions for multi-step updates
• Rolling back too early or too late
• Missing COMMIT to save changes