BEGIN, COMMIT, ROLLBACK in MySQL - Step-by-Step Execution

Concept Flow - BEGIN, COMMIT, ROLLBACK

Start Transaction with BEGIN

↓

Perform SQL Operations

↓

Decision: Commit or Rollback?

↓

COMMIT: Save changes

↓

End Transaction

This flow shows starting a transaction, doing operations, then choosing to save changes with COMMIT or undo with ROLLBACK.

Execution Sample

MySQL

BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

This code starts a transaction, transfers 100 from account 1 to 2, then saves changes permanently.

Execution Table

Step	Action	SQL Command	Effect on Data	Transaction State
1	Start transaction	BEGIN;	No data changed yet	Transaction started
2	Subtract 100 from account 1	UPDATE accounts SET balance = balance - 100 WHERE id = 1;	Account 1 balance decreased by 100 (pending)	Transaction open
3	Add 100 to account 2	UPDATE accounts SET balance = balance + 100 WHERE id = 2;	Account 2 balance increased by 100 (pending)	Transaction open
4	Save changes permanently	COMMIT;	All changes saved to database	Transaction ended
5	Try to rollback after commit	ROLLBACK;	No effect, transaction already ended	No active transaction

💡 Transaction ends after COMMIT or ROLLBACK; no changes saved after rollback.

Variable Tracker

Variable	Start	After Step 2	After Step 3	After Step 4	After Step 5
account_1_balance	1000	900 (pending)	900 (pending)	900 (saved)	900 (saved)
account_2_balance	500	500 (pending)	600 (pending)	600 (saved)	600 (saved)
transaction_state	none	open	open	ended	none

Key Moments - 3 Insights

Why do the balances show 'pending' changes before COMMIT?

Because the transaction is open, changes are made but not saved permanently until COMMIT (see steps 2 and 3 in execution_table).

What happens if ROLLBACK is called after COMMIT?

No changes are undone because the transaction already ended at COMMIT (see step 5 in execution_table).

Why start with BEGIN instead of running updates directly?

BEGIN groups multiple operations so they all succeed or fail together, preventing partial updates (see step 1 in execution_table).

Visual Quiz - 3 Questions

Look at the execution_table, what is the transaction state after step 3?

A Transaction open

B Transaction ended

C No active transaction

D Transaction rolled back

💡 Hint

Check the 'Transaction State' column at step 3 in execution_table.

At which step are the changes saved permanently to the database?

A Step 2

B Step 3

C Step 4

D Step 5

💡 Hint

Look for the COMMIT command in execution_table.

If ROLLBACK was called at step 3 instead of COMMIT, what would happen to account_1_balance?

A It would stay at 900

B It would revert to 1000

C It would increase to 1100

D It would be undefined

💡 Hint

Rollback undoes changes made during the transaction before commit (see variable_tracker and key_moments).

Concept Snapshot

BEGIN starts a transaction to group operations.
COMMIT saves all changes permanently.
ROLLBACK undoes all changes in the transaction.
Use transactions to keep data consistent.
No changes are permanent until COMMIT.
ROLLBACK cancels changes if errors occur.

Full Transcript

This lesson shows how to control database changes using BEGIN, COMMIT, and ROLLBACK. First, BEGIN starts a transaction so multiple changes act as one group. Then, SQL commands update data but changes are not saved yet. You decide to COMMIT to save all changes permanently or ROLLBACK to undo them all. The execution table traces each step, showing data changes pending until commit. The variable tracker shows balances and transaction state changing step by step. Key moments clarify why changes are pending before commit and what happens if rollback is called after commit. The quiz tests understanding of transaction states and effects of commit and rollback. Remember, transactions help keep your data safe and consistent by grouping changes together.