Database Transactions: ACID Properties

Say a checkout endpoint creates an order, decrements stock, and charges a wallet balance in three separate queries. The PHP worker gets killed after the second query. Now you have an order and reduced stock, but nobody paid, and nothing in your logs says why. Bugs like this usually show up weeks later as numbers that don't add up.

That's the problem database transactions solve, and ACID is the set of guarantees they give you. This post goes through each of the four properties with SQL and framework examples, then covers isolation levels, durability settings, savepoints, distributed transactions, and the mistakes I see most often.

What is a Database Transaction?

You group several reads and writes together and tell the database to treat them as one unit. That group is a transaction. Either every change gets committed, or the whole thing rolls back and the database looks as if you never started.

The classic example is a bank transfer:

1. Read the balance of Account A
2. Deduct $500 from Account A
3. Read the balance of Account B
4. Add $500 to Account B

All four steps must succeed together. If step 2 succeeds but step 4 fails, money disappears. Transactions guarantee this "all or nothing" behavior.

-- Start a transaction
BEGIN;

-- Deduct from Account A
UPDATE accounts SET balance = balance - 500 WHERE id = 1;

-- Add to Account B
UPDATE accounts SET balance = balance + 500 WHERE id = 2;

-- If everything succeeded, commit
COMMIT;

-- If something went wrong, roll back all changes
-- ROLLBACK;

ACID: The Four Pillars

ACID stands for Atomicity, Consistency, Isolation, and Durability. Together they describe what "reliable" means for a transactional database. The acronym was coined by Theo Härder and Andreas Reuter in 1983, building on Jim Gray's earlier work on transaction processing.

Atomicity: All or Nothing

With atomicity, there's no in-between state. Every operation in the transaction gets applied, or none of them do.

Under the hood, the engine keeps a write-ahead log (WAL) or an undo log. It records what it's about to change before changing it, so it can reverse everything if something fails partway.

START TRANSACTION;

-- Step 1: Deduct from sender
UPDATE accounts
SET balance = balance - 500
WHERE user_id = 1 AND balance >= 500;

-- Check if the update actually affected a row
-- (balance was sufficient)
-- In application code, check affected rows here

-- Step 2: Credit to receiver
UPDATE accounts
SET balance = balance + 500
WHERE user_id = 2;

-- Step 3: Log the transfer
INSERT INTO transfer_log (from_user, to_user, amount, created_at)
VALUES (1, 2, 500, NOW());

-- All succeeded, so commit
COMMIT;

-- If any step failed, the application would call:
-- ROLLBACK;
-- And the database reverts to the state before BEGIN

Atomicity in Application Code

Most frameworks provide transaction helpers so you don't have to manually write BEGIN/COMMIT/ROLLBACK:

// Laravel automatically handles ROLLBACK on exception
DB::transaction(function () use ($fromId, $toId, $amount) {
    // Deduct from sender
    Account::where('user_id', $fromId)
        ->decrement('balance', $amount);

    // Credit receiver
    Account::where('user_id', $toId)
        ->increment('balance', $amount);

    // Log the transfer
    TransferLog::create([
        'from_user' => $fromId,
        'to_user'   => $toId,
        'amount'    => $amount,
    ]);
});
// If any exception is thrown inside, Laravel auto-rolls back

const t = await sequelize.transaction();

try {
    await Account.decrement('balance', {
        by: amount,
        where: { userId: fromId },
        transaction: t
    });

    await Account.increment('balance', {
        by: amount,
        where: { userId: toId },
        transaction: t
    });

    await TransferLog.create(
        { fromUser: fromId, toUser: toId, amount },
        { transaction: t }
    );

    await t.commit();
} catch (error) {
    await t.rollback();
    throw error;
}

from sqlalchemy.orm import Session

