Transactions

A database transaction groups related statements into one controlled unit. The application begins the transaction, performs the required reads and writes, then either commits the complete result or rolls it back after failure.

Use a transaction when partial completion would leave stored data dishonest. Typical cases include transfers, orders with lines, inventory reservation, user registration across several tables, and an audit record that must accompany a state change.

A Complete PDO Transaction

This example transfers 500 pence between two accounts in SQLite:

<?php

declare(strict_types=1);

$pdo = new PDO('sqlite::memory:');
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$pdo->exec('CREATE TABLE accounts (
    id INTEGER PRIMARY KEY,
    balance_pence INTEGER NOT NULL CHECK (balance_pence >= 0)
)');
$pdo->exec('INSERT INTO accounts (id, balance_pence) VALUES (1, 1000), (2, 200)');

$pdo->beginTransaction();

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

    if ($debit->rowCount() !== 1) {
        throw new RuntimeException('Debit failed.');
    }

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

    if ($credit->rowCount() !== 1) {
        throw new RuntimeException('Credit failed.');
    }

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

    throw $exception;
}

echo $pdo->query('SELECT balance_pence FROM accounts WHERE id = 2')->fetchColumn(), PHP_EOL;

// Prints:
// 700

The conditional debit prevents the balance becoming negative and closes a common read-then-write race. Both updates participate in the same transaction.

Begin, Commit, And Roll Back

beginTransaction() disables ordinary auto-commit behavior for participating statements on that connection. commit() asks the database to make the transaction's changes permanent. rollBack() abandons uncommitted changes.

PDO returns booleans from these methods, but with exception error mode enabled, failures are normally handled as exceptions. Configure PDO::ATTR_ERRMODE to PDO::ERRMODE_EXCEPTION for clear control flow.

Always guard rollback with inTransaction(). The connection may have already rolled back, failed, or encountered a database operation that ended the transaction. A second rollback error can hide the original failure.

Catching Throwable covers application exceptions, PDOException, TypeError, and Error. Rethrow after cleanup unless the current boundary can translate the failure into a meaningful application result.

Choose The Boundary From The Use Case

A transaction should contain every database change required for one accepted business outcome, but no unrelated slow work.

For checkout:

Validate request shape
Call payment provider or perform remote checks
BEGIN
Create order
Insert order lines
Reserve stock
Insert outbox event
COMMIT
Render response

Calling a remote API while holding database locks increases contention and creates awkward failure combinations. Do slow external work before the transaction when business semantics permit. Represent after-commit work through an outbox or queue rather than pretending an HTTP call participates in SQL rollback.

Do not wrap an entire controller in a transaction by default. Template rendering, response serialization, logging, and unrelated reads usually do not belong inside it.

Validation Happens At Different Times

Validate request types and simple rules before opening the transaction:

<?php

declare(strict_types=1);

function requirePositiveAmount(int $amountPence): int
{
    if ($amountPence < 1) {
        throw new InvalidArgumentException('Amount must be positive.');
    }

    return $amountPence;
}

Rules that depend on current database state may need enforcement inside the transaction. Even then, prefer constraints or atomic conditional writes where possible. A PHP check performed before the transaction can become stale before the write.

Database constraints remain essential. A transaction groups changes, but it does not define valid values by itself. Use foreign keys, unique constraints, NOT NULL, and CHECK constraints for stable stored invariants.

Transaction Ownership Must Be Clear

A common architecture problem is lower-level methods starting and committing their own transactions:

OrderRepository::save() begins and commits
InventoryRepository::reserve() begins and commits

An application service cannot make both operations one unit if each repository commits independently. The use-case layer should usually own the transaction and call repositories with the same connection or unit-of-work context.

Repository methods should document whether they require an active transaction. Avoid hidden commits in utility methods. Transaction boundaries are part of application behavior and deserve visible code.

Nested Transactions Are Not Portable PDO Behavior

Calling beginTransaction() while a PDO transaction is already active does not create a portable independent nested transaction. Frameworks sometimes simulate nesting with counters or savepoints.

A savepoint allows partial rollback inside one outer transaction:

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

The outer transaction still owns the final commit. Rolling it back removes work before and after the savepoint. Do not describe a savepoint as an inner committed transaction.

Establish one transaction owner instead of relying on accidental nesting behavior.

Transactions And Concurrency

A transaction does not make unsafe application logic automatically safe. Two workers can read the same value and make conflicting decisions.

Use techniques appropriate to the rule:

