DEV Community

Cover image for ACID Operation in Backend Engineering: Building Data Integrity Like a Pro
Adeniyi Olanrewaju
Adeniyi Olanrewaju

Posted on

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

#python #programming #sqlalchemy #database

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()
Enter fullscreen mode Exit fullscreen mode

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")
Enter fullscreen mode Exit fullscreen mode

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() )
Enter fullscreen mode Exit fullscreen mode

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)
Enter fullscreen mode Exit fullscreen mode

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)
Enter fullscreen mode Exit fullscreen mode

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.

Top comments (0)