Transactions and ACID
A transaction groups statements into one logical unit. ACID refers to atomicity, consistency, isolation, and durability. BEGIN starts a transaction, COMMIT makes its changes permanent, and ROLLBACK discards uncommitted changes. Transactions are crucial when multiple writes must succeed together, such as creating an order and reducing inventory.
Keep transactions appropriately short. Long-running transactions can retain old row versions, increase contention, and interfere with maintenance. Never assume that two separate queries are atomic together unless they are in the same transaction or protected by another suitable mechanism.
Transfer funds atomically
BEGIN;
UPDATE accounts SET balance = balance - 500 WHERE id = 1;
UPDATE accounts SET balance = balance + 500 WHERE id = 2;
COMMIT;💡 Both balance changes are committed together if the transaction succeeds. Production code should verify that the source account exists and has sufficient funds, and should handle errors with rollback behavior.
Key Takeaways
- Transactions group related changes.
- ACID describes key transaction guarantees.
- Keep transactions short and handle failures deliberately.
Knowledge Check
Which command discards uncommitted changes in the current transaction?