Part 2 of 4 · 1 min read
The motivating example: a bank transfer
Why a single real-world action often needs more than one database statement.
Think of moving a coin from one hand to the other. If the coin is dropped in between, someone insists there are still two hands, one holding nothing and one holding nothing. Dropping the coin should mean it stays in the first hand.
In plain words: transferring 500 from one bank account to another needs two writes: subtracting 500 from one account's balance, and adding 500 to the other's. If only the first write happens, 500 vanishes. If only the second happens, 500 appears from nowhere. Both writes need to happen together, or neither should happen at all.
A worked example. A bookstore paying a supplier 2,000 for new stock.
The key lines.
UPDATE accounts SET balance = balance - 2000 WHERE account_id = 'bookstore';
UPDATE accounts SET balance = balance + 2000 WHERE account_id = 'supplier';
Transaction
A transaction is a group of one or more database operations treated as a single unit. Either every operation in it takes effect, or none do. SQL groups statements into a transaction using BEGIN and COMMIT.
Example: wrapping both UPDATE statements above in a transaction ensures the 2,000 either moves completely or not at all, never partway.