Database

Use Database Transactions in PHP and MySQL

Protect multi-step database operations with transactions, rollback and consistent error handling.

Overview

A transaction groups related database operations so they succeed or fail as a unit. This is essential when one business action updates multiple rows, such as creating an order and reducing inventory.

Use a transactional storage engine

For MySQL, verify that the tables participating in the transaction use a transactional engine such as InnoDB. A transaction cannot provide the expected atomicity across non-transactional tables.

PDO transaction pattern

<?php
$pdo->beginTransaction();

try {
    $order = $pdo->prepare(
        'INSERT INTO orders (customer_id, total) VALUES (:customer, :total)'
    );
    $order->execute(['customer' => $customerId, 'total' => $total]);

    $stock = $pdo->prepare(
        'UPDATE products SET quantity = quantity - :qty
         WHERE id = :id AND quantity >= :qty'
    );
    $stock->execute(['qty' => $quantity, 'id' => $productId]);

    if ($stock->rowCount() !== 1) {
        throw new RuntimeException('Insufficient stock.');
    }

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

Adapt the example to your schema and concurrency requirements. The conditional update helps prevent stock from becoming negative under concurrent requests.

Keep transactions short

Do not make network calls or wait for user input while holding database locks. Prepare data before beginning when possible, perform the database work, then commit quickly. Understand isolation levels and deadlock behavior for high-concurrency workloads.

Idempotency and retries

A client retry after a timeout can submit the same operation twice. Use unique business identifiers or idempotency keys for operations where duplicates are harmful. Retry deadlocks only with bounded logic and a safe transaction boundary.

Official references

PDO transactions MySQL InnoDB transaction model

Keep the transaction boundary small

A transaction should contain the related database statements that must succeed together. Validate input before opening it, keep external network calls outside it and commit promptly to avoid holding locks longer than necessary.

<?php
$pdo->beginTransaction();

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

    $orderId = (int)$pdo->lastInsertId();

    $stock = $pdo->prepare(
        'UPDATE products SET quantity = quantity - :qty
         WHERE id = :id AND quantity >= :qty'
    );
    $stock->execute(['qty' => $quantity, 'id' => $productId]);

    if ($stock->rowCount() !== 1) {
        throw new RuntimeException('Insufficient stock.');
    }

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

Use prepared statements and verify affected-row counts for conditional updates. Configure PDO exception mode so database failures enter the error path.

Understand isolation and concurrency

Transactions do not automatically prevent every race condition. Choose an isolation level appropriate to the workflow and use constraints, conditional updates or row locks when two requests could compete for the same resource. Retry deadlocks only with a bounded policy and an idempotent operation.

Avoid external side effects inside a transaction

Do not send email or call a payment provider while holding database locks. Persist the intent in an outbox or queue, commit, then process the side effect separately. This makes failure recovery more predictable.