BEGIN, COMMIT, ROLLBACK in MySQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

What is the output after a COMMIT?

Consider the following sequence of SQL commands executed on a MySQL database with autocommit disabled:

BEGIN;
INSERT INTO accounts (id, balance) VALUES (1, 1000);
COMMIT;
SELECT balance FROM accounts WHERE id = 1;

What will the SELECT query return?

MySQL

BEGIN;
INSERT INTO accounts (id, balance) VALUES (1, 1000);
COMMIT;
SELECT balance FROM accounts WHERE id = 1;

A NULL

B 1000

C 0

D Error: no such row

💡 Hint

Think about what COMMIT does to the changes made inside a transaction.

COMMIT saves all changes made in the transaction permanently. So the inserted row with balance 1000 is visible after commit.

❓ query_result

intermediate

What happens after a ROLLBACK?

Given this sequence:

BEGIN;
INSERT INTO accounts (id, balance) VALUES (2, 500);
ROLLBACK;
SELECT balance FROM accounts WHERE id = 2;

What will the SELECT query return?

MySQL

BEGIN;
INSERT INTO accounts (id, balance) VALUES (2, 500);
ROLLBACK;
SELECT balance FROM accounts WHERE id = 2;

A NULL

B 0

C 500

D Error: no such row

💡 Hint

ROLLBACK cancels all changes made in the current transaction.

ROLLBACK undoes the INSERT, so the row with id 2 does not exist after rollback.

🧠 Conceptual

advanced

Which statement about transactions is TRUE?

Choose the correct statement about transactions in MySQL:

A COMMIT makes all changes in the current transaction permanent.

B BEGIN starts a new transaction and locks the entire database.

C Transactions automatically commit after every SQL statement.

D ROLLBACK permanently saves all changes made in the transaction.

💡 Hint

Recall the purpose of COMMIT and ROLLBACK in transactions.

COMMIT saves all changes made in the transaction permanently. ROLLBACK undoes changes. BEGIN starts a transaction but does not lock the entire database. Autocommit mode controls automatic commits.

📝 Syntax

advanced

Identify the syntax error in this transaction block

Which option contains a syntax error in MySQL transaction commands?

Option A:
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
COMMIT;

Option B:
START TRANSACTION;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
ROLLBACK;

Option C:
BEGIN TRANSACTION;
DELETE FROM accounts WHERE id = 3;
COMMIT;

Option D:
BEGIN;
INSERT INTO accounts (id, balance) VALUES (4, 200);
COMMIT;

A o syntax error

B No syntax error

C rorre xatnys oN

D Syntax error: 'BEGIN TRANSACTION' is invalid in MySQL

💡 Hint

Check the exact syntax MySQL accepts for starting transactions.

MySQL uses either BEGIN or START TRANSACTION to start a transaction. 'BEGIN TRANSACTION' is not valid syntax in MySQL.

🔧 Debug

expert

Why does this transaction not save changes?

A developer runs this code:

BEGIN;
UPDATE accounts SET balance = balance + 100 WHERE id = 1;
-- forgot to COMMIT
SELECT balance FROM accounts WHERE id = 1;

After disconnecting and reconnecting, the balance is unchanged. Why?

A Because SELECT queries do not show uncommitted changes.

B Because the UPDATE statement has a syntax error and did not run.

C Because the transaction was never committed, so changes were lost on disconnect.

D Because autocommit was enabled, so the transaction was committed automatically.

💡 Hint

Think about what happens to uncommitted transactions when the connection closes.

If a transaction is not committed before disconnect, all changes are rolled back automatically. So the UPDATE was lost.

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