COMMIT and ROLLBACK behavior in SQL - Mini Project: Build & Apply

Understanding COMMIT and ROLLBACK Behavior in SQL

📖 Scenario: You are managing a simple customer database for a small store. Sometimes, you need to add new customers or update their information. But if something goes wrong, you want to undo those changes to keep the data clean.

🎯 Goal: Learn how to use COMMIT and ROLLBACK commands in SQL to save or undo changes in a database transaction.

📋 What You'll Learn

Create a table named customers with columns id (integer), name (text), and email (text).

Insert initial data into the customers table.

Start a transaction and insert a new customer.

Use ROLLBACK to undo the insertion.

Start another transaction and insert a new customer.

Use COMMIT to save the insertion.

💡 Why This Matters

🌍 Real World

In real businesses, transactions ensure data stays correct even if errors happen during updates.

💼 Career

Database administrators and developers use COMMIT and ROLLBACK to manage data safely and avoid mistakes.

1

Create the customers table and insert initial data

Write SQL commands to create a table called customers with columns id (integer), name (text), and email (text). Then insert these exact rows: (1, 'Alice', '[e-mail]') and (2, 'Bob', '[e-mail]').

SQL

-- Create the customers table and insert initial data
-- Your code here

Hint

Use CREATE TABLE to make the table and INSERT INTO to add rows.

2

Start a transaction and insert a new customer

Write SQL commands to start a transaction with BEGIN and insert a new customer with id 3, name 'Charlie', and email '[e-mail]' into the customers table.

SQL

CREATE TABLE customers (
  id INTEGER,
  name TEXT,
  email TEXT
);

INSERT INTO customers (id, name, email) VALUES
  (1, 'Alice', '[e-mail]'),
  (2, 'Bob', '[e-mail]');

-- Start a transaction and insert a new customer
-- Your code here

Hint

Use BEGIN to start the transaction and INSERT INTO to add the new row.

3

Undo the insertion with ROLLBACK

Write the SQL command to undo the last transaction using ROLLBACK.

SQL

CREATE TABLE customers (
  id INTEGER,
  name TEXT,
  email TEXT
);

INSERT INTO customers (id, name, email) VALUES
  (1, 'Alice', '[e-mail]'),
  (2, 'Bob', '[e-mail]');

BEGIN;
INSERT INTO customers (id, name, email) VALUES (3, 'Charlie', '[e-mail]');

-- Undo the insertion
-- Your code here

Hint

Use ROLLBACK to undo the changes made in the current transaction.

4

Start another transaction, insert a new customer, and save with COMMIT

Write SQL commands to start a new transaction with BEGIN, insert a new customer with id 4, name 'Diana', and email '[e-mail]', and then save the changes permanently using COMMIT.

SQL

CREATE TABLE customers (
  id INTEGER,
  name TEXT,
  email TEXT
);

INSERT INTO customers (id, name, email) VALUES
  (1, 'Alice', '[e-mail]'),
  (2, 'Bob', '[e-mail]');

BEGIN;
INSERT INTO customers (id, name, email) VALUES (3, 'Charlie', '[e-mail]');
ROLLBACK;

-- Start a new transaction, insert a new customer, and commit
-- Your code here

Hint

Use BEGIN to start, INSERT INTO to add the row, and COMMIT to save.

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