KAHIBARO
Discord Login Register ☰
☰

9.14 ACID Properties

Table of Contents

Why ACID Properties Matter

When you build backend applications, your data must stay correct, even when:

Relational databases like PostgreSQL rely on transactions and ACID properties to keep data safe and consistent in all these situations.

ACID is a set of guarantees that describe how transactional databases behave:

You do not usually configure ACID directly. Instead, you use transactions, and the database uses ACID rules to make your transactions safe.

In this chapter, you will learn what each letter means, with practical backend examples.

A for Atomicity

What Atomicity Means

Atomicity says: a transaction is all or nothing.

A transaction can contain many SQL statements. With atomicity, either:

Nothing is left half-applied.

Atomicity rule
A transaction is indivisible: it cannot be partially committed.
If any part of a transaction fails, the entire transaction must roll back.

Real-World Example: Money Transfer

Imagine a bank transfer between two accounts:

In SQL, this might be:

sql 
BEGIN;
UPDATE accounts
SET balance = balance - 50
WHERE id = 1;  -- Alice
UPDATE accounts
SET balance = balance + 50
WHERE id = 2;  -- Bob
COMMIT;

Atomicity ensures that both updates happen together:

If something goes wrong between BEGIN and COMMIT, the whole transaction is undone with ROLLBACK:

sql 
BEGIN;
UPDATE accounts
SET balance = balance - 50
WHERE id = 1;
-- Something fails here, for example:
-- UPDATE accounts SET balance = balance + 50 WHERE id = 2;
-- raises an error
ROLLBACK;  -- undo the change to Alice's balance

Atomicity is critical anywhere you need changes to move together:

Grouping Operations into One Transaction

In your backend code, you usually do not write BEGIN and COMMIT by hand. Your database driver or ORM provides a transaction API.

Pseudo code example:

python 
with db.transaction():  # start transaction
    db.execute("UPDATE accounts SET balance = balance - 50 WHERE id = 1")
    db.execute("UPDATE accounts SET balance = balance + 50 WHERE id = 2")
# leaving the 'with' block commits on success, or rolls back on error

If any execute call fails, the context manager rolls back automatically. Atomicity is then enforced by the database.

C for Consistency

What Consistency Means

Consistency says: a transaction must move the database from one valid state to another valid state.

A valid state is defined by:

Examples of constraints:

Consistency rule
Every committed transaction must preserve all defined constraints and rules.
The database must never commit a state that violates its own invariants.

Consistency has two parts:

Database-Level Consistency: Constraints

Examples of SQL constraints:

sql 
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    email TEXT NOT NULL UNIQUE,
    age INT CHECK (age >= 0),
    account_id INT REFERENCES accounts(id)
);

If you try to insert invalid data, the database will reject it:

sql 
INSERT INTO users (email, age, account_id)
VALUES ('john@example.com', -5, 123);  -- age < 0
-- ERROR: new row for relation "users" violates check constraint "users_age_check"

Atomicity and consistency work together:

Application-Level Consistency: Business Rules

Not all rules can be expressed as SQL constraints. For example:

These rules live in your backend code. You use checks before running SQL.

Example:

python 
def create_order(user_id, product_id, quantity):
    product = db.fetch_one("SELECT stock FROM products WHERE id = %s", (product_id,))
    if product["stock"] < quantity:
        raise ValueError("Not enough stock")
    with db.transaction():
        db.execute(
            "INSERT INTO orders (user_id, product_id, quantity) VALUES (%s, %s, %s)",
            (user_id, product_id, quantity),
        )
        db.execute(
            "UPDATE products SET stock = stock - %s WHERE id = %s",
            (quantity, product_id),
        )

Here, consistency depends on both:

I for Isolation

What Isolation Means

Isolation says: transactions should not see each other's partial work.

When several transactions run at the same time, each one should behave as if it is the only one running, at least from its own point of view.

In practice, databases offer different isolation levels, which balance:

You will learn detailed isolation levels in a separate chapter. Here we focus on intuition and typical problems.

Isolation rule
Concurrent transactions must not interfere in a way that produces incorrect or inconsistent results.
Each transaction should see a controlled view of data, independent from other unfinished transactions.

Typical Concurrency Problems

When isolation is weak, you can get strange behaviors.

Problem typeDescription in simple words
Dirty readA transaction reads data that another transaction has not committed yet
Non-repeatable readA transaction reads the same row twice and sees different values
Phantom readA transaction runs the same query twice and sees different sets of rows
Lost updateTwo transactions update the same row, and one update overwrites the other

