Article

The ACID Test: Why Your Database Transactions Need These Four Magic Letters

9 min read
Originally published on Medium. Read, clap or comment on Medium ↗

Understanding the principles that keep your money safe and your data sane

You’re standing at an ATM at 11:59 PM, withdrawing your last $100 before rent is due. At the exact same moment, your bank is processing your paycheck deposit. The ATM screen flickers. The power goes out. When it comes back on, what happened to your money?

If your bank uses a database that follows ACID principles, you can sleep soundly. If not, well… you might want to check your balance.

The Problem with Digital Money

Here’s the thing about databases: they’re essentially trying to do magic. They’re taking the messy, chaotic world of multiple people doing multiple things at the exact same time, and making it all… work. It’s like conducting an orchestra where every musician is in a different time zone, playing a different song, and occasionally the concert hall catches fire.

Without rules, this is what could happen:

This is where ACID comes in. Not the chemical kind that burns holes in things, but the database kind that prevents holes from appearing in your data.

A is for Atomicity: The “All or Nothing” Rule

Imagine you’re moving to a new apartment. You wouldn’t want half your stuff to arrive at the new place and half to vanish into the ether, right? That’s atomicity.

In database terms, atomicity means a transaction either happens completely or doesn’t happen at all. There’s no middle ground, no “mostly succeeded,” no “well, two out of three ain’t bad.”

The Classic Example: Bank Transfers

When you Venmo your roommate $50 for pizza, this is what happens behind the scenes:

Step 1: Subtract $50 from your account
Step 2: Add $50 to their account

Atomicity guarantees that both steps happen or neither happens. You’ll never have the money disappear from your account without appearing in theirs. The transaction is atomic — indivisible, like an atom. (Yes, I know atoms can be split. The database people named this before they knew about nuclear physics, okay?)

When Things Go Wrong

Let’s say the database crashes right after Step 1. Without atomicity, you’re out $50 and your roommate got nothing. With atomicity, the database says “Nope, that transaction never happened” and rolls everything back. Your $50 is restored, your roommate is still hungry, and the universe makes sense again.

C is for Consistency: The Rules Must Be Followed

Consistency is your database’s strict parent. It enforces the rules, no matter what.

Think of it like a bouncer at a club checking IDs. The rule is “must be 21 or older.” The bouncer doesn’t care if you’re really mature for your age or if it’s your birthday tomorrow. Rules are rules.

The Inventory Problem

You’re buying the last Nintendo Switch at Target’s website. The rule is simple: you can’t sell more items than you have in stock.

Without consistency:

With consistency:

Other Rules Consistency Enforces

The beautiful thing about consistency is that it prevents your database from ever being in a state that violates your business logic. It’s the difference between a database and a pile of random numbers.

I is for Isolation: The “Mind Your Own Business” Rule

This is where things get interesting. Isolation is about what happens when multiple people are using the database at the same time — which, let’s be honest, is always.

The Concert Ticket Scenario

Imagine Taylor Swift just announced a surprise show. The website lists one ticket left. You and 10,000 other people click “Buy” at the exact same millisecond.

Without proper isolation, chaos:

With isolation:

The Four Levels of Isolation

Databases actually give you a choice of how isolated you want transactions to be. It’s like choosing between living alone, living with roommates, or living in a college dorm.

  1. Read Uncommitted (The Dorm): You can see everyone’s dirty laundry, even stuff they haven’t finalized. Fast but chaotic.
  2. Read Committed (Shared Apartment): You only see things after people have cleaned up. More reliable, still pretty quick.
  3. Repeatable Read (Your Own Studio): Once you start reading something, it stays consistent until you’re done. Even more reliable.
  4. Serializable (Isolation Chamber): Transactions happen as if you’re the only person in the universe. Completely safe, but slooooow.

Most applications use Read Committed as a good balance. Your bank probably uses Serializable for anything involving money, because they’d rather be slow than wrong.

D is for Durability: The “It’s Written in Stone” Rule

Durability is the promise that once the database says “Yep, that happened,” it really, truly, happened. Forever. Even if the server explodes, the building burns down, and the company’s hamster mascot runs away.

The Order Confirmation

You buy something online. The website shows: “Order Confirmed! Confirmation number: 123456.” Then, one second later, a meteor hits the data center.

Without durability:

With durability:

How Databases Achieve Durability

