COMMIT and ROLLBACK behavior in SQL - Step-by-Step Execution

Concept Flow - COMMIT and ROLLBACK behavior

Start Transaction

↓

Execute SQL Statements

↓

Decision: Commit or Rollback?

↓

COMMIT

↓

Save Changes

↓

End Transaction

This flow shows how a transaction starts, executes statements, then either commits to save changes or rolls back to undo them.

Execution Sample

SQL

BEGIN;
INSERT INTO accounts VALUES (1, 'Alice', 100);
UPDATE accounts SET balance = balance - 50 WHERE id = 1;
ROLLBACK;

This code starts a transaction, inserts a row, updates it, then undoes all changes with ROLLBACK.

Execution Table

Step	Action	State of accounts table	Transaction State	Effect
1	BEGIN;	No change yet	Transaction started	Ready to execute statements
2	INSERT INTO accounts VALUES (1, 'Alice', 100);	Row (1, 'Alice', 100) added (uncommitted)	Transaction open	Row added but not saved permanently
3	UPDATE accounts SET balance = balance - 50 WHERE id = 1;	Row updated to (1, 'Alice', 50) (uncommitted)	Transaction open	Balance decreased by 50, change not saved
4	ROLLBACK;	No rows (all changes undone)	Transaction ended	All changes undone, table back to original state

💡 ROLLBACK called, so all uncommitted changes are undone and transaction ends

Variable Tracker

Variable	Start	After Step 2	After Step 3	After Step 4
accounts table	empty	contains (1, 'Alice', 100) uncommitted	contains (1, 'Alice', 50) uncommitted	empty (rolled back)
transaction state	none	open	open	ended

Key Moments - 2 Insights

Why don't the changes appear in the table after ROLLBACK?

Because ROLLBACK undoes all uncommitted changes, the table returns to its original state as shown in execution_table step 4.

What happens if COMMIT is used instead of ROLLBACK?

COMMIT saves all changes permanently, so the updated rows remain in the table after the transaction ends, unlike the rollback case.

Visual Quiz - 3 Questions

Look at the execution_table at step 3, what is the balance of Alice's account?

A 100

B 0

C 50

D No row exists

💡 Hint

Check the 'State of accounts table' column at step 3 in the execution_table

At which step does the transaction end?

A Step 3

B Step 4

C Step 2

D Transaction never ends

💡 Hint

Look at the 'Transaction State' column in the execution_table to see when it changes to 'ended'

If we replace ROLLBACK with COMMIT at step 4, what will be the final state of the accounts table?

A Contains (1, 'Alice', 50)

B Contains (1, 'Alice', 100)

C Empty table

D Contains (1, 'Alice', 150)

💡 Hint

COMMIT saves the last uncommitted state shown at step 3 in the execution_table

Concept Snapshot

COMMIT and ROLLBACK control transaction changes.
BEGIN starts a transaction.
COMMIT saves all changes permanently.
ROLLBACK undoes all changes since BEGIN.
Use COMMIT to keep changes, ROLLBACK to discard.
Transactions ensure data consistency.

Full Transcript

This visual execution shows how SQL transactions work with COMMIT and ROLLBACK. First, a transaction begins with BEGIN. Then, SQL statements like INSERT and UPDATE run but their changes are not permanent yet. If ROLLBACK is called, all changes made during the transaction are undone, and the table returns to its original state. If COMMIT is called instead, all changes are saved permanently. The execution table tracks each step, showing the state of the accounts table and transaction status. The variable tracker shows how the table contents and transaction state change after each step. Key moments clarify why changes disappear after rollback and what commit does. The quiz tests understanding of balances, transaction end, and final table state after commit or rollback. This helps beginners see exactly how transactions control data changes in SQL.

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