BEGIN, COMMIT, ROLLBACK in MySQL

Introduction

These commands help you control changes in your database. You can start a group of changes, save them all at once, or undo them if something goes wrong.

When you want to make several changes to the database but only save them if all succeed.

When updating multiple tables and you want to keep data correct if one update fails.

When testing changes and you want to undo them easily without affecting the data.

When you want to ensure data stays consistent during complex operations.

When you want to avoid partial updates that could cause errors or confusion.

Syntax

MySQL

START TRANSACTION;
-- your SQL statements here
COMMIT;
-- or if something goes wrong
ROLLBACK;

START TRANSACTION starts a transaction.

COMMIT saves all changes made since START TRANSACTION.

ROLLBACK undoes all changes made since START TRANSACTION.

Examples

This moves 100 from account 1 to account 2 and saves the changes.

MySQL

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

This tries to add an order but then cancels it, so no change happens.

MySQL

START TRANSACTION;
INSERT INTO orders (product, quantity) VALUES ('Book', 3);
ROLLBACK;

Sample Program

This example creates a simple accounts table, moves 200 from account 1 to account 2 inside a transaction, and then shows the updated balances.

MySQL

CREATE TABLE IF NOT EXISTS test_account (id INT PRIMARY KEY, balance INT);
INSERT INTO test_account (id, balance) VALUES (1, 500), (2, 300) ON DUPLICATE KEY UPDATE balance=VALUES(balance);

START TRANSACTION;
UPDATE test_account SET balance = balance - 200 WHERE id = 1;
UPDATE test_account SET balance = balance + 200 WHERE id = 2;
COMMIT;

SELECT * FROM test_account ORDER BY id;

Important Notes

Always use transactions when you need to keep data accurate across multiple changes.

If you forget COMMIT, changes may not be saved.

ROLLBACK can only undo changes after START TRANSACTION and before COMMIT.

Summary

START TRANSACTION starts a safe group of changes.

COMMIT saves all changes made in the group.

ROLLBACK cancels all changes made in the group.

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