ACID: Atomicity

Atomicity is often summarized as "all or nothing," but useful PHP code needs a more precise question: exactly which operations belong to the transaction, and which operations are outside it?

Define The Atomic Unit From The Use Case

Suppose checkout creates an order, inserts its lines, and decrements stock. Those writes describe one accepted purchase. Keeping an order without its lines or decrementing stock without creating the order would be dishonest state, so they belong to one transaction.

<?php

declare(strict_types=1);

function createOrder(PDO $pdo, int $customerId, array $lines): int
{
    if ($lines === []) {
        throw new InvalidArgumentException('An order requires at least one line.');
    }

    $pdo->beginTransaction();

    try {
        $order = $pdo->prepare(
            'INSERT INTO orders (customer_id, status) VALUES (:customer_id, :status)'
        );
        $order->execute(['customer_id' => $customerId, 'status' => 'pending']);
        $orderId = (int) $pdo->lastInsertId();

        $insertLine = $pdo->prepare(
            'INSERT INTO order_lines (order_id, product_id, quantity)
             VALUES (:order_id, :product_id, :quantity)'
        );
        $reserve = $pdo->prepare(
            'UPDATE products
             SET stock = stock - :quantity
             WHERE id = :product_id AND stock >= :quantity'
        );

        foreach ($lines as $line) {
            $quantity = $line['quantity'];
            $productId = $line['product_id'];

            $reserve->execute(['quantity' => $quantity, 'product_id' => $productId]);

            if ($reserve->rowCount() !== 1) {
                throw new RuntimeException("Insufficient stock for product $productId.");
            }

            $insertLine->execute([
                'order_id' => $orderId,
                'product_id' => $productId,
                'quantity' => $quantity,
            ]);
        }

        $pdo->commit();

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

        throw $exception;
    }
}

The transaction starts after basic input validation and ends after the last related write. A stock failure on any line removes the inserted order and earlier line reservations.

Rollback Is Not Undoing Arbitrary PHP

A database rollback affects participating database changes on that connection. It does not reverse values assigned to PHP variables, output already sent to a browser, files written to disk, emails sent, or HTTP calls made.

<?php

declare(strict_types=1);

$notice = 'not sent';

try {
    $pdo->beginTransaction();
    $pdo->exec("INSERT INTO audit_log (message) VALUES ('started')");
    $notice = 'prepared in memory';
    throw new RuntimeException('Stop the operation.');
} catch (Throwable $exception) {
    if ($pdo->inTransaction()) {
        $pdo->rollBack();
    }
}

echo $notice, PHP_EOL;

The row is rolled back, but $notice remains changed because it is ordinary process memory. Atomicity has a concrete resource boundary.

Use A Transactional Storage Engine

Transaction calls cannot create atomicity when the underlying operation is not transactional. In MySQL and MariaDB, table engine choice matters. InnoDB supports transactions; historical or specialized engines may not offer the same behavior.

Some database statements also cause implicit commits or cannot be rolled back in the way ordinary data changes can. Schema migration tools therefore need database-specific knowledge. Do not assume that wrapping any SQL text in beginTransaction() makes it atomic.

PDO exposes a common API, but it does not erase differences between SQLite, MySQL, MariaDB, and PostgreSQL. Test the behavior on the database engine used in production.

Check Every Failure Path

Atomic code must roll back for all throwables, not only a selected exception class. PHP can fail through PDOException, TypeError, Error, or an application exception. Catching Throwable, rolling back, and rethrowing preserves the failure while cleaning up the transaction.

The inTransaction() guard matters because a connection failure or earlier rollback can leave no active transaction. Calling rollBack() unconditionally may throw another exception and obscure the original problem.

Do not swallow the original error:

<?php

declare(strict_types=1);

try {
    // Run transactional work.
} catch (Throwable $exception) {
    if ($pdo->inTransaction()) {
        $pdo->rollBack();
    }

    throw $exception;
}

Logging may add context, but logs must not contain credentials, payment details, or sensitive row values.

Atomic Writes Can Replace Read-Then-Write Logic

A transaction boundary groups multiple statements. A single SQL statement is also atomic at the statement level in normal transactional use. Prefer one conditional write when it expresses the rule clearly.

This unsafe shape has a race:

SELECT stock FROM products WHERE id = 10;
PHP checks stock >= 1;
UPDATE products SET stock = stock - 1 WHERE id = 10;

Two workers can both pass the PHP check. Instead, encode the condition in the update:

UPDATE products
SET stock = stock - 1
WHERE id = :id AND stock >= 1;

Then require rowCount() === 1. This combines the decision and write. It is still placed inside the larger order transaction when other writes must commit with it.

Savepoints Provide Partial Rollback, Not Independent Commit

