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.

Practice

1. What does the BEGIN statement do in MySQL transactions?

easy

A. It permanently saves all changes made to the database.

B. It starts a transaction block where changes can be grouped safely.

C. It cancels all changes made in the current transaction.

D. It deletes the current database table.

Solution

1. Step 1: Understand the role of BEGIN

   The BEGIN statement marks the start of a transaction block where multiple changes can be grouped.

2. Step 2: Differentiate from COMMIT and ROLLBACK

   COMMIT saves changes, ROLLBACK cancels changes, but BEGIN only starts the grouping.

3. Final Answer:

   It starts a transaction block where changes can be grouped safely. -> Option B

4. Quick Check:

   BEGIN starts transaction [OK]

Hint: BEGIN always starts a safe group of changes [OK]

Common Mistakes:

• Confusing BEGIN with COMMIT
• Thinking BEGIN saves changes
• Assuming BEGIN cancels changes

2. Which of the following is the correct syntax to start a transaction in MySQL?

easy

A. SET TRANSACTION;

B. BEGIN TRANSACTION;

C. BEGIN;

D. OPEN TRANSACTION;

Solution

1. Step 1: Recall MySQL transaction start syntax

   The simplest and most common syntax in MySQL to start a transaction is BEGIN;.

2. Step 2: Check options for exact syntax

   BEGIN TRANSACTION; and OPEN TRANSACTION; are not valid MySQL syntax.

3. Final Answer:

   BEGIN; -> Option C

4. Quick Check:

   BEGIN; is correct syntax [OK]

Hint: Use just BEGIN; to start a transaction in MySQL [OK]

Common Mistakes:

• Using BEGIN TRANSACTION; which is invalid in MySQL
• Confusing with other SQL dialects
• Adding extra keywords like OPEN

3. Consider this sequence of commands in MySQL:
BEGIN;
INSERT INTO users (name) VALUES ('Alice');
ROLLBACK;
SELECT * FROM users WHERE name = 'Alice';
What will the SELECT query return?

medium

A. It will return an empty result (no rows).

B. It will return the row with name 'Alice'.

C. It will cause a syntax error.

D. It will return all rows in the users table.

Solution

1. Step 1: Analyze the transaction commands

   The BEGIN; starts a transaction, then an INSERT adds 'Alice'. The ROLLBACK; cancels this change.

2. Step 2: Understand the effect of ROLLBACK

   Since the INSERT was rolled back, the row with 'Alice' was never saved.

3. Final Answer:

   It will return an empty result (no rows). -> Option A

4. Quick Check:

   ROLLBACK cancels changes [OK]

Hint: ROLLBACK undoes changes made after BEGIN [OK]

Common Mistakes:

• Assuming INSERT stays after ROLLBACK
• Thinking ROLLBACK saves changes
• Confusing ROLLBACK with COMMIT

4. You run this MySQL code:
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
COMMIT;
ROLLBACK;
What is the effect of the final ROLLBACK;?

medium

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

B. It causes an error because there is no active transaction.

C. It commits the changes permanently.

D. It does nothing because COMMIT already saved changes.

Solution

1. Step 1: Follow the transaction flow

   BEGIN; starts a transaction, UPDATE changes balance, COMMIT; saves changes permanently.

2. Step 2: Understand ROLLBACK after COMMIT

   After commit, no active transaction exists, so ROLLBACK; does nothing.

3. Final Answer:

   It does nothing because COMMIT already saved changes. -> Option D

4. Quick Check:

   ROLLBACK after COMMIT has no effect [OK]

Hint: ROLLBACK after COMMIT does nothing [OK]

Common Mistakes:

• Thinking ROLLBACK can undo after COMMIT
• Expecting an error on ROLLBACK without active transaction
• Confusing order of COMMIT and ROLLBACK

5. You want to transfer $200 from account A (id=1) to account B (id=2) safely using transactions. Which sequence correctly ensures both updates happen together or not at all?

hard

A. BEGIN; UPDATE accounts SET balance = balance - 200 WHERE id = 1; UPDATE accounts SET balance = balance + 200 WHERE id = 2; COMMIT;

B. UPDATE accounts SET balance = balance - 200 WHERE id = 1; UPDATE accounts SET balance = balance + 200 WHERE id = 2;

C. BEGIN; UPDATE accounts SET balance = balance - 200 WHERE id = 1; ROLLBACK; UPDATE accounts SET balance = balance + 200 WHERE id = 2; COMMIT;

D. BEGIN; UPDATE accounts SET balance = balance - 200 WHERE id = 1; UPDATE accounts SET balance = balance + 200 WHERE id = 2; ROLLBACK;

Solution

1. Step 1: Identify the need for atomicity

   Both updates must succeed or fail together to keep balances consistent.

2. Step 2: Check each option's transaction handling

   BEGIN; UPDATE accounts SET balance = balance - 200 WHERE id = 1; UPDATE accounts SET balance = balance + 200 WHERE id = 2; COMMIT; uses BEGIN and COMMIT to group both updates safely. UPDATE accounts SET balance = balance - 200 WHERE id = 1; UPDATE accounts SET balance = balance + 200 WHERE id = 2; has no transaction, so partial updates can happen. BEGIN; UPDATE accounts SET balance = balance - 200 WHERE id = 1; ROLLBACK; UPDATE accounts SET balance = balance + 200 WHERE id = 2; COMMIT; rolls back after first update, so second update is outside transaction. BEGIN; UPDATE accounts SET balance = balance - 200 WHERE id = 1; UPDATE accounts SET balance = balance + 200 WHERE id = 2; ROLLBACK; rolls back after both updates, canceling changes.

3. Final Answer:

   BEGIN; UPDATE ...; UPDATE ...; COMMIT; -> Option A

4. Quick Check:

   Use BEGIN and COMMIT to group related updates [OK]

Hint: Group related updates between BEGIN and COMMIT [OK]

Common Mistakes:

• Not using transactions for multi-step updates
• Rolling back too early or too late
• Missing COMMIT to save changes