ACID Operation in Backend Engineering: Building Data Integrity Like a Pro

When you build a banking app, an e-commerce checkout, or any critical backend system, data integrity is everything. A single failed transaction could cause duplicate charges, missing payments, or worse.

To prevent such disasters, databases use ACID properties:

• Atomicity
• Consistency
• Isolation
• Durability

In this article, we’ll break each property into plain English explanations with Python code examples using SQLAlchemy.

1. Atomicity – All or Nothing

Atomicity means that a transaction is indivisible.

If part of the transaction fails, the entire operation is rolled back.

Example Scenario

Alice is transferring $300 to Bob:

1. Subtract $300 from Alice’s balance.

2. Add $300 to Bob’s balance.

If step 2 fails, step 1 must be undone.

Code Example

from sqlalchemy import create_engine, Column, Integer, String
from sqlalchemy.orm import sessionmaker, declarative_base

Base = declarative_base()

class Account(Base):
    __tablename__ = 'accounts'
    id = Column(Integer, primary_key=True)
    name = Column(String, unique=True)
    balance = Column(Integer, default=0)

engine = create_engine("sqlite:///bank.db", echo=False)
Session = sessionmaker(bind=engine)
Base.metadata.create_all(engine)

def atomic_transfer(session, from_id, to_id, amount):
    try:
        from_acc = session.query(Account).filter(Account.id == from_id).with_for_update().first()
        to_acc = session.query(Account).filter(Account.id == to_id).with_for_update().first()

        if from_acc.balance < amount:
            raise ValueError("Insufficient funds")

        from_acc.balance -= amount
        to_acc.balance += amount

        session.commit()  # Only commit if all steps succeed
        print("Transfer completed.")
    except Exception as e:
        session.rollback()  # Undo everything if something fails
        print("Transfer failed:", e)

# Run a test
session = Session()
atomic_transfer(session, from_id=1, to_id=2, amount=300)
session.close()

2. Consistency – Valid State Before and After

Consistency ensures that data always follows rules.
For example:

• Balances cannot go negative.

• Total money in the system should remain correct.

Example

# Consistency check
if from_acc.balance < amount:
    raise ValueError("Insufficient funds: cannot go negative")

Here, we ensure no transaction breaks the system’s rules.

3. Isolation – Transactions Don’t Interfere

Isolation means that concurrent transactions behave as if they’re running one after another, even when they’re processed at the same time.

Example

Two transfers happening at once:

• Alice transfers $200 to Bob.

• Bob transfers $100 to Charlie.

Without isolation, their operations might overlap and cause wrong balances.
Code Example

Using with_for_update() to lock rows during the transfer:

from_acc = (
            session.query(Account)
            .filter(Account.id == from_id)
            .with_for_update()
            .first()
        )
to_acc = (
            session.query(Account)
            .filter(Account.id == to_id)
            .with_for_update()
            .first()
        )

This works for a single worker, but when you have multiple workers or parallel requests, isolation gets tricky.

4. Durability – Changes Persist

Durability guarantees that once a transaction is committed, the changes survive crashes.

Example

def durable_deposit(session, account_id, amount):
    try:
        acc = session.query(Account).filter(Account.id == account_id).with_for_update().first()
        acc.balance += amount
        session.commit()
        print(f"Deposited {amount} to {acc.name}. This change is permanent.")
    except Exception as e:
        session.rollback()
        print("Deposit failed:", e)

Even if your server restarts after session.commit(), the deposit remains in the database.

5. Full Example: Deposit + Transfer

def deposit_and_transfer(session, deposit_to, deposit_amt, transfer_from, transfer_to, transfer_amt):
    try:
        # Deposit
        acc = session.query(Account).filter(Account.id == deposit_to).with_for_update().first()
        acc.balance += deposit_amt
        print(f"Deposited {deposit_amt} to {acc.name}. New balance: {acc.balance}")

        # Transfer
        from_acc = session.query(Account).filter(Account.id == transfer_from).with_for_update().first()
        to_acc = session.query(Account).filter(Account.id == transfer_to).first()

        if from_acc.balance < transfer_amt:
            raise ValueError("Insufficient funds for transfer")

        from_acc.balance -= transfer_amt
        to_acc.balance += transfer_amt

        session.commit()
        print("Deposit and transfer successful.")
    except Exception as e:
        session.rollback()
        print("Operation failed:", e)

This ensures all steps succeed or all fail.

Why ACID Matters

• Atomicity: No partial transfers.

• Consistency: No invalid states.

• Isolation: No clashing transactions.

• Durability: Data persists after commit.

Coming Next: Isolation Under Pressure

Our examples work well when there’s one worker.
But with 4 workers handling two webhooks at the same time, things can break.
We’ll uncover why with_for_update() is not enough and how to use distributed locks or queues to fix it.