Why COMMIT and ROLLBACK behavior in SQL? - Purpose & Use Cases

The Big Idea

What if you could undo mistakes in your database as easily as pressing Ctrl+Z?

The Scenario

Imagine you are updating a long list of customer orders in a notebook. If you make a mistake halfway, you have to erase everything manually or start over. There is no easy way to undo just the last few changes.

The Problem

Manually tracking changes is slow and risky. You might miss errors or accidentally keep wrong data. Fixing mistakes means rewriting or erasing many pages, which wastes time and causes frustration.

The Solution

COMMIT and ROLLBACK let you control when changes become permanent or get undone. You can try updates safely, and if something goes wrong, just ROLLBACK to the previous correct state without hassle.

Before vs After

✗ Before

Update orders one by one and rewrite if error found

✓ After

BEGIN TRANSACTION; UPDATE orders SET status='shipped'; -- if error ROLLBACK; else COMMIT;

What It Enables

This behavior lets you make safe, reversible changes to your data, avoiding costly mistakes and keeping your database reliable.

Real Life Example

When booking a flight, the system updates seat availability and payment info together. If payment fails, ROLLBACK cancels the seat hold, preventing errors.

Key Takeaways

Manual updates are error-prone and hard to fix.

COMMIT saves changes permanently.

ROLLBACK undoes changes to keep data safe.

Practice

1. What does the COMMIT command do in a database transaction?

easy

A. It locks the database to prevent changes.

B. It undoes all changes made during the transaction.

C. It starts a new transaction.

D. It saves all changes made during the transaction permanently.

Solution

1. Step 1: Understand the purpose of COMMIT

   The COMMIT command finalizes all changes made in the current transaction and saves them permanently in the database.

2. Step 2: Compare with other options

   ROLLBACK undoes changes, starting a transaction is done by BEGIN or START TRANSACTION, and locking is a different operation.

3. Final Answer:

   It saves all changes made during the transaction permanently. -> Option D

4. Quick Check:

   COMMIT = Save changes permanently [OK]

Hint: COMMIT means save changes permanently [OK]

Common Mistakes:

• Confusing COMMIT with ROLLBACK
• Thinking COMMIT starts a transaction
• Assuming COMMIT locks the database

2. Which of the following is the correct syntax to undo changes made in the current transaction?

easy

A. COMMIT;

B. ROLLBACK;

C. UNDO;

D. SAVE;

Solution

1. Step 1: Identify the command to undo changes

   The ROLLBACK command is used to undo all changes made since the last COMMIT.

2. Step 2: Check syntax correctness

   ROLLBACK; is the correct syntax. COMMIT; saves changes, UNDO; and SAVE; are not valid SQL commands for this purpose.

3. Final Answer:

   ROLLBACK; -> Option B

4. Quick Check:

   ROLLBACK = Undo changes [OK]

Hint: ROLLBACK undoes changes, COMMIT saves them [OK]

Common Mistakes:

• Using COMMIT to undo changes
• Assuming UNDO is a valid SQL command
• Confusing SAVE with COMMIT

3. Consider this sequence of SQL commands:

BEGIN TRANSACTION;
INSERT INTO users (id, name) VALUES (1, 'Alice');
ROLLBACK;
SELECT * FROM users WHERE id = 1;

What will the SELECT query return?

medium

A. No rows

B. A row with id=1 and name='Alice'

C. Syntax error

D. All rows in the users table

Solution

1. Step 1: Analyze the transaction commands

   The INSERT adds a row with id=1 and name='Alice', but then ROLLBACK undoes this change.

2. Step 2: Understand the effect of ROLLBACK on SELECT

   Since the insert was rolled back, the row does not exist. The SELECT query will find no rows with id=1.

3. Final Answer:

   No rows -> Option A

4. Quick Check:

   ROLLBACK removes inserted rows [OK]

Hint: ROLLBACK removes changes before SELECT runs [OK]

Common Mistakes:

• Assuming inserted row remains after ROLLBACK
• Thinking ROLLBACK affects only future queries
• Confusing ROLLBACK with COMMIT

4. You run this SQL code:

BEGIN TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE id = 5;
COMMIT;
ROLLBACK;

What is the effect of the final ROLLBACK?

medium

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

B. It causes a syntax error because ROLLBACK cannot follow COMMIT.

C. It has no effect because changes were already committed.

D. It commits the changes again.

Solution

1. Step 1: Understand COMMIT effect

   COMMIT saves all changes permanently, so the UPDATE is finalized.

2. Step 2: Analyze ROLLBACK after COMMIT

   ROLLBACK after COMMIT does nothing because there is no active transaction to undo.

3. Final Answer:

   It has no effect because changes were already committed. -> Option C

4. Quick Check:

   ROLLBACK after COMMIT = no effect [OK]

Hint: ROLLBACK only undoes uncommitted changes [OK]

Common Mistakes:

• Thinking ROLLBACK undoes committed changes
• Assuming ROLLBACK causes error after COMMIT
• Confusing order of commands

5. You want to insert multiple rows into a table but only save them if all inserts succeed. Which sequence correctly uses COMMIT and ROLLBACK to achieve this?

1. BEGIN TRANSACTION;
2. INSERT INTO orders VALUES (101, 'Book');
3. INSERT INTO orders VALUES (102, 'Pen');
4. -- If both inserts succeed, then ___;
5. -- If any insert fails, then ___;

hard

A. 4: COMMIT; 5: ROLLBACK;

B. 4: ROLLBACK; 5: COMMIT;

C. 4: COMMIT; 5: COMMIT;

D. 4: ROLLBACK; 5: ROLLBACK;

Solution

1. Step 1: Understand transaction control

   To save changes only if all inserts succeed, you COMMIT after success and ROLLBACK if any fail.

2. Step 2: Match commands to conditions

   Line 4 should be COMMIT to save changes; line 5 should be ROLLBACK to undo partial changes.

3. Final Answer:

   4: COMMIT; 5: ROLLBACK; -> Option A

4. Quick Check:

   COMMIT on success, ROLLBACK on failure [OK]

Hint: COMMIT saves all; ROLLBACK undoes all [OK]

Common Mistakes:

• Swapping COMMIT and ROLLBACK roles
• Committing even if inserts fail
• Rolling back after successful inserts