A savepoint marks a position within a transaction. Code can roll back changes made after that point while keeping earlier changes pending.

SAVEPOINT before_optional_note;
INSERT INTO order_notes (order_id, note) VALUES (42, 'Gift wrap');
ROLLBACK TO SAVEPOINT before_optional_note;

The earlier transaction has not committed. If the outer transaction rolls back, all its work is still removed. Savepoints can help with optional sub-operations or test cleanup, but they do not turn an inner method into an independently atomic transaction.

PDO has no portable nested-transaction abstraction. Frameworks may count nesting levels or use savepoints, and their behavior differs. A method should not casually start a transaction when its caller may already own one.

Keep External Effects Recoverable

Consider this sequence:

1. Insert an invoice.
2. Send an invoice email.
3. Commit.

If email succeeds and commit fails, the customer receives a link to an invoice that does not exist. Reversing the last two steps produces another gap: commit can succeed and the process can crash before email is sent.

The transactional outbox pattern records work to perform:

BEGIN;
INSERT INTO invoices (id, customer_id, total_pence) VALUES (:id, :customer_id, :total);
INSERT INTO outbox_messages (event_id, event_type, payload)
VALUES (:event_id, 'InvoiceCreated', :payload);
COMMIT;

A worker reads unpublished outbox rows, sends the message, and records publication. This does not make the email transaction atomic. It makes the intent durable and retryable. The worker and consumer should be idempotent because a crash can cause duplicate delivery.

Atomicity Across Multiple Databases Is Harder

A local transaction normally covers one database connection. Writing to two separate databases does not become atomic because PHP begins one transaction on each. The first commit can succeed and the second fail.

Distributed transactions and two-phase commit exist, but they introduce coordinator, availability, operational, and recovery complexity. Many web systems instead use local transactions, durable messages, idempotent handlers, and compensating actions.

A compensating action is not a rollback in time. It is a new operation that semantically counteracts a committed one, such as issuing a refund after a reservation cannot be completed. The intermediate state may be visible, so workflows need explicit statuses.

Test Atomicity By Injecting Failure

A happy-path test proves little about rollback. Place a controlled failure after an early write and verify that no partial state remains.

<?php

declare(strict_types=1);

$pdo = new PDO('sqlite::memory:');
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$pdo->exec('CREATE TABLE entries (id INTEGER PRIMARY KEY, label TEXT NOT NULL UNIQUE)');

try {
    $pdo->beginTransaction();
    $pdo->exec("INSERT INTO entries (label) VALUES ('first')");
    $pdo->exec("INSERT INTO entries (label) VALUES ('first')");
    $pdo->commit();
} catch (Throwable $exception) {
    if ($pdo->inTransaction()) {
        $pdo->rollBack();
    }
}

$count = (int) $pdo->query('SELECT COUNT(*) FROM entries')->fetchColumn();
echo $count, PHP_EOL;

// Prints:
// 0

The unique constraint fails on the second insert. The assertion is not merely that an exception occurred; it is that the first insert did not survive.

Also test failures at different positions: before any write, after the first write, and immediately before commit. For external side effects, test that an outbox row is absent after rollback and present after commit.

Avoid Transactions That Are Too Large

Putting an entire request in one transaction may sound safer, but it can hold locks while templates render, APIs respond, or large files process. Long transactions increase contention, deadlock probability, log retention, and cleanup cost.

The atomic unit should match the business state change, not the lifespan of a controller. Validate request shape first. Fetch non-locking reference data where appropriate. Open the transaction for the decision and writes that require protection. Commit before rendering the response.

Large imports may need chunk-level transactions. One transaction for a million rows can consume excessive resources and make retry expensive. The tradeoff must be explicit: chunking means the entire import is not atomic, so the application needs progress records and a restart policy.

Review Checklist

When reviewing atomicity, ask:

• Which writes describe one business outcome?
• Does one layer own the full transaction?
• Does every throwable trigger rollback?
• Are participating tables and statements transactional?
• Can one conditional statement replace a racing read and write?
• Are external effects represented by durable work?
• Are savepoints being mistaken for independent transactions?
• Does a test force failure after an early write?
• Is the transaction short enough for the expected traffic?
• Is a multi-database workflow using explicit recovery rather than pretending to be atomic?

What You Should Be Able To Do

After this lesson, you should be able to define the atomic unit from a use case, implement commit and rollback safely with PDO, and prove rollback through failure injection. You should recognize that PHP memory and network side effects are outside a SQL transaction.

You should also be able to distinguish a savepoint from a nested commit, replace unsafe read-then-write logic with a conditional write, and choose an outbox or compensating workflow when one local database transaction cannot cover the whole operation.