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.