Fun MySQL fact of the day: all-or-something?

Finally, A. The A in 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 D, C, and 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 D, C, or 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.