Database Transactions: ACID Properties

Imagine you're transferring $500 from your savings account to your checking account. The bank's system debits your savings first, then credits your checking. But what happens if the system crashes between those two operations? You'd lose $500 with it appearing nowhere — a financial disaster. This is exactly the problem that database transactions and ACID properties were designed to solve. Understanding them is foundational to building reliable, production-grade software.

What is a Database Transaction?

A transaction is a sequence of one or more database operations (reads and writes) that are treated as a single logical unit of work. A transaction either completes entirely — committing all its changes — or rolls back completely, leaving the database as if the transaction never happened.

The classic example is a bank transfer. Consider the following operations:

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. These four properties define what it means for a database to reliably process transactions. They were formally described by Jim Gray and Andreas Reuter in 1992 and remain the gold standard for transactional databases today.

Atomicity — All or Nothing

Atomicity guarantees that a transaction is treated as a single indivisible unit. Either all operations in the transaction succeed and are committed, or none of them are applied and the database is left unchanged. There is no in-between state.

The word "atomic" comes from the Greek atomos, meaning "indivisible." The database engine achieves atomicity using a write-ahead log (WAL) or undo log — it records intended changes before applying them, so it can roll back if anything goes wrong.

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 — 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

Consistency ensures that a transaction brings the database from one valid state to another. Any data written must satisfy all defined rules — including constraints, cascades, triggers, and application-level invariants. If a transaction would violate any rule, it must be rejected.

There are two layers of consistency:

• Database-level consistency: Enforced by the engine through constraints (NOT NULL, UNIQUE, FOREIGN KEY, CHECK)
• Application-level consistency: Enforced by your business logic — e.g., "a user cannot 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

Note that Consistency is the one ACID property that depends partly on the application developer — the database can only enforce rules you explicitly define. If your business rule is "an order total must equal the sum of its line items," that must be validated in code, not just trusted to the DB.

Isolation — Concurrent Transactions Don't Interfere

Isolation is arguably the most complex ACID property. It determines how and when changes made by one transaction become visible to other concurrent transactions. Without isolation, simultaneous operations on shared data can produce incorrect results even when each transaction is individually correct.

Isolation Anomalies

Without proper isolation, several anomalies can occur:

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

Durability guarantees that once a transaction is committed, it remains committed — even in the event of a system crash, power failure, or database restart. The data must be persisted to non-volatile storage before the commit is acknowledged to the client.

Databases achieve durability through a Write-Ahead Log (WAL) — every change is first written to a log file on disk before being applied 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

Transactions are all-or-nothing by default, but SQL provides savepoints — named checkpoints within a transaction that allow you to roll back to a specific point without aborting the entire transaction. This is useful for complex operations where you want to retry individual steps.

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

ACID is well-understood for single-node databases, but what about distributed systems — where data is spread across multiple servers or microservices? This is where things get significantly harder.

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 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 are not free. They introduce overhead from locking, logging, and coordination. Here are practical techniques to keep transactions performant:

-- 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 $$;

Key Takeaways

Database transactions and ACID properties are the bedrock of data integrity for any serious application. Let's recap what each property does for you:

"ACID properties don't make databases slow — they make databases correct. And correct is what your users actually need."
— Jim Gray, Turing Award Winner and co-author of Transaction Processing

Understanding transactions deeply will transform how you design schemas, write queries, and build application logic. The next time you reach for a framework's transaction helper, you'll know exactly what guarantees it's providing — and what could go wrong without it.

Mayur Dabhi

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