A transaction groups database operations into a unit of work. It is essential when several related changes must be treated consistently, such as transferring money, placing an order, or reserving inventory. Transaction syntax, isolation defaults, and support for rolling back particular statements vary by engine and storage system.
Key Takeaways at a Glance:
- •ACID Guarantees: Atomicity (all or nothing), Consistency (valid state transitions), Isolation (independent concurrent execution), Durability (committed data survives crashes).
- •TCL Commands: BEGIN / START TRANSACTION starts the block; COMMIT makes changes permanent; ROLLBACK undoes changes.
- •SAVEPOINT creates named intermediate checkpoints within a transaction, allowing partial rollback via 'ROLLBACK TO savepoint_name'.
- •Isolation Levels: Read Uncommitted, Read Committed, Repeatable Read (MySQL default), Serializable.
In Simple Words (The Mental Model)
A transaction treats multiple database steps as one single unbreakable unit. If you transfer $100 from Account A to Account B, two things must happen: subtract $100 from A, and add $100 to B. If the power cuts out after subtracting from A, a transaction rolls everything back so your money isn't lost in cyberspace.
Real-World Analogy
Think of an escrow agent buying a house. The buyer deposits the money; the seller signs over the deed. The escrow agent releases the deed to the buyer AND the money to the seller at the exact same moment. If the buyer's check bounces, the agent returns the deed (ROLLBACK). Both succeed together, or neither happens.