Understanding Atomicity: The First Pillar of ACID Transactions

Introduction If you've worked with relational databases, you've likely come across the term ACID—an acronym that ensures reliable database transactions. Each letter in ACID holds deep meaning and importance: Atomicity, Consistency, Isolation, and Durability. In this article, we're going to focus on the first pillar: Atomicity. You’ll understand what it means, why it matters, and how databases implement it internally. This post includes a real-world analogy, a SQL implementation, and even a hands-on Go simulation of a Write-Ahead Log (WAL)—a key mechanism databases like PostgreSQL use to guarantee atomicity. If you're interested in diving into the rest of ACID properties, you can explore: Consistency in ACID (Coming Soon) Isolation in ACID (Coming Soon) Durability in ACID (Coming Soon) Let's deep dive in to: What is Atomicity in Databases? Atomicity in Action: A Collaborative Recipe Platform How Databases Ensure Atomicity (Under the Hood) Write Ahead Logging Explained WAL Simulation in Go Conclusion What is Atomicity in Databases? For demonstration purposes, we'll assume a PostgreSQL database. However, with only minor changes, the same principles and techniques apply to almost all traditional SQL databases like MySQL, MariaDB, and SQL Server. Atomicity means that a group of operations within a transaction are treated as a single, indivisible unit. Either all operations succeed, or none do. There’s no halfway state. Think of a transaction like a switch—it either flips entirely or not at all. This prevents data corruption, especially in systems where multiple operations are tightly coupled. Atomicity in Action: A Collaborative Recipe Platform Let’s skip the classic "bank transfer" story. Here’s a fresh real-world scenario that shows why atomicity is critical: Scenario: Collaborative Recipe Creation Platform A platform where users can collaboratively create and edit recipes. Atomic Transaction Example: When a user adds a new ingredient to a recipe, three things must happen: The ingredient is added to the recipe's ingredient list. The recipe’s "last modified" timestamp is updated. The contributor’s "contribution count" is incremented. The Problem Without Atomicity: The ingredient is added, but the recipe no longer exists. The contributor's count updates, but the ingredient is never stored. One part succeeds while the others fail—leading to data inconsistency. As you can see, without atomicity, there's a real risk of data inconsistency—where some parts of a transaction are saved while others are not. In a simple recipe app, it might just mean a missing ingredient or an incorrect contributor count. But in critical business applications—like banking, e-commerce, or healthcare—even small inconsistencies can lead to serious consequences. It can damage user trust, trigger system errors, and ultimately cause financial or reputational loss. That's why atomicity isn’t just a nice-to-have—it’s essential. So, how do databases actually guarantee atomicity during operations? Let’s see how we can ensure atomicity using SQL transactions in a practical scenario. Practical SQL Example Here’s how you'd structure this in SQL to ensure atomicity: BEGIN; -- Start the transaction -- Step 1: Check if the recipe exists and lock it SELECT recipe_id FROM recipes WHERE recipe_id = 1 FOR UPDATE; -- Step 2: Add a new ingredient INSERT INTO ingredients (recipe_id, name, added_by) VALUES (1, 'Garlic', 2); -- Step 3: Update the recipe's timestamp UPDATE recipes SET last_modified = NOW() WHERE recipe_id = 1; -- Step 4: Increment user's contribution count UPDATE users SET contribution_count = contribution_count + 1 WHERE user_id = 2; COMMIT; -- Commit only if all operations succeed If any step fails, the database rolls everything back to the state before BEGIN. How Databases Ensure Atomicity (Under the Hood) Now you may wonder, how does the database make sure this all-or-nothing behavior works, especially if the system crashes in between? The answer lies in something called a Write-Ahead Log (WAL). Write Ahead Logging Explained In PostgreSQL, Write-Ahead Logging (WAL) ensures that every transaction is all or nothing—the core of atomicity. Before the database makes any real changes, it first writes them to a special log file (the WAL). Think of it like drafting a message in your notes app before sending it—if your phone crashes, you still have the draft. If the system crashes in the middle of a transaction, PostgreSQL uses that log to figure out what was safely finished and what wasn’t. It only applies the changes that were fully completed, and rolls back anything else. This way, the database never ends up in a broken or half-updated state. WAL also adds a touch of durability, because even if the power goes out, the log is still there to bring everything back as it was before.

Apr 13, 2025 - 08:12

Understanding Atomicity: The First Pillar of ACID Transactions

Introduction

If you've worked with relational databases, you've likely come across the term ACID—an acronym that ensures reliable database transactions. Each letter in ACID holds deep meaning and importance: Atomicity, Consistency, Isolation, and Durability.

In this article, we're going to focus on the first pillar: Atomicity. You’ll understand what it means, why it matters, and how databases implement it internally. This post includes a real-world analogy, a SQL implementation, and even a hands-on Go simulation of a Write-Ahead Log (WAL)—a key mechanism databases like PostgreSQL use to guarantee atomicity.

If you're interested in diving into the rest of ACID properties, you can explore:

Consistency in ACID (Coming Soon)
Isolation in ACID (Coming Soon)
Durability in ACID (Coming Soon)

Let's deep dive in to:

What is Atomicity in Databases?
Atomicity in Action: A Collaborative Recipe Platform
How Databases Ensure Atomicity (Under the Hood)
Write Ahead Logging Explained
WAL Simulation in Go
Conclusion

What is Atomicity in Databases?

For demonstration purposes, we'll assume a PostgreSQL database. However, with only minor changes, the same principles and techniques apply to almost all traditional SQL databases like MySQL, MariaDB, and SQL Server.

