ACID Transactions Overview

A transaction is a boundary around database operations that belong to one business action. Transferring credit, creating an order with its lines, reserving stock, and recording a payment are common examples. This lesson establishes the complete model. The following four lessons examine each letter independently.

Begin With An Invariant

A useful transaction starts with a rule that must remain true. Suppose an application transfers 300 pence from account 1 to account 2:

Before: account 1 = 1000, account 2 = 200, combined = 1200
After:  account 1 = 700,  account 2 = 500, combined = 1200

The combined balance must not change, and neither account should show only half of the transfer. The database helps enforce these rules, but PHP must choose the transaction boundary and issue safe statements.

<?php

declare(strict_types=1);

function transfer(PDO $pdo, int $fromId, int $toId, int $amount): void
{
    if ($amount < 1) {
        throw new InvalidArgumentException('Transfer amount must be positive.');
    }

    $pdo->beginTransaction();

    try {
        $debit = $pdo->prepare(
            'UPDATE accounts
             SET balance_pence = balance_pence - :amount
             WHERE id = :id AND balance_pence >= :amount'
        );
        $debit->execute(['amount' => $amount, 'id' => $fromId]);

        if ($debit->rowCount() !== 1) {
            throw new RuntimeException('Source account has insufficient funds.');
        }

        $credit = $pdo->prepare(
            'UPDATE accounts SET balance_pence = balance_pence + :amount WHERE id = :id'
        );
        $credit->execute(['amount' => $amount, 'id' => $toId]);

        if ($credit->rowCount() !== 1) {
            throw new RuntimeException('Destination account does not exist.');
        }

        $pdo->commit();
    } catch (Throwable $exception) {
        if ($pdo->inTransaction()) {
            $pdo->rollBack();
        }

        throw $exception;
    }
}

The conditional debit matters. Reading a balance and updating it later creates a race unless the read is protected. Here, the database checks the balance and subtracts the amount in one write.

Atomicity: One Transaction, One Outcome

Atomicity means the transaction's changes commit together or roll back together. It does not mean each PHP statement executes instantaneously, and it does not define what concurrent transactions can observe.

In the transfer, atomicity prevents the debit remaining committed if the credit fails. Calling rollBack() on every failure path is part of the application implementation. The database supplies transactional storage; PHP must use it correctly.

Atomicity has a boundary. Sending email, publishing to an unrelated message broker, writing a file, or calling a payment API does not automatically roll back with SQL. Coordinating database state with external systems requires designs such as a transactional outbox, stable idempotency keys, or compensating operations.

Consistency: Preserve Declared Rules

Consistency means a successful transaction moves the database from one valid state to another valid state. Validity depends on rules represented by constraints, application logic, triggers, and transaction code.

CREATE TABLE accounts (
    id INTEGER PRIMARY KEY,
    email VARCHAR(255) NOT NULL UNIQUE,
    balance_pence INTEGER NOT NULL CHECK (balance_pence >= 0)
);

NOT NULL, UNIQUE, foreign keys, and CHECK constraints reject invalid states even if another application path forgets a validation rule. PHP validation can provide clearer messages, while database constraints remain the final protection for stored data.

ACID consistency is not the same as consistency in distributed systems. A read replica can lag behind its primary even though every transaction on each database preserves local constraints. State the exact guarantee instead of saying only that a system is consistent.

Isolation: Concurrent Work Must Not Corrupt Decisions

Isolation describes how concurrent transactions interact and what effects they can observe. Production applications have multiple PHP workers, so two requests can read and update the same record at nearly the same time.

Without a safe write or lock, two purchases might both observe one item in stock and both claim it. Transactions alone do not prevent every race. The behavior depends on statements, indexes, locks, constraints, and isolation level.

An atomic conditional update is often safer than a read followed by a write:

UPDATE inventory
SET available = available - 1
WHERE product_id = :product_id
  AND available > 0;

The application checks whether one row changed. Another option is a locking read such as SELECT ... FOR UPDATE in databases that support it. Choose according to the workflow and database engine.

Common isolation-level names are Read Uncommitted, Read Committed, Repeatable Read, and Serializable. Precise behavior differs between engines, especially around snapshots, predicate locks, and phantom rows. Verify the documentation for the database and version actually deployed.

Durability: Acknowledged Commits Survive

Durability means that after the database reports a successful commit, the committed state survives ordinary failures covered by the configured storage guarantees. Databases commonly use transaction logs or write-ahead logs so recovery can replay committed work after a crash.

