A banking application runs two UPDATE statements to transfer money: first debiting Alice's account, then crediting Bob's. There is no explicit transaction — the database is in auto-commit mode. A server crash occurs between the two statements. What is the outcome?
ABoth statements are rolled back automatically because the operation was incomplete
BAlice's account is debited but Bob's is never credited — the $100 is lost
CThe database retries both statements when the server restarts
DThe credit to Bob is applied when the server restarts, completing the transfer
In auto-commit mode, each statement is its own independent transaction. The first UPDATE (debit) committed immediately and is permanent. The second UPDATE (credit) never ran due to the crash and there is no transaction to roll back. The result is a partial state: money leaves Alice's account but never arrives in Bob's. This is precisely the problem transactions solve. Without an explicit BEGIN wrapping both statements, atomicity across multiple operations does not exist.
Question 2 Multiple Choice
A developer wraps two UPDATE statements in a transaction with BEGIN, but the application crashes before the COMMIT is issued. What does the database do?
ACommits the changes that completed successfully before the crash
BLeaves the database in a partial state until the developer manually cleans it up
CAutomatically rolls back all changes made since BEGIN, restoring the original state
DCommits the changes on the next successful connection to the database
Transaction atomicity guarantees that if anything goes wrong before COMMIT — a crash, a constraint violation, an application error — the database rolls back all changes made since BEGIN. This is the core promise: there is no partial state. The changes either fully complete and commit, or they are entirely undone. The 'all or nothing' guarantee is what makes multi-step database operations reliable in the presence of failures.
Question 3 True / False
Issuing ROLLBACK inside a transaction undoes all changes made since the corresponding BEGIN.
TTrue
FFalse
Answer: True
ROLLBACK discards all modifications made to the database since the transaction began with BEGIN, restoring the database to its state at that point. This is the complement of COMMIT, which makes changes permanent. The undo capability is what gives transactions their recovery power — if any step of a multi-statement operation fails, ROLLBACK returns the database to a known-good state, leaving no partial effects.
Question 4 True / False
In most relational databases, a ROLLBACK issued inside a transaction will undo a CREATE TABLE or DROP TABLE statement that was executed within that transaction.
TTrue
FFalse
Answer: False
Most databases (including PostgreSQL exceptions aside, and notably MySQL/Oracle) auto-commit DDL statements — CREATE TABLE, DROP TABLE, ALTER TABLE — immediately regardless of transaction boundaries. This means DDL cannot be rolled back by a subsequent ROLLBACK in most systems. PostgreSQL is an exception and does support transactional DDL. This is an important practical trap: if you run DROP TABLE inside what you think is a safe transaction, you may discover the DDL committed immediately and cannot be undone.
Question 5 Short Answer
What specific problem does transaction atomicity solve, and what is the concrete consequence of running multi-step database operations without it?
Think about your answer, then reveal below.
Model answer: Atomicity ensures that a sequence of database operations either all succeed and commit, or all fail and are rolled back — no partial state can exist. Without it, a failure midway through a multi-step operation (like a bank transfer) leaves the database in an inconsistent state: one change persists while the corresponding change does not, violating the real-world invariant the operations were meant to maintain.
The bank transfer example makes this concrete: subtract from Alice, add to Bob must be atomic — both happen or neither does. Without a transaction wrapping both, a crash between the two statements permanently corrupts the data. The database has no way to know the two operations were related. Transactions provide that relationship: the BEGIN announces 'these operations belong together,' and the atomicity guarantee enforces it.