COMMIT and ROLLBACK behavior in SQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Effect of COMMIT on data visibility

Consider a transaction that inserts a new row into a table but does not commit immediately. What will another session see before and after the COMMIT?

SQL

BEGIN TRANSACTION;
INSERT INTO employees (id, name) VALUES (101, 'Alice');
-- No COMMIT yet
-- Session 2 runs: SELECT * FROM employees WHERE id = 101;
COMMIT;
-- Session 2 runs again: SELECT * FROM employees WHERE id = 101;

A Before COMMIT, session 2 sees the new row; after COMMIT, session 2 still sees it.

B Before COMMIT, session 2 does not see the new row; after COMMIT, session 2 sees the new row.

C Before COMMIT, session 2 sees the new row; after COMMIT, session 2 does not see it.

D Session 2 never sees the new row, even after COMMIT.

💡 Hint

Think about transaction isolation and when changes become visible to other sessions.

Before COMMIT, the inserted row is only visible inside the transaction that made the change. Other sessions cannot see uncommitted changes. After COMMIT, the change is saved and visible to all sessions.

❓ query_result

intermediate

ROLLBACK effect on data changes

If a transaction updates a row but then issues a ROLLBACK, what is the state of the data?

SQL

BEGIN TRANSACTION;
UPDATE products SET price = price * 1.1 WHERE id = 5;
ROLLBACK;
SELECT price FROM products WHERE id = 5;

A The price remains unchanged, as if the update never happened.

B The price is updated to the new value after ROLLBACK.

C The price is set to NULL after ROLLBACK.

D The query causes an error because the transaction was rolled back.

💡 Hint

ROLLBACK undoes all changes made in the current transaction.

ROLLBACK cancels all changes made in the transaction, so the data remains as it was before the transaction started.

🧠 Conceptual

advanced

Understanding transaction isolation and COMMIT

Which statement best describes the role of COMMIT in transaction management?

A COMMIT permanently saves all changes made in the current transaction and releases locks held by it.

B COMMIT temporarily saves changes but allows them to be rolled back later.

C COMMIT discards all changes made in the current transaction and ends it.

D COMMIT only saves changes if no errors occurred, otherwise it rolls back automatically.

💡 Hint

Think about what happens to changes and locks after COMMIT.

COMMIT finalizes all changes made in the transaction and releases any locks, making changes visible to other transactions.

📝 Syntax

advanced

Identify the error in transaction control syntax

Which of the following SQL snippets will cause a syntax error?

SQL

BEGIN TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
COMMIT TRANSACTION;
ROLLBACK;

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

B BEGIN TRANSACTION; UPDATE accounts SET balance = balance - 100 WHERE id = 1; COMMIT TRANSACTION;

C BEGIN TRANSACTION; UPDATE accounts SET balance = balance - 100 WHERE id = 1; COMMIT; ROLLBACK;

D BEGIN; UPDATE accounts SET balance = balance - 100 WHERE id = 1; ROLLBACK;

💡 Hint

Consider what happens if you try to ROLLBACK after COMMIT in the same transaction.

After COMMIT, the transaction ends. Issuing ROLLBACK without a new transaction causes a syntax or runtime error.

🔧 Debug

expert

Diagnose unexpected data after ROLLBACK

A developer runs the following sequence but notices the data was changed despite a ROLLBACK. What is the most likely cause?

BEGIN TRANSACTION;
UPDATE orders SET status = 'shipped' WHERE id = 10;
COMMIT;
ROLLBACK;
SELECT status FROM orders WHERE id = 10;

A The update was never applied because COMMIT was missing.

B The SELECT query is inside the transaction, so it shows old data.

C ROLLBACK undoes the COMMIT, so the update is reversed.

D ROLLBACK has no effect because the transaction was already committed.

💡 Hint

Think about the order of COMMIT and ROLLBACK commands.

Once COMMIT is executed, changes are permanent. ROLLBACK after COMMIT does nothing because there is no active transaction to roll back.

Practice

1. What does the COMMIT command do in a database transaction?

easy

A. It locks the database to prevent changes.

B. It undoes all changes made during the transaction.

C. It starts a new transaction.

D. It saves all changes made during the transaction permanently.

Solution

1. Step 1: Understand the purpose of COMMIT

   The COMMIT command finalizes all changes made in the current transaction and saves them permanently in the database.

2. Step 2: Compare with other options

   ROLLBACK undoes changes, starting a transaction is done by BEGIN or START TRANSACTION, and locking is a different operation.

