Overview

ACID stands for Atomicity, Consistency, Isolation, and Durability. It was coined in 1983 by Theo Härder and Andreas Reuter to define a standard for database fault-tolerance mechanisms. However, in reality, databases implement it in various ways, leading to different outcomes. So, when a database claims to adhere to ACID, we cannot be entirely sure of everything. Let’s be cautious about that.

Atomicity

A transaction is a set of operations that must be performed as a single unit. That means either all of the operations in the transaction are performed or none of them are completed. In other words, a transaction is atomic if treated as a single “unit,” which either succeeds completely or fails completely.

e.g., you transfer 100$ from your bank account to my bank account. Only two cases can happen:

  • You lost 100$ , and I got 100$
  • You lost nothing, and I got nothing

that is atomicity.

Consistency

Consistency means that the database is always in a valid state. That means that all the rules of the database are followed. For example, if you have a table of employees, then each employee’s age must be greater than 18. Database consistency ensures that this rule is always followed.

But in the real world, it is more complex. Currently, most software has complex business rules. So they can’t be implemented in the database. So usually, They handle it in the application layer.

When scaled up, it can be challenging to maintain consistency. But more than a post is needed to explain it.

Note: Don’t confuse consistency with atomicity. e.g., You transfer 100$ to an unknown account. This transaction is atomic. But it is inconsistent.

Isolation

Isolation is the ability of a transaction to run independently of other transactions. That means that the execution of a transaction does not affect the execution of other transactions. In other words, a transaction is isolated if it is treated as if it were the only transaction running.

We have four levels of isolation:

  • Read Uncommitted
    • A transaction can read data that other transactions have not committed.
  • Read Committed
    • A transaction can only read data that other transactions have committed.
  • Repeatable Read
    • Higher than Read Committed. When a transaction reads the same data multiple times, it will always get the same result.
  • Serializable
    • Transactions are executed serially. In reality, the database will not execute transactions serially. It will try to keep transactions parallel but still guarantee Serializable. If not, the performance will be terrible.

Durability

Durability means that once a transaction has been committed, it will remain committed seven in the event of a system failure. In other words, a transaction is durable if it is treated as if it were permanent. You can’t keep 100% durability. Example: Disk failure.

To ensure durability, they’re using WAL (Write-Ahead Logging). It is a technique for ensuring that database transactions are durable.