BEGIN, COMMIT, ROLLBACK in MySQL - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to start a transaction in MySQL.

MySQL

[1];

Drag options to blanks, or click blank then click option'

ACOMMIT

BROLLBACK

CBEGIN

DSAVEPOINT

Attempts:

3 left

💡 Hint

Common Mistakes

Using COMMIT to start a transaction instead of BEGIN.

Using ROLLBACK to start a transaction.

The BEGIN statement starts a new transaction in MySQL.

2fill in blank

medium

Complete the code to save changes permanently in a transaction.

MySQL

[1];

Drag options to blanks, or click blank then click option'

ABEGIN

BCOMMIT

CROLLBACK

DSAVEPOINT

Attempts:

3 left

💡 Hint

Common Mistakes

Using BEGIN instead of COMMIT to save changes.

Using ROLLBACK which cancels changes.

The COMMIT statement saves all changes made in the current transaction permanently.

3fill in blank

hard

Fix the error in the code to cancel all changes in the current transaction.

MySQL

[1];

Drag options to blanks, or click blank then click option'

AROLLBACK

BBEGIN

CCOMMIT

DSAVEPOINT

Attempts:

3 left

💡 Hint

Common Mistakes

Using COMMIT instead of ROLLBACK to cancel changes.

Using BEGIN which starts a transaction.

The ROLLBACK statement cancels all changes made in the current transaction.

4fill in blank

hard

Fill both blanks to start a transaction and then save changes.

MySQL

[1];
-- some SQL statements
[2];

Drag options to blanks, or click blank then click option'

ABEGIN

BCOMMIT

CROLLBACK

DSAVEPOINT

Attempts:

3 left

💡 Hint

Common Mistakes

Using ROLLBACK instead of COMMIT to save changes.

Using COMMIT before BEGIN.

First, BEGIN starts the transaction, then COMMIT saves the changes.

5fill in blank

hard

Fill all three blanks to start a transaction, cancel changes, and then start a new transaction.

MySQL

[1];
-- some SQL statements
[2];
[3];

Drag options to blanks, or click blank then click option'

ABEGIN

BCOMMIT

CROLLBACK

DSAVEPOINT

Attempts:

3 left

💡 Hint

Common Mistakes

Using COMMIT instead of ROLLBACK to cancel changes.

Not starting a new transaction after rollback.

First, BEGIN starts the transaction, then ROLLBACK cancels changes, and BEGIN starts a new transaction again.

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