Why BEGIN, COMMIT, ROLLBACK in MySQL? - Purpose & Use Cases

The Big Idea

What if you could undo a mistake in your database just like hitting Ctrl+Z in a text editor?

The Scenario

Imagine you are updating multiple records in a database by hand, one by one. If you make a mistake halfway, you have no easy way to undo the changes you already made.

The Problem

Manually fixing mistakes is slow and risky. You might forget which changes you made or accidentally leave the data inconsistent. This can cause errors and confusion.

The Solution

Using BEGIN, COMMIT, and ROLLBACK lets you group changes into a single unit. You can try all changes together, and if something goes wrong, undo everything at once. This keeps your data safe and consistent.

Before vs After

✗ Before

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

✓ After

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

What It Enables

This makes it possible to handle complex changes safely, ensuring your data stays correct even if something unexpected happens.

Real Life Example

When transferring money between bank accounts, you want to make sure both the withdrawal and deposit happen together. If one fails, you don't want to lose or create money by mistake.

Key Takeaways

BEGIN starts a group of changes called a transaction.

COMMIT saves all changes made in the transaction.

ROLLBACK undoes all changes if something goes wrong.