3. Final Answer:

   It saves all changes made during the transaction permanently. -> Option D

4. Quick Check:

   COMMIT = Save changes permanently [OK]

Hint: COMMIT means save changes permanently [OK]

Common Mistakes:

• Confusing COMMIT with ROLLBACK
• Thinking COMMIT starts a transaction
• Assuming COMMIT locks the database

2. Which of the following is the correct syntax to undo changes made in the current transaction?

easy

A. COMMIT;

B. ROLLBACK;

C. UNDO;

D. SAVE;

Solution

1. Step 1: Identify the command to undo changes

   The ROLLBACK command is used to undo all changes made since the last COMMIT.

2. Step 2: Check syntax correctness

   ROLLBACK; is the correct syntax. COMMIT; saves changes, UNDO; and SAVE; are not valid SQL commands for this purpose.

3. Final Answer:

   ROLLBACK; -> Option B

4. Quick Check:

   ROLLBACK = Undo changes [OK]

Hint: ROLLBACK undoes changes, COMMIT saves them [OK]

Common Mistakes:

• Using COMMIT to undo changes
• Assuming UNDO is a valid SQL command
• Confusing SAVE with COMMIT

3. Consider this sequence of SQL commands:

BEGIN TRANSACTION;
INSERT INTO users (id, name) VALUES (1, 'Alice');
ROLLBACK;
SELECT * FROM users WHERE id = 1;

What will the SELECT query return?

medium

A. No rows

B. A row with id=1 and name='Alice'

C. Syntax error

D. All rows in the users table

Solution

1. Step 1: Analyze the transaction commands

   The INSERT adds a row with id=1 and name='Alice', but then ROLLBACK undoes this change.

2. Step 2: Understand the effect of ROLLBACK on SELECT

   Since the insert was rolled back, the row does not exist. The SELECT query will find no rows with id=1.

3. Final Answer:

   No rows -> Option A

4. Quick Check:

   ROLLBACK removes inserted rows [OK]

Hint: ROLLBACK removes changes before SELECT runs [OK]

Common Mistakes:

• Assuming inserted row remains after ROLLBACK
• Thinking ROLLBACK affects only future queries
• Confusing ROLLBACK with COMMIT

4. You run this SQL code:

BEGIN TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE id = 5;
COMMIT;
ROLLBACK;

What is the effect of the final ROLLBACK?

medium

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

B. It causes a syntax error because ROLLBACK cannot follow COMMIT.

C. It has no effect because changes were already committed.

D. It commits the changes again.

Solution

1. Step 1: Understand COMMIT effect

   COMMIT saves all changes permanently, so the UPDATE is finalized.

2. Step 2: Analyze ROLLBACK after COMMIT

   ROLLBACK after COMMIT does nothing because there is no active transaction to undo.

3. Final Answer:

   It has no effect because changes were already committed. -> Option C

4. Quick Check:

   ROLLBACK after COMMIT = no effect [OK]

Hint: ROLLBACK only undoes uncommitted changes [OK]

Common Mistakes:

• Thinking ROLLBACK undoes committed changes
• Assuming ROLLBACK causes error after COMMIT
• Confusing order of commands

5. You want to insert multiple rows into a table but only save them if all inserts succeed. Which sequence correctly uses COMMIT and ROLLBACK to achieve this?

1. BEGIN TRANSACTION;
2. INSERT INTO orders VALUES (101, 'Book');
3. INSERT INTO orders VALUES (102, 'Pen');
4. -- If both inserts succeed, then ___;
5. -- If any insert fails, then ___;

hard

A. 4: COMMIT; 5: ROLLBACK;

B. 4: ROLLBACK; 5: COMMIT;

C. 4: COMMIT; 5: COMMIT;

D. 4: ROLLBACK; 5: ROLLBACK;

Solution

1. Step 1: Understand transaction control

   To save changes only if all inserts succeed, you COMMIT after success and ROLLBACK if any fail.

2. Step 2: Match commands to conditions

   Line 4 should be COMMIT to save changes; line 5 should be ROLLBACK to undo partial changes.

3. Final Answer:

   4: COMMIT; 5: ROLLBACK; -> Option A

4. Quick Check:

   COMMIT on success, ROLLBACK on failure [OK]

Hint: COMMIT saves all; ROLLBACK undoes all [OK]

Common Mistakes:

• Swapping COMMIT and ROLLBACK roles
• Committing even if inserts fail
• Rolling back after successful inserts