The technical magic behind durability involves:

When ACID Saves the Day: Real-World Stories

The Flash Crash That Wasn’t

In 2010, the US stock market had a “flash crash” where $1 trillion in value vanished in minutes. Thanks to ACID-compliant databases, every single transaction was tracked, isolated, and durable. When they rolled back the erroneous trades, the database could say with certainty: “Here’s exactly what happened, transaction by transaction.” No money was actually lost to the void.

The E-Commerce Nightmare Averted

A major online retailer once had a bug that tried to charge customers multiple times for the same order. ACID’s atomicity meant that each charge-and-ship transaction was all-or-nothing. When the duplicate charges were attempted, they violated consistency rules (one order ID can’t map to multiple charges), so they were rejected. Crisis averted.

The Hospital That Didn’t Lose Patient Records

During a ransomware attack, hackers encrypted a hospital’s main database. But because of durability and proper backups, every single patient record, prescription, and appointment was recoverable. The transaction logs let them replay every database operation since the last backup, recovering everything.

The Cost of ACID

Here’s the catch: ACID isn’t free. All these guarantees require work, and work takes time.

ACID databases are generally:

When You Don’t Need Full ACID

Do you need ACID for your blog’s comment section? Probably not. If a comment gets lost, it’s annoying but not catastrophic.

Do you need ACID for your bank’s core transaction system? Absolutely, 100%, no question, you’d be insane not to.

This is why different databases make different choices. MongoDB started out prioritizing speed over strict ACID compliance (though modern versions have ACID transactions). PostgreSQL and traditional SQL databases go all-in on ACID. It depends on what you’re building.

The Modern Twist: BASE

In the age of massive distributed systems (think Google, Facebook, Amazon), some engineers said: “You know what? Perfect consistency is impossible at this scale. Let’s be realistic.”

They created BASE: Basically Available, Soft state, Eventually consistent.

BASE systems say: “Your data will be consistent… eventually. Maybe in a few milliseconds, maybe in a few seconds. But we’ll get there. Promise.”

This works for things like social media likes. If your like count is briefly off by a few hundred on a viral post, who cares? It’ll correct itself. But for your bank balance? You want ACID, not BASE.

Building with ACID: A Quick Example

Here’s what an ACID-compliant transaction looks like in a Node.js application using MongoDB:

async function transferMoney(fromAccount, toAccount, amount) {
  const session = await mongoose.startSession();
  
  try {
    await session.withTransaction(async () => {
      // Atomicity: All these happen or none happen
      
      // Consistency: Check business rules
      const sender = await Account.findOneAndUpdate(
        { 
          accountId: fromAccount,
          balance: { $gte: amount } // Rule: can't overdraft
        },
        { $inc: { balance: -amount } },
        { session, new: true }
      );
      
      if (!sender) {
        throw new Error('Insufficient funds');
      }
      
      // Isolation: Other transactions can't interfere
      await Account.findOneAndUpdate(
        { accountId: toAccount },
        { $inc: { balance: amount } },
        { session, new: true }
      );
      
      // Durability: Once we commit, it's permanent
    });
    
    return { success: true };
  } catch (error) {
    // Transaction rolled back automatically
    return { success: false, error: error.message };
  } finally {
    session.endSession();
  }
}

Notice how we don’t have to manually handle rollbacks, isolation, or durability. The database does all that heavy lifting. We just define what we want to happen, and ACID makes sure it happens correctly.

The Bottom Line

ACID principles are like the foundation of a house. You don’t think about them when everything’s working, but without them, the whole structure collapses.

Every time you:

…there’s an ACID-compliant database in the background, making sure everything happens exactly as it should, even when servers crash, networks fail, and thousands of people are doing things simultaneously.

It’s not the flashiest part of software engineering. Nobody writes Medium articles titled “10 ACID Principles That Will SHOCK You!” (except, well, me, just now). But it’s the bedrock of every reliable application you use.

So the next time your bank transfer goes through perfectly, or your concert ticket purchase is confirmed, take a moment to appreciate those four little letters: A.C.I.D.

They’re keeping your digital life from descending into chaos, one transaction at a time.

Want to dive deeper? Check out Martin Kleppmann’s “Designing Data-Intensive Applications” — it’s the bible of database architecture. Or don’t, and just trust that the engineers building your apps read it so you don’t have to.

← All articles Discuss on Medium ↗