A funds-transfer routine debits one account and then credits another. Yesterday the server failed between the two UPDATE statements, leaving money debited but never credited. How should the routine be changed?
Select an answer to reveal the explanation.
Short Explanation
A transfer is a two-step dance: if one partner falls, both should sit down. Transaction control gives you exactly that, so either both updates stick or neither does. The trap is autocommit, which saves each step on its own, and that's precisely how you got half a transfer.
Full Explanation
Transaction Control Language statements group multiple data changes into a single atomic unit. The routine should start a transaction, run the debit and the credit, and issue COMMIT only after both statements succeed; any error triggers ROLLBACK, which undoes every change made in that transaction. If the server fails before COMMIT, the engine's recovery process rolls back the incomplete transaction on restart, so the debit cannot survive without its credit. Reversing the order of the updates does not create atomicity; a failure between them would now leave money credited but never debited, which is still an inconsistent state, just in the bank's disfavor. A trigger that reruns the credit after detecting a missing one is a fragile repair mechanism that runs after the damage and can double-credit if it misfires; it treats the symptom instead of making the unit of work atomic. Autocommit commits each statement independently as soon as it finishes, which is the exact behavior that allowed the debit to persist while the credit never ran. Exam caveat: atomicity is the A in ACID, and isolation settings separately control what concurrent sessions see during the transfer. Operational check: in a test environment, force an error after the debit, then confirm both account balances are unchanged after the rollback.