ACID transactions explained: atomicity, consistency, isolation and durability

The value of a transactional system lies in its guarantees. OLTP systems enforce transactions with ACID semantics, named after four properties: atomicity, consistency, isolation and durability.

Learn’s illustration is a bank transfer. Account A starts with a balance of 100 and account B with 50, and 40 is to be moved from A to B. The system must debit A and credit B as a single, reliable operation. Each ACID property describes one way that operation is protected.

• Atomicity: each transaction is treated as a single unit that either succeeds completely or fails completely. The debit from A and the credit to B must both happen; if either cannot be completed, the other must fail too. A transaction is never left half-done. In a traditional relational database, steps already carried out are rolled back automatically when a transaction cannot complete.
• Consistency: a transaction can only take the data from one valid state to another valid state. When the transfer completes, the data must show the funds moved from one account to the other: 60 in A and 90 in B.
• Isolation: concurrent transactions (ones running at the same time) cannot interfere with one another and must result in a consistent database state. While the transfer is in progress, another transaction checking both balances must get consistent results. It cannot read A’s balance from before the transfer and B’s balance from after it, which would make 40 appear twice.
• Durability: once a transaction is committed (confirmed as complete), it stays committed. The new balances are persisted, so even if the database system were switched off, the committed transfer would still be reflected when it is switched on again.

Common trap: Mixing up consistency and isolation - consistency is about each transaction moving the data from one valid state to another; isolation is about transactions running at the same time not seeing each other’s half-finished work, such as a balance check during a transfer.

Common trap: Assuming ACID guarantees are only available in relational databases - Learn’s OLTP guidance notes that Azure Cosmos DB, a non-relational service, provides ACID guarantees through transactional batch operations within a logical partition.

Get the whole book

This note is one section of Ultra Transcenders DP-900: Microsoft Azure Data Fundamentals, an independent study guide that explains every topic the exam covers by technology, with comparison tables, diagrams and the common traps, plus a glossary linked to Microsoft Learn.

About the book · DP-900 terms in the glossary · All DP-900 study notes