The effective guarantee depends on the database engine, storage, flush settings, replication mode, and infrastructure. Configuration can exchange stronger synchronization for throughput. Application developers should not silently assume every environment uses the strongest settings.

Durability is not backup. A durable accidental deletion remains deleted, and replication may copy it. Backups, point-in-time recovery, and tested restore procedures protect against different failures. Durability protects an acknowledged commit; recovery planning protects against corruption, human error, and broader loss.

The Four Properties Work Together

Consider creating an order:

1. Insert the order header.
2. Insert its order lines.
3. Decrement inventory.
4. Record the payment reference.

Atomicity prevents a partial order. Consistency protects foreign keys, quantities, and non-negative stock. Isolation stops concurrent purchases making incompatible decisions. Durability retains the committed order after a crash.

These properties do not prove that business logic is correct. A transaction can atomically, consistently, and durably charge the wrong total if PHP calculated it incorrectly. ACID governs transaction behavior, not product requirements.

Choose A Short Transaction Boundary

Open a transaction after slow validation and external calls are complete, perform the required database work, then finish promptly. Holding a transaction open while waiting for HTTP requests or user input can retain locks, increase contention, and make failures harder to recover from.

Transaction ownership should be clear. If saveOrder() commits independently while reserveStock() starts another transaction, a use case cannot make both operations atomic. An application service or unit-of-work boundary should normally own the transaction around all persistence operations required by that use case.

PDO does not provide portable automatic nested transactions. Some projects use savepoints, but a savepoint does not create an independent outer commit. Define one owner and document whether lower-level methods expect an active transaction.

Preserve Failures During Rollback

A reusable wrapper can preserve the original exception:

<?php

declare(strict_types=1);

function inTransaction(PDO $pdo, callable $operation): mixed
{
    $pdo->beginTransaction();

    try {
        $result = $operation($pdo);
        $pdo->commit();

        return $result;
    } catch (Throwable $exception) {
        if ($pdo->inTransaction()) {
            $pdo->rollBack();
        }

        throw $exception;
    }
}

Do not roll back and then pretend the operation succeeded. Translate an exception only at a boundary that can add useful meaning. Deadlocks and serialization failures may be retryable, but retries need limits and the complete transaction body must be safe to execute again.

A lost connection can make an outcome ambiguous: the server may have committed even though the client did not receive confirmation. Payments and message consumers therefore use stable operation identifiers and idempotent behavior rather than relying only on a local retry loop.

External Side Effects Need Another Design

A SQL transaction cannot make a third-party email or broker publish atomic. Publishing before commit risks announcing work that later rolls back. Publishing after commit risks losing the event if the process crashes between the commit and publish.

A transactional outbox addresses this gap. The application writes the business data and an outbox row in one transaction. A worker later publishes pending rows, records success, and safely retries failures. Consumers should still handle duplicates because delivery may be at least once.

The outbox does not extend the database transaction across the network. It turns an unrecoverable timing gap into durable work that can be retried and observed.

ACID Depends On Scope And Configuration

SQLite, MySQL, MariaDB, and PostgreSQL support transactions, but defaults and details differ. MySQL table engines matter: InnoDB is transactional, while assumptions based on another engine can be wrong. Schema statements can cause implicit commits in some systems. DDL transaction support, locking syntax, and default isolation levels vary.

Other storage products may advertise ACID for one item, one partition, several documents, or a wider scope. The acronym alone is insufficient. Ask:

• What operations participate in one transaction?
• Which records or partitions can it include?
• What isolation behavior is provided?
• When is commit acknowledged?
• Which failures are covered?
• Which settings can weaken the guarantee?

Review Transactional PHP Carefully

Before approving a transactional workflow, verify that:

• the business invariant is explicit;
• every related database write belongs to one transaction;
• all failure paths roll back and preserve the error;
• constraints protect important stored rules;
• concurrent requests cannot pass an unsafe read-then-write check;
• one layer clearly owns the transaction;
• avoidable network work stays outside the transaction;
• retries are bounded and safe;
• external side effects use an outbox or another recovery strategy;
• durability is not confused with backup and restore.

What You Should Be Able To Do

After this lesson, you should be able to define all four ACID properties without merging them into one concept. You should be able to identify an invariant, choose a transaction boundary, explain why constraints and concurrency controls still matter, and distinguish a committed database change from an external side effect.

You should also challenge imprecise claims. "It uses a transaction" is not enough. Ask what is atomic, which rules establish consistency, how concurrent work is isolated, and what configuration supports the durability promised after commit.