ACID

principles

acronym title description
A ATOMICITY all or nothing rule. If a part of the transaction fails, whole/entire transaction fails. Atomic nature of transactions. All operations in a transaction succeed or every operation is rolled back. All transaction operations are a whole unit or atom. An atomic transaction is either fully completed, or is not begun at all. e.g.: transfer money from account A to account B. if system fails after subtracting from A, money gets returned to A.
C CONSISTENCY Only valid data is written to the database. When a transaction results in invalid data, the database reverts to its previous state, which abides by all customary rules and constraints. On the completion of a transaction, the database is structurally sound. A transaction cannot leave the database in an inconsistent state. e.g.: Looking again at the account transfer system, the system is consistent if the total of all accounts is constant. If an error occurs and the money is removed from account A and not added to account B, then the total in all accounts would have changed. The system would no longer be consistent. By rolling back the removal from account A, the total will again be what it should be, and the system back in a consistent state.
I ISOLATION multiple transactions occurring at the same time not impact each other’s execution. No interference. Does not ensure their order. Transactions do not contend with one another. Contentious access to data is moderated by the database so that transactions appear to run sequentially. If transactions are executed concurrently, the result is equivalent to their serial execution.
D DURABILITY ensures that any transaction committed to the database will not be lost. Db backups and transaction logs facilitate restoration. Completed transactions persist, even when servers restart etc. The results of applying a transaction are permanent, even in the presence of failures.

glossary

  • write-ahead logging (WAL) in which any transaction detail is first written to a log that includes both redo and undo information.
  • shadow-paging in which a shadow page is created when data is to be modified. The query’s updates are written to the shadow page rather than to the real data in the database. The database itself is modified only when the edit is complete.
  • For many domains and use cases, ACID transactions are far more pessimistic (i.e., they’re more worried about data safety) than the domain actually requires.
  • In the NoSQL world, ACID transactions are less fashionable as some databases have loosened the requirements for immediate consistency, data freshness and accuracy in order to gain other benefits, like scale and resilience. Notably, the .NET-based RavenDB has bucked the trend(to be noticeably different from the way that a situation is developing generally) among aggregate stores in supporting ACID transactions.
< sql > nosql home