ACID Database Properties

5 min readJun 29, 2020

Today we will learn in depth about ACID database properties. If you have accidentally clicked on this video to learn about acid and base chemistry, might as well — stick around a bit longer to learn about software and programming which provides the best career opportunities right now!! This might be your life changing moment for you!

Objective

To understand what ACID database properties are.

What is Database Transaction?

We need to first understand what a database transaction is. A transaction is a logical unit that is independently executed for data retrieval or updates. A logical unit can consist of multiple tasks.

For instance, depositing $200 into your bank account is considered as a transaction. It consists of the following tasks internally in the bank’s database system:

Retrieve account balance : $1000
Update balance with a new deposit amount: $1000 + $200 = $1200

Another example of a transaction is transferring $300 from your saving account into your chequing account. It consists of the following tasks internally in the bank’s database system:

Retrieve saving account balance: $1000
Update saving account balance: $1000 - $300 = $700
Retrieve chequing account balance: $2000
Update chequing account balance: $2000 + $300 = $2300

What is ACID?

ACID is a set of properties of database transactions intended to guarantee validity even in the event of errors, power failures, etc — wikipedia definition. It is a fundamental principles of a transactional system. ACID stands for Atomicity, Consistency, Isolation, and Durability:

Atomicity: All operations in a transaction succeed or every operation is rolled back.
Consistency: On the completion of a transaction, the database is structurally sound.
Isolation: Transactions do not contend with one another. Contentious access to data is moderated by the database so that transactions appear to run sequentially.
Durability: The results of applying a transaction are permanent, even in the presence of failures.

Let’s simplify each property with examples below!

Atomicity

All operations in a transaction succeed or every operation is rolled back.

Imagine you are transferring money from your savings account to your chequing account. As I mentioned above, it consists of many tasks:

Retrieve saving account balance
Update saving account balance
Retrieve chequing account balance
Update chequing account balance

Without atomic property, you might end up with a state where you neither have your money in your chequing account nor your savings account. The situation can be reversed. You might end up doubling your money, your money in both savings and chequing account. Either way, it’s not the behaviour you would expect from your bank system

Atomic property ensures that you end up with one of the following states:

Success: $1500 moves from saving account to chequing account
Failure: $1500 stays in saving account (no changes in chequing account)

Consistency

On the completion of a transaction, the database is structurally sound.

Imagine you are depositing or withdrawing money from your bank account and your bank has the following two database tables:

transaction table: keep track of all transactions
account table: keep track of the latest balance

Note: Bank usually keeps track of all transactions to prepare for bank audit.

As you can see from the above image, transactions::transaction ID column is a foreign key to account::last transaction ID column in the account table.

With consistency property, it ensures that you do not end up with an invalid state that violates the referential integrity (e.g., having last transaction ID column sets to an ID that does not exist in the transactions table).

Isolation

Transactions do not contend with one another. Contentious access to data is moderated by the database so that transactions appear to run sequentially.

Imagine there are multiple transactions are happening to your account: i) transferring money, ii) purchasing product, and iii) paying bill all at the same time.

Without isolation property, you might end up with an invalid state where the balance is incorrectly updated because tasks from each transactions can possibly interleave from one another — famous data race condition.

Isolation property ensures that all transactions does not contend with one another and transactions appear to run sequentially.

Durability

The results of applying a transaction are permanent, even in the presence of failures.

Imagine that your bank’s database shutdown or crashes for some reason.

You do not want event of errors or power failures affect your bank account. Even it the database shutdowns, you expect your account balance to stay the same when the system comes back up online again.

Durability property ensures this!

How is this helpful for System Design Interviews?

One of the most common interview questions is around the difference between relational database vs. non-relational databases. Given a question, you need to know the difference between the two in order to choose the optimal (or most suitable) database for the problem.

ACID properties are the fundamental principle that the relational databases are built on top of. Without understanding this, you won’t be able to truly understand the pros and cons of each databases.

I hope this post helped you to understand what ACID database properties are and brought you a step forward for your interview preparation. Let me know if you have any questions, I’ll get back to you within a day or two.

Cheers!