Deep dive
A transaction is a boundary around a unit of work. It gives you ACID properties (to varying degrees depending on isolation level):
- **Atomicity**: all-or-nothing.
- **Consistency**: constraints/invariants are preserved (assuming correct logic).
- **Isolation**: concurrent transactions don’t see each other in unsafe ways.
- **Durability**: once committed, data survives crashes.
Example (why it matters)
Money transfer must not “lose” money: 1) subtract from account A 2) add to account B If step 2 fails, you must roll back step 1.
Practical guidance
- Keep transactions short (long transactions hold locks or keep old MVCC versions around).
- Touch rows in a consistent order to reduce deadlocks.
- Be prepared to retry on transient failures (e.g., serialization conflicts).
Common pitfalls
- Doing network calls inside a transaction (slow + increases lock time).
- Assuming transactions automatically fix race conditions without proper isolation/locking.
- Swallowing errors and committing partial work accidentally.