"I" in ACID - Isolation

After talking about the "A" and the "C" in ACID, let's talk about the "I" in ACID - Isolation. In this one, we do a micro-dive into Isolation in the context of database. We will take a detailed look into Isolation, understand its importance, functioning, and how the database implements it.

What is Isolation?

Isolation is the ability of the database to concurrently process multiple transactions in a way that changes made in one does not affect the other. A simple analogy is how we have to make our data structures and variables thread-safe in a multi-threaded (concurrent) environment.

And similar to how we use Mutex and Semaphores to protect variables, the database uses locks (shared and exclusive) to protect transactions from one another.


Why is Isolation important?

Isolation is one of the most important properties of any database engine, the absence of which directly impacts the integrity of the data.

Example 1: Cowin Portal

When 500 slots open for a hospital, the system has to ensure that a max of 500 people can book their slots.

Example 2: Flash Sale

When Xiaomi conducts a flash sale with 100k units, the system has to ensure that orders of a max of 100k units are placed.

Example 3: Flight Booking

If a flight has a seating capacity of 130, the airlines cannot have a system that allows ticket booking of more than that.

Example 4: Money transfers

When two or more transfers happen on the same account simultaneously, the system has to ensure that the end state is consistent with no mismatch of the amount. Sum of total money across all the parties to remain constant.

The isolation property of a database engine allows the system to put these checks on the database, which ensures that the data never goes into an inconsistent state even when hundreds of transactions are executing concurrently.

How is isolation implemented?

A transaction before altering any row takes a lock (shared or exclusive) on that row, disallowing any other transaction to act on it. The other transactions might have to wait until the first one either commits or rollbacks.

The granularity and the scope of locking depend on the isolation level configured. Every database engine supports multiple Isolation levels, which determines how stringent the locking is. The 4 isolation levels are

  • Serializable
  • Repeatable reads
  • Read committed
  • Read uncommitted

We will discuss Isolation Levels in detail in some other essay.


Arpit Bhayani

Arpit's Newsletter

CS newsletter for the curious engineers

❤️ by 38000+ readers

If you like what you read subscribe you can always subscribe to my newsletter and get the post delivered straight to your inbox. I write essays on various engineering topics and share it through my weekly newsletter.

Other essays that you might like

ACID - Durability

335 reads 2021-07-19

Durability seems to be a taken-for-granted requirement, but to be honest, it is the most important one. Let's deep dive ...

ACID - Consistency

387 reads 2021-07-02

In the context of databases, Consistency is Correctness, which means that under no circumstance will the data lose its c...

ACID - Atomicity

748 reads 2021-06-28

A single database transaction often contains multiple statements to be executed on the database. In Relational Databases...

Image Steganography

304 reads 2020-01-17

Steganography has been around since at least 440 BCE but with the rise of computers, the techniques have evolved to hand...

Be a better engineer

A set of courses designed to make you a better engineer and excel at your career; no-fluff, pure engineering.

Paid Courses

System Design for Beginners

A masterclass that helps early engineers and product managers become great at designing scalable systems.

180+ learners

Details →

System Design Masterclass

A masterclass that helps you become great at designing scalable, fault-tolerant, and highly available systems.

1000+ learners

Details →

Redis Internals

Learn internals of Redis by re-implementing some of the core features in Golang.

98+ learners

Details →

Free Courses

Designing Microservices

A free playlist to help you understand Microservices and their high-level patterns in depth.

823+ learners

Details →

GitHub Outage Dissections

A free playlist to help you learn core engineering from outages that happened at GitHub.

651+ learners

Details →

Hash Table Internals

A free playlist to help you understand the internal workings and construction of Hash Tables.

1027+ learners

Details →

BitTorrent Internals

A free playlist to help you understand the algorithms and strategies that power P2P networks and BitTorrent.

692+ learners

Details →