0
0
SQLquery~20 mins

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

Choose your learning style9 modes available
LearnWhyDeepVisualTryChallengeProjectRecallTime
Challenge - 5 Problems
🎖️
Transaction Mastery
Get all challenges correct to earn this badge!
Test your skills under time pressure!
❓ query_result
intermediate
2:00remaining
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;
ABefore COMMIT, session 2 sees the new row; after COMMIT, session 2 still sees it.
BBefore COMMIT, session 2 does not see the new row; after COMMIT, session 2 sees the new row.
CBefore COMMIT, session 2 sees the new row; after COMMIT, session 2 does not see it.
DSession 2 never sees the new row, even after COMMIT.
Attempts:
2 left
💡 Hint

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

❓ query_result
intermediate
2:00remaining
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;
AThe price remains unchanged, as if the update never happened.
BThe price is updated to the new value after ROLLBACK.
CThe price is set to NULL after ROLLBACK.
DThe query causes an error because the transaction was rolled back.
Attempts:
2 left
💡 Hint

ROLLBACK undoes all changes made in the current transaction.

🧠 Conceptual
advanced
2:00remaining
Understanding transaction isolation and COMMIT

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

ACOMMIT permanently saves all changes made in the current transaction and releases locks held by it.
BCOMMIT temporarily saves changes but allows them to be rolled back later.
CCOMMIT discards all changes made in the current transaction and ends it.
DCOMMIT only saves changes if no errors occurred, otherwise it rolls back automatically.
Attempts:
2 left
💡 Hint

Think about what happens to changes and locks after COMMIT.

📝 Syntax
advanced
2:00remaining
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;
ASTART TRANSACTION; UPDATE accounts SET balance = balance - 100 WHERE id = 1; COMMIT;
BBEGIN TRANSACTION; UPDATE accounts SET balance = balance - 100 WHERE id = 1; COMMIT TRANSACTION;
CBEGIN TRANSACTION; UPDATE accounts SET balance = balance - 100 WHERE id = 1; COMMIT; ROLLBACK;
DBEGIN; UPDATE accounts SET balance = balance - 100 WHERE id = 1; ROLLBACK;
Attempts:
2 left
💡 Hint

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

🔧 Debug
expert
3:00remaining
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;
AThe update was never applied because COMMIT was missing.
BThe SELECT query is inside the transaction, so it shows old data.
CROLLBACK undoes the COMMIT, so the update is reversed.
DROLLBACK has no effect because the transaction was already committed.
Attempts:
2 left
💡 Hint

Think about the order of COMMIT and ROLLBACK commands.