Last week, I wrote about SQL and NoSQL databases. One of the main differences between the database types is ACID compliancy. ACID stands for atomicity, consistency, isolation, durability, and describes a set of transaction properties that guarantees database validity even if an error occurs. A majority of SQL/relational databases are ACID compliant databases. I wanted to learn more about ACID properties and why it's an important consideration when deciding what database to use.
Transactions
A quick note about transactions. A transaction is a collection of instructions. All transactions must follow ACID properties in order to guarantee database validity. I'll use banking transactions between customers below to illustrate the 4 properties.
Atomicity
Atomicity ensures that each transaction is treated as a single unit that follows the "all or nothing" rule; a transaction either happens completely or not at all. If any part of the transaction fails, the entire transaction will fail.
For example, customer A wants to withdraw $100 from their account and then deposit it into customer B's account. If any of the instructions fail (insufficient funds or server crashes, etc.), the transaction will fail and any changes will be rolled back.
Consistency
Consistency guarantees that a transaction must be valid before it gets written to the database. A transaction is valid when it follows all of the defined rules, including constraints, cascades, triggers, and any combination of these. Inconsistent transactions will result in the database being rolled back to a previous state that complies with the given database rules.
If customer A wants to withdraw $100 from their account but only has $50 in their account, consistency prevents them from withdrawing the funds and the transaction will be aborted.
Isolation
As transactions are typically executed at the same time (i.e. multiple transactions that read and write to a table at the same time), isolation sees that each transaction acts as an individual transaction and would receive the same state if the transactions were executed sequentially. Isolation is important for concurrency control.
If customer C has an account balance of $1000 that both customers A and B can make withdrawals from (say $50 and $100, respectively), one of the customers will have to wait until the other customer transaction is finished, in order to avoid inconsistent data.
Durability
Durability ensures that once a transaction has been completed and committed, it will remain committed even in the event of a system failure (crash or power failure). These completed transactions are stored in non-volatile memory.
If customer A successfully deposited $500 in their account, this transaction should not disappear if any system failure occurs.
As I mentioned above, a majority of SQL/relational databases are ACID compliant. NoSQL databases tend to sacrifice ACID compliancy for faster performance. There is strong data reliability and a guarantee that the transactions are safe.
There may be clear cut scenarios where an ACID-compliant database is the best solution, such as e-commerce, healthcare, financial services apps (when data integrity is of the utmost concern). Yet, there other times where you may want to take other factors into consideration in deciding between a SQL or NoSQL database.
Now fully understanding what ACID stands for, I feel better informed when having to make database decisions.
Resources
ACID - Wikipedia
Atomicity Consistency Isolation Durability (ACID) - Techopedia
What are ACID properties in a database? - Educative
Top comments (1)
Thanks! Very helpful post. ACID is often asked in a tech interview so it's good to memorize the basics of it.