SkillsetMaster - AI, Web Development & Data Analytics Courses

What is ROLLBACK?

ROLLBACK cancels all changes made during a transaction. It returns the database to the state it was in before BEGIN.

Think of ROLLBACK like clicking Cancel instead of Save. All the changes you made disappear as if they never happened.

How ROLLBACK Works

Basic Syntax

BEGIN;

-- Make some changes
UPDATE table SET column = value;
INSERT INTO table (columns) VALUES (values);

-- Something went wrong, cancel everything
ROLLBACK;

After ROLLBACK, it is like the UPDATE and INSERT never happened.

When to Use ROLLBACK

There are several situations where ROLLBACK is useful:

1. You made a mistake

You accidentally updated the wrong records.

BEGIN;

-- Oops, forgot the WHERE clause
UPDATE employees SET salary = 50000;
-- This changed ALL employees!

ROLLBACK;  -- Cancel, everyone keeps their original salary

2. An error occurred

One of your commands failed and you need to undo the previous commands.

BEGIN;

UPDATE accounts SET balance = balance - 100 WHERE user_id = 1;
-- This worked

UPDATE accounts SET balance = balance + 100 WHERE user_id = 999;
-- ERROR: user 999 does not exist

ROLLBACK;  -- Cancel the first UPDATE too

Without ROLLBACK, user 1 would lose 100 dollars and nobody would receive it.

3. Business logic failed

The data is valid but does not meet business requirements.

BEGIN;

-- Try to withdraw money
UPDATE accounts SET balance = balance - 500 WHERE user_id = 1;

-- Check the balance
SELECT balance FROM accounts WHERE user_id = 1;
-- Result: -200 (negative balance)

-- Business rule: no negative balances allowed
ROLLBACK;  -- Cancel the withdrawal

Real-life Example: Failed Order

A customer places an order but the product is out of stock.

BEGIN;

-- Create order
INSERT INTO orders (customer_id, product_id, quantity) VALUES (1, 100, 5);

-- Try to reduce stock
UPDATE products SET stock = stock - 5 WHERE product_id = 100;

-- Check if stock went negative
SELECT stock FROM products WHERE product_id = 100;
-- Result: -3 (not enough stock)

-- Cannot fulfill order, cancel everything
ROLLBACK;

The order is not created and the stock is not changed.

Real-life Example: Data Validation

You want to update multiple records but only if all updates are valid.

BEGIN;

UPDATE students SET grade = 'A' WHERE student_id = 1;
UPDATE students SET grade = 'B' WHERE student_id = 2;
UPDATE students SET grade = 'X' WHERE student_id = 3;
-- ERROR: 'X' is not a valid grade

ROLLBACK;  -- Cancel all updates, including student 1 and 2

ROLLBACK vs COMMIT

At the end of a transaction, you must choose one:

COMMIT - Everything worked, save all changes ROLLBACK - Something went wrong, cancel all changes

You cannot do both. Once you COMMIT, you cannot ROLLBACK. Once you ROLLBACK, there is nothing to COMMIT.

Summary

ROLLBACK cancels a transaction:

All changes made after BEGIN are discarded
Database returns to its previous state
Use when errors occur or mistakes are made
Protects data integrity when things go wrong

ROLLBACK is your safety net. It allows you to try operations knowing you can always undo them.