def transfer_funds(db: Session, from_id: int, to_id: int, amount: float):
    try:
        # SQLAlchemy sessions are transactional by default
        sender = db.query(Account).filter_by(user_id=from_id).one()
        receiver = db.query(Account).filter_by(user_id=to_id).one()

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

        sender.balance -= amount
        receiver.balance += amount

        db.add(TransferLog(
            from_user=from_id, to_user=to_id, amount=amount
        ))

        db.commit()
    except Exception:
        db.rollback()
        raise

Consistency: Valid State Before and After

A transaction should move the database from one valid state to another. Whatever it writes has to satisfy your constraints, cascades, triggers, and application rules. If it would break one, it gets rejected.

In practice consistency lives in two places:

• Database level: the engine enforces constraints (NOT NULL, UNIQUE, FOREIGN KEY, CHECK)
• Application level: your business logic enforces rules like "a user can't have a negative balance"

CREATE TABLE accounts (
    id          INT PRIMARY KEY AUTO_INCREMENT,
    user_id     INT NOT NULL,
    balance     DECIMAL(15, 2) NOT NULL DEFAULT 0.00,

    -- CHECK constraint: balance can never go below zero
    CONSTRAINT chk_balance CHECK (balance >= 0),

    -- Foreign key: user must exist
    CONSTRAINT fk_user FOREIGN KEY (user_id)
        REFERENCES users(id) ON DELETE CASCADE
);

-- This will FAIL: consistency violation
UPDATE accounts SET balance = -100 WHERE id = 1;
-- ERROR 3819: Check constraint 'chk_balance' is violated.

-- This will ALSO FAIL: foreign key violation
INSERT INTO accounts (user_id, balance) VALUES (9999, 100.00);
-- ERROR 1452: Cannot add or update a child row: a foreign key constraint fails

Consistency is the one ACID property that's partly your job. The database can only enforce rules you've actually told it about. If your rule is "an order total must equal the sum of its line items," you have to check that in code (or write a trigger for it).

Isolation: Concurrent Transactions Don't Interfere

Two requests hit the same row at the same moment. Each one is correct on its own, yet together they produce the wrong result. Isolation controls when one transaction's changes become visible to others, and it's the property with the most knobs and the most surprises.

Isolation Anomalies

These are the anomalies you get with weak or no isolation:

Isolation Levels

The SQL standard defines four isolation levels, each preventing a different set of anomalies at the cost of more or less concurrency:

-- MySQL: Set isolation level for next transaction
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
START TRANSACTION;
-- ... your queries ...
COMMIT;

-- PostgreSQL: Set for the current transaction
BEGIN ISOLATION LEVEL REPEATABLE READ;
-- ... your queries ...
COMMIT;

-- Check current isolation level (MySQL)
SELECT @@transaction_isolation;

-- Check current isolation level (PostgreSQL)
SHOW transaction_isolation;

How Isolation Is Implemented: MVCC

Most modern databases (PostgreSQL, MySQL/InnoDB, SQLite) implement isolation using Multi-Version Concurrency Control (MVCC). Instead of locking rows to block readers, MVCC keeps multiple versions of each row. Each transaction sees a consistent snapshot of the database from the moment it started, allowing reads and writes to proceed concurrently without blocking each other.

Durability: Committed Data Survives

Once the database tells your app "committed," that change has to survive a crash, a power cut, or a restart. That's durability, and it means the data must reach non-volatile storage before the commit is acknowledged.

Databases do this with a Write-Ahead Log (WAL). Every change goes to a log file on disk first, and only later to the data files. On recovery after a crash, the database replays the WAL to restore committed transactions that may not have been flushed to the main data files.

-- postgresql.conf key settings for durability

-- MUST be on for full durability (default: on)
-- Turning off risks data loss on crash for speed
fsync = on

-- Controls WAL sync method (varies by OS)
-- fdatasync, fsync, open_sync, open_datasync
wal_sync_method = fdatasync

-- Synchronous commit: wait for WAL flush before acknowledging
-- off = async commit (faster, slight data loss risk on crash)
-- on = synchronous commit (default, fully durable)
synchronous_commit = on

