What is the purpose of the 'ROLLBACK' command in SQL?
Instruction: Using PostgreSQL, explain ROLLBACK with a disposable practice_balances table containing (id=1, value=10). Show an update that is rolled back and identify important limits of transaction rollback.
Updated
Official answer
ROLLBACK aborts the current transaction and discards its transactional changes. It does not undo a transaction that has already committed.
In this PostgreSQL example, the table initially contains id = 1, value = 10:
BEGIN;
UPDATE practice_balances SET value = 20 WHERE id = 1;
ROLLBACK;
SELECT value FROM practice_balances WHERE id = 1;
The final query returns 10. With autocommit enabled, a successful statement outside an explicit transaction is usually committed immediately, so a later rollback cannot undo it. ROLLBACK TO SAVEPOINT can instead undo part of an open transaction, according to the engine's semantics.
Do not treat rollback as reversal of every side effect. For example, PostgreSQL sequence values consumed by nextval are not reclaimed on rollback. An external message or service call also needs its own consistency strategy. Transactional DDL support varies by engine.
Assessment: Identify the transaction boundary and explain what is and is not restored, rather than describing rollback as a universal undo button.