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.