Let's drop ACID on our database

A good amount of acronyms that defines software engineering principles. People likes them because they create structured rules, and it gives a frame to think within.

One of these, that applies to databases, is the ACID principle. It’s an important concept to understand if you work with databases, especially when it comes to ensuring data integrity.

What is the ACID principle, you may ask?

ACID stands for Atomicity, Consistency, Isolation, and Durability. These are the four properties that ensure that database transactions are processed reliably and consistently.

Atomicity #

The Atomicity refers to the concept of an “all or nothing” transaction. Which means that, a transaction is either completed in its entirety or not at all. In practice, if a transaction fails at any point, all the changes made during the transaction are rolled back, and the database is returned to its original state.

We can think of an example where a database transaction for an online banking system consist of transferring $20 from an account A to an account B. The first step to this is to deduce the $20 from the account A and then adding them to the account B. But without respecting the Atomicity of it, if we fail at adding the $20 to the account B, and we committed the changes to deduce the money from account A, we would end up with a net loss of money.

Consistency #

Consistency means that a transaction must leave the database in a valid state. In other words, all data must meet the rules and constraints defined by the database schema. If a transaction violates any of these rules or constraints, it will be rolled back.

To image this concept, we can simply think of a case where a transaction would try to transgress an SQL database enforced types on constrains like the uniqueness of the pk.

Isolation #

The Isolation refers to the concept that each transaction works independently of other transactions. Each transaction must be isolated from others to prevent any interference or data corruption. This ensures that transactions are processed without any interference from other transactions running concurrently.

For instance, if we take back the example of a bank system: If a first transaction consists of transferring money to an account A to B, that is to first reduce an account’s balance, then increase the other one. And a second transaction is to measure the balance of all the accounts. If we measure the balance of both accounts when we reduced the first account’s balance, but we haven’t increased the second one yet, that is, if both transactions are not isolated, the results would be false and we would measure an imbalance.

Durability #

Durability means that once a transaction is committed, it is permanent and cannot be undone. Even in the case of a system failure, a committed transaction will be saved and restored once the system is back up and running.

This one can seem a bit more abstract since its validity lies almost entirely on the database’s system developers, imho. But in our banking system, it would mean that we have to be insured that if an account A has received a transfer of $20. If right after the transfer, the database faces a system failure, we can restore the latest state, which takes into account the credited $20.

Why is the ACID principle important? #

The ACID principle is essential for ensuring the reliability and consistency of database transactions. By following the ACID principle, databases can maintain data integrity and ensure that data is accurate and consistent. This is particularly important in applications that require high levels of data consistency, such as financial systems, e-commerce sites, and healthcare applications.

It is yet another acronym, but offers a nice framework to ensure the safety of your data and the management of your transactions. By ensuring that transactions are atomic, consistent, isolated, and durable, databases can maintain data integrity and prevent data corruption.