-- MySQL InnoDB equivalent:
-- innodb_flush_log_at_trx_commit = 1 (fully durable, default)
-- innodb_flush_log_at_trx_commit = 0 (flush every second, risky)
-- innodb_flush_log_at_trx_commit = 2 (flush on commit, OS may buffer)

Savepoints: Partial Rollbacks

Sometimes you want to undo only part of a transaction. Savepoints are named checkpoints inside a transaction: you can roll back to one without throwing away everything before it. They're handy when one step might fail and you'd rather retry it or try something else. Laravel uses them under the hood when you nest DB::transaction() calls.

BEGIN;

-- Step 1: Create order header
INSERT INTO orders (user_id, total) VALUES (42, 150.00);
-- Save current point
SAVEPOINT after_order;

-- Step 2: Add line items
INSERT INTO order_items (order_id, product_id, quantity, price)
VALUES (LAST_INSERT_ID(), 7, 2, 50.00);

INSERT INTO order_items (order_id, product_id, quantity, price)
VALUES (LAST_INSERT_ID(), 12, 1, 50.00);

-- Oops, one item is out of stock, but we can keep the order
ROLLBACK TO SAVEPOINT after_order;
-- order row still exists, but line items were undone

-- Insert different items
INSERT INTO order_items (order_id, product_id, quantity, price)
VALUES (LAST_INSERT_ID(), 5, 3, 50.00);

-- Release savepoint (optional cleanup)
RELEASE SAVEPOINT after_order;

COMMIT;

Distributed Transactions and the CAP Theorem

On a single database server, all of this is well understood. Once your data is split across several servers or microservices, it gets a lot harder, because no single engine owns the whole transaction anymore.

Two-Phase Commit (2PC)

The traditional approach to distributed transactions is the Two-Phase Commit (2PC) protocol. A coordinator asks all participant nodes to "prepare" (phase 1), and if all agree, instructs them to "commit" (phase 2). While correct, 2PC is slow, blocking, and problematic if the coordinator crashes.

The CAP Theorem

The CAP Theorem states that a distributed system can guarantee at most two of three properties simultaneously:

• Consistency (C): Every read receives the most recent write
• Availability (A): Every request receives a response (not necessarily the latest data)
• Partition Tolerance (P): The system continues despite network partitions

Since network partitions are unavoidable in distributed systems, real-world systems must choose between CP (consistent but may be unavailable during a partition, e.g. HBase, ZooKeeper) or AP (available but may return stale data, e.g. Cassandra, DynamoDB). Traditional RDBMS on a single node is CA but not partition-tolerant by nature.

Transactions and Performance

Transactions aren't free. Locking, logging, and coordination all cost something, and most of the problems show up as lock waits under load.

-- Instead of one massive transaction for 1 million rows:
-- BEGIN; UPDATE ... (1M rows); COMMIT;  ← BAD

-- Use chunked transactions for bulk operations
DO $$
DECLARE
    batch_size INT := 1000;
    offset_val INT := 0;
    affected   INT;
BEGIN
    LOOP
        BEGIN
            UPDATE users
            SET    tier = 'gold'
            WHERE  total_spend > 1000
              AND  tier != 'gold'
              AND  id > offset_val
            LIMIT batch_size;

            GET DIAGNOSTICS affected = ROW_COUNT;
            EXIT WHEN affected = 0;

            offset_val := offset_val + batch_size;
            COMMIT;
        END;
    END LOOP;
END $$;

Mistakes I See Most Often

When wouldn't I bother with an explicit transaction? A single INSERT or UPDATE is already atomic on its own, so wrapping it adds nothing. Read-only pages rarely need one either, unless you need several queries to see the same snapshot (a report that totals several tables, say). And across services, I'd reach for an outbox table or a saga with compensating actions before I'd try 2PC. Inside one database, though, any time two writes have to agree with each other, they belong in the same transaction.

Mayur Dabhi

Full Stack Developer with 5+ years of experience building scalable web applications with Laravel, React, and Node.js.