Fun MySQL fact of the day: all-or-something?
ACID stands for Atomicity, which is the property that guarantees that a series of database operations performed in a transaction all occur or nothing occurs. You might be thinking, "if Atomicity is the first letter in
ACID, why did you save it for last"? I saved it for last simply because Durability, Consistency, and Isolation are all intertwined with Atomicity:
- Durability - If a transaction cannot commit because it cannot be permanently recorded, it must* be rolled-back like nothing happened.
- Consistency - If a transaction cannot commit because it has attempted to change data in an "illegal" way, it must* be rolled-back like nothing happened.
- Isolation - If a transaction cannot commit because another transaction affecting a common record has completed first, it must* be rolled-back like nothing happened.
Back to MySQL: by default, MySQL puts all new connections in
autocommit mode, which executes a single statement in a single transaction that, assuming all of
I all stay intact, runs in an atomic, all-or-nothing way.
autocommit can be a remarkably powerful feature when used prudently, but it can also be a very annoying, if not exceptionally dangerous feature when used incorrectly or unknowingly. Find out why in tomorrow's Fun MySQL fact of the day.
* - Whether or not a transaction is automatically rolled-back after an
I failure varies between database versions and vendors. From a logical standpoint, any changes made before a failed change are likely based on erroneous, inconsistent information that is no longer valid. It doesn't really make a lot of sense to keep half-applied changes. Still, for technical reasons, an automatic
ROLLBACK may not always be desirable in MySQL/InnoDB, but that's a topic for another day.