Atomicity means that a group of operations within a transaction are treated as a single, indivisible unit. Either all operations succeed, or none do. There’s no halfway state.

Think of a transaction like a switch—it either flips entirely or not at all. This prevents data corruption, especially in systems where multiple operations are tightly coupled.

Atomicity in Action: A Collaborative Recipe Platform

Let’s skip the classic "bank transfer" story. Here’s a fresh real-world scenario that shows why atomicity is critical:

Scenario: Collaborative Recipe Creation Platform

A platform where users can collaboratively create and edit recipes.

Atomic Transaction Example:

When a user adds a new ingredient to a recipe, three things must happen:

The ingredient is added to the recipe's ingredient list.
The recipe’s "last modified" timestamp is updated.
The contributor’s "contribution count" is incremented.

The Problem Without Atomicity:

The ingredient is added, but the recipe no longer exists.
The contributor's count updates, but the ingredient is never stored.
One part succeeds while the others fail—leading to data inconsistency.

As you can see, without atomicity, there's a real risk of data inconsistency—where some parts of a transaction are saved while others are not. In a simple recipe app, it might just mean a missing ingredient or an incorrect contributor count. But in critical business applications—like banking, e-commerce, or healthcare—even small inconsistencies can lead to serious consequences. It can damage user trust, trigger system errors, and ultimately cause financial or reputational loss. That's why atomicity isn’t just a nice-to-have—it’s essential.
So, how do databases actually guarantee atomicity during operations? Let’s see how we can ensure atomicity using SQL transactions in a practical scenario.

Practical SQL Example

Here’s how you'd structure this in SQL to ensure atomicity:

BEGIN;  -- Start the transaction

-- Step 1: Check if the recipe exists and lock it
SELECT recipe_id FROM recipes 
WHERE recipe_id = 1 
FOR UPDATE;

-- Step 2: Add a new ingredient
INSERT INTO ingredients (recipe_id, name, added_by) 
VALUES (1, 'Garlic', 2);

-- Step 3: Update the recipe's timestamp
UPDATE recipes 
SET last_modified = NOW() 
WHERE recipe_id = 1;

-- Step 4: Increment user's contribution count
UPDATE users 
SET contribution_count = contribution_count + 1
WHERE user_id = 2;

COMMIT;  -- Commit only if all operations succeed

If any step fails, the database rolls everything back to the state before BEGIN.

How Databases Ensure Atomicity (Under the Hood)

Now you may wonder, how does the database make sure this all-or-nothing behavior works, especially if the system crashes in between?

The answer lies in something called a Write-Ahead Log (WAL).

Write Ahead Logging Explained

In PostgreSQL, Write-Ahead Logging (WAL) ensures that every transaction is all or nothing—the core of atomicity. Before the database makes any real changes, it first writes them to a special log file (the WAL). Think of it like drafting a message in your notes app before sending it—if your phone crashes, you still have the draft.

If the system crashes in the middle of a transaction, PostgreSQL uses that log to figure out what was safely finished and what wasn’t. It only applies the changes that were fully completed, and rolls back anything else. This way, the database never ends up in a broken or half-updated state.

WAL also adds a touch of durability, because even if the power goes out, the log is still there to bring everything back as it was before.

WAL Simulation in Go

package main

import (
    "bufio"
    "fmt"
    "os"
)

type WriteAheadLog struct {
    logFile string
}

func NewWriteAheadLog(filename string) *WriteAheadLog {
    return &WriteAheadLog{logFile: filename}
}

func (wal *WriteAheadLog) LogWrite(transaction string) {
    file, err := os.OpenFile(wal.logFile, os.O_APPEND|os.O_CREATE|os.O_WRONLY, 0644)
    if err != nil {
        fmt.Println("Error writing log:", err)
        return
    }
    defer file.Close()

    _, err = file.WriteString(transaction + "\n")
    if err == nil {
        fmt.Println("Logged:", transaction)
    }
}

func (wal *WriteAheadLog) LogRecover() {
    file, err := os.Open(wal.logFile)
    if err != nil {
        fmt.Println("Error reading log:", err)
        return
    }
    defer file.Close()

    fmt.Println("Recovering transactions...")
    scanner := bufio.NewScanner(file)
    for scanner.Scan() {
        fmt.Println("Recovered:", scanner.Text())
    }
}

func (wal *WriteAheadLog) Commit() {
    err := os.WriteFile(wal.logFile, []byte{}, 0644)
    if err != nil {
        fmt.Println("Error committing log:", err)
    } else {
        fmt.Println("Committed transactions.")
    }
}

func main() {
    wal := NewWriteAheadLog("wal_log.txt")

    wal.LogWrite("INSERT INTO users VALUES(1, 'Alice')")
    wal.LogWrite("UPDATE users SET name = 'Bob' WHERE id = 1")

    fmt.Println("\nSimulating system restart...\n")
    wal.LogRecover()

    wal.Commit()
}

Conclusion

Atomicity ensures that your data operations don't leave the database in a broken or inconsistent state. Whether you’re building a social app, a recipe platform, or a financial system—atomic transactions preserve trust and data integrity.

Modern databases, especially PostgreSQL, use mechanisms like WAL to guarantee atomicity even in the face of unexpected crashes. By understanding these internals, you become better equipped to design reliable, scalable systems.

In future articles, we’ll dive into the remaining pillars of ACID—Consistency, Isolation, and Durability—each critical in its own right.

Before I end this post, I want to take a moment to express my deep love and unwavering support for our Palestinian Muslim brothers and sisters