• unique constraints for uniqueness races;
• conditional updates for stock or balance checks;
• version columns for optimistic concurrency;
• SELECT ... FOR UPDATE or equivalent locking reads when a decision requires protected rows;
• Serializable isolation for selected cross-row invariants;
• retry handling for recognized deadlock or serialization failures.

The dedicated Isolation lesson develops these choices in detail. At this stage, remember to inspect the concurrent timeline rather than only one request's code.

Keep Transactions Short

Long transactions retain snapshots, locks, log records, and database resources. They increase blocking, deadlock probability, and recovery work.

Avoid:

• waiting for user input;
• making remote HTTP requests;
• sending email;
• processing large files;
• rendering templates;
• sleeping for retry delays;
• scanning unindexed large tables.

Large imports may use one transaction per bounded batch. That means the whole import is not atomic, so track progress and define restart behavior. A million-row transaction is not automatically safer than controlled chunks.

External Systems Are Outside The Transaction

This sequence is unsafe:

BEGIN
Insert invoice
Send email
COMMIT

If email succeeds and commit fails, the message describes state that does not exist. Committing first has the opposite gap: the process can crash before email.

A transactional outbox records an event in the same transaction as the invoice. A worker publishes it later with retries and idempotency. The external system remains outside SQL, but the intent is durable and recoverable.

Similarly, writing a file, charging a payment provider, or publishing to a broker is not rolled back by PDO::rollBack().

Handle Retryable Failures Deliberately

Databases may abort a transaction because of deadlock or serialization conflict. Retrying can be correct when:

• the error is positively identified as retryable for the driver;
• the whole transaction body is safe to repeat;
• external side effects are not repeated;
• attempts are bounded;
• failures are logged with useful context;
• the final failure is surfaced.

Do not retry every PDOException. Syntax errors, missing tables, invalid credentials, and constraint violations generally need different handling.

Lock shared resources in a consistent order to reduce deadlocks. For account transfers, lock account rows by ascending ID rather than source then destination.

Understand Implicit Commits And Engine Differences

PDO presents a common interface, but transaction behavior is database-specific. Some schema operations cause implicit commits. DDL rollback support differs. MySQL/MariaDB table engines affect whether data operations are transactional. SQLite locking differs from server databases.

Test migrations and transaction assumptions on the production engine. An in-memory SQLite test is useful for application structure but does not prove MySQL or PostgreSQL concurrency behavior.

Test Both Commit And Rollback

A transaction test should inspect resulting state, not only exceptions.

<?php

declare(strict_types=1);

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

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

echo $pdo->query('SELECT COUNT(*) FROM labels')->fetchColumn(), PHP_EOL;

// Prints:
// 0

Also test the successful path, failures after different writes, constraint behavior, and any retry or outbox logic. Run true concurrency tests with separate connections against the real database engine.

Avoid Common Transaction Mistakes

Watch for:

• beginning after the first write;
• committing before all related writes finish;
• catching only Exception when an Error can escape;
• rolling back without checking transaction state;
• swallowing an exception and returning success;
• using separate connections for work expected to share one transaction;
• starting transactions in multiple repositories;
• holding locks during network calls;
• assuming rollback reverses files or messages;
• testing only the happy path.

Code review should identify the invariant and final state, not merely confirm that beginTransaction() appears somewhere.

Relation To ACID

Transactions are the mechanism through which relational databases provide ACID guarantees:

• Atomicity groups commit and rollback.
• Consistency preserves declared invariants.
• Isolation governs concurrent interaction.
• Durability protects acknowledged commits.

Each property has its own lesson because the transaction API alone does not explain constraints, anomalies, storage logs, replication, or recovery.

Review Checklist

Before approving transactional PHP code, confirm:

• one business outcome defines the boundary;
• all required writes use the same connection;
• one layer owns commit and rollback;
• every throwable cleans up and remains visible;
• validation and constraints protect stored rules;
• concurrency-sensitive decisions are atomic or locked;
• the transaction is short;
• external effects use a recoverable design;
• retry policy is bounded and driver-aware;
• tests prove both committed and rolled-back state;
• engine-specific assumptions are documented.

What You Should Be Able To Do

After this lesson, you should be able to implement a complete PDO transaction, choose a boundary from a use case, keep transaction ownership visible, and roll back safely without hiding the original error.

You should also understand why validation, constraints, concurrency controls, outbox messaging, engine behavior, and failure tests remain necessary. A transaction is not a magic safety wrapper; it is a precise database boundary that application code must design and verify.