Lost Update Example

Imagine two HTTP requests at the same time:

  1. User A opens a page to update their profile name
  2. User B also opens the same profile page
  3. Both change the name and click "Save" within a short time

Sequence:

If nothing prevents it, the final value is John B. T1's update is lost.

With proper isolation and sometimes extra logic, you can detect or prevent this.

One common technique is optimistic locking:

sql 
ALTER TABLE users ADD COLUMN version INT NOT NULL DEFAULT 0;

Then you update with a condition:

sql 
UPDATE users
SET name = 'John A', version = version + 1
WHERE id = 1 AND version = 3;

If another transaction already changed version, this update affects 0 rows. Your backend notices and retries or shows a conflict to the user.

Isolation Levels Overview

Most relational databases support these isolation levels:

Isolation levelTypical problems allowed
Read UncommittedDirty reads, non-repeatable reads, phantoms
Read CommittedNo dirty reads, but non-repeatable and phantoms allowed
Repeatable ReadNo dirty or non-repeatable reads, some phantoms may appear depending on DB
SerializablePrevents all of the above, behaves as if transactions run one by one

Isolating more strongly usually reduces concurrency or requires more internal work, such as locking or retrying transactions.

As a backend developer, you must:

Example in PostgreSQL:

sql 
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN;
-- transactional queries here
COMMIT;

D for Durability

What Durability Means

Durability says: once a transaction is committed, its changes must not be lost, even if:

When the database comes back up, committed data must still be there.

Durability rule
After a transaction commits, the database must guarantee
that its changes will persist, even in the face of crashes.

How Databases Implement Durability

Implementation details vary, but common techniques include:

As a backend developer, you do not implement these. But you must be aware that:

Durability and Acknowledging Requests

In backend development, durability affects when you tell the user "success".

Example:

  1. Your API receives a POST /orders request
  2. You insert a new row into orders table
  3. The transaction commits
  4. You return HTTP 201 Created

If the database says "transaction committed", you can assume the order is safely stored. Even if the server crashes right after sending the response, the order is still in the database.

If you ever use features that relax durability, you must be careful about when you send a "success" response, because some writes might still be only in memory and not safe on disk.

How ACID Properties Work Together

ACID in One Transfer Example

Return to the money transfer example and see how ACID works as a whole.

Transaction:

sql 
BEGIN;
UPDATE accounts
SET balance = balance - 50
WHERE id = 1;  -- Alice
UPDATE accounts
SET balance = balance + 50
WHERE id = 2;  -- Bob
COMMIT;

ACID guarantees:

Without any one of these, your data could become corrupted or surprising:

ACID and Backend Code

In practice, you usually call:

Example pattern in a backend service:

python 
def transfer(db, from_id, to_id, amount):
    with db.transaction():  # atomic, durable unit
        # checks for consistency
        from_account = db.fetch_one("SELECT balance FROM accounts WHERE id = %s FOR UPDATE", (from_id,))
        if from_account["balance"] < amount:
            raise ValueError("Insufficient funds")
        # changes
        db.execute(
            "UPDATE accounts SET balance = balance - %s WHERE id = %s",
            (amount, from_id),
        )
        db.execute(
            "UPDATE accounts SET balance = balance + %s WHERE id = %s",
            (amount, to_id),
        )
    # on exit, commit is called. If anything fails, rollback happens.

In this example:

Common Misunderstandings About ACID

Misunderstanding 1: ACID Means Everything Is Always Perfect

ACID does not mean:

ACID only describes behavior inside the database, with its own constraints and configuration.

You still must:

Misunderstanding 2: ACID Is Only for Banks

Banking is a classic example, but ACID is useful in many areas:

Any time you think "these operations must happen together", you are thinking about atomic transactions and ACID properties.

Misunderstanding 3: NoSQL Databases Are Always Non-ACID

Some NoSQL databases offer ACID properties, at least at the document level. Others offer limited forms or tunable consistency.

Relational databases are traditionally the strongest in ACID guarantees, but modern systems vary. When choosing a database, always check:

Summary

As a backend developer, you make use of ACID by:

Understanding ACID is a foundation for every other database topic you will encounter, from schema design to performance and scalability.

Views: 10

Comments

Please login to add a comment.

Don't have an account? Register now!

© 2025 Kahibaro
Visitors: 3134 / 24h
Impressum / Imprint
Datenschutz / Data Policy