Database Basics
In an account table, a transaction is started, and an UPDATE that subtracts 300 from account A's balance and an UPDATE that adds 300 to account B's balance are executed. Then ROLLBACK is executed instead of COMMIT. What happens to the account table?
- AOnly the addition to account B is cancelled; the subtraction from account A remains
- BBoth UPDATEs are cancelled, and the table returns to its state before the transaction started
- CBecause the UPDATEs have already been executed, neither is cancelled
Show answer and explanation
Answer: B. Both UPDATEs are cancelled, and the table returns to its state before the transaction started
A transaction is a mechanism that treats a series of operations on a database as a single unit. Updates made within a transaction become permanent when COMMIT is executed, and are all cancelled when ROLLBACK is executed, returning the data to its state before the transaction started. In this example, both the subtraction from account A and the addition to account B are cancelled, and the two balances are unchanged. If an error occurs partway through a transfer, rolling back ensures that no half-finished state with only one account updated remains. To cancel only part of the work, you can mark a point with SAVEPOINT and return to it with ROLLBACK TO SAVEPOINT.
- A. ROLLBACK cancels every update in the transaction, not only the last statement.
- B. Correct. All uncommitted updates are cancelled, returning to the state before the transaction began.
- C. Updates are not permanent before COMMIT, so ROLLBACK cancels them.