ACID is the building blocks of a relational databases. Today we’re going to understand what it means.

tldr; Just remember

  • Atomicity relates to Transactions - All or Nothing.
  • Consistency relates to Constraints and Triggers - All the data is consistent.
  • Isolation relates to Locks - Concurrent operation doesn’t intefere with each other.
  • Durability relates to Replication - Committed Transactions to be persisted in case of system failure.

Atomicity - Transactions

Atomicity guarantees All or Nothing.

A set of instructions are executed as an atomic unit, either all succeeds or none.

Say Alice is transferring 500$ to Bob’s account:

BEGIN TRANSACTION

  1. Check 500$ Balance in Alice’s Account - SUCCESS
  2. Deduct 500$ from Alice’s Account - SUCCESS
  3. Deposit 500$ to Bob’s Account - FAILURE :(

END TRANSACTION

Due to network failure the Deposit 500$ step fails. Since all the steps are in a Transaction, all of them are reverted. 500$ is transferred back to Alice’s account

Consistency - Constraints and Triggers

Consistency ensures All the data is consistent.

When any change is carried out a set of rules are conformed with when the data changes.

Say we’ve added a constraint on Accounts table - account’s amount cannot be < 0. This constraint is checked whenever data changes in Accounts table.

  1. Check Balance in Alice’s Account - 200$
  2. Deduct 800$ from Alice’s Account - FAILURE

Deducting 800$ from Alice’s account failed because of the constraint mentioned before.

Isolation - Locks

Isolation makes sure Concurrent operation doesn’t intefere with each other.

If 2 transactions tries to write on the same row then only one must be allowed at a time.

Let’s say there are two Transactions updating Alice’s account

T1

  1. Read Balance from Alice’s Account - 1200$
  2. Deduct 800$ from Alice’s Balance - 400$
  3. Update Alice’s Account with 400$

T2

  1. Read Balance from Alice’s Account - 1200$
  2. Deduct 200$ from Alice’s Balance - 1000$
  3. Update Alice’s Account with 1000$

If T1 and T2 were allowed to run without locks then the updates can go wrong:

  • T1 Reads Balance from Alice’s Account - 1200$
  • T2 Reads Balance from Alice’s Account - 1200$
  • T1 Deducts 800$ from Alice’s Balance - 400$
  • T1 Updates Alice’s Account with - 400$
  • T2 Deducts 200$ from Alice’s Balance - 1000$
  • T2 Updates Alice’s Account with - 1000$

Finally Alice’s Account has 1000$ which is not right. Using locks with Transactions makes sure that only one transaction can perform operations on the same data.

Durability - Replication and Transaction Log Files

Durability allows Committed Transactions to be persisted in case of system failure.

Say in our Alice and Bob example the system failed before transferring 500$ from Alice’s account to Bob’s account. Alice’s account now has 500$ less while Bob didn’t got the 500$ since it was committed.

After the system comes back online it must redo the deposit to Bob’s account.