Web Development

MariaDB Deadlocks in Small PHP Applications - Retry the Transaction, Not the Last Query

Written by Adam Muiz 16 Oct 2026 Updated: 16 Oct 2026 3 min read

Two database transactions can each be valid on their own and still become unable to continue when they run together. One holds a lock the other needs; the second holds a lock the first needs. MariaDB then returns a message that sounds alarming: Deadlock found when trying to get lock; try restarting transaction.

What exactly should restart? Retrying only the last query can detach it from the checks and writes that came before it. Retrying forever can hide a design problem. Ignoring the error abandons otherwise valid work. The useful response is narrower: understand the lock cycle, let InnoDB end one attempt, and retry the complete database transaction only under controlled conditions.

This article focuses on InnoDB through PHP PDO. It is not a promise that deadlocks can be eliminated, and it does not prescribe one retry policy for every application.

A deadlock is a cycle, not merely a slow query

Imagine two transfers that touch account rows 1 and 2. Transaction A updates row 1 first and then requests row 2. At nearly the same time, transaction B updates row 2 first and then requests row 1.

A holds the lock for row 1 and waits for row 2.
B holds the lock for row 2 and waits for row 1.
Neither can release its first lock without finishing or rolling back.

That cycle is the deadlock. The InnoDB documentation explains that the same pattern can involve multiple tables, rows, index records, or index gaps. Application code may speak in terms of logical rows, while InnoDB applies row-level locking through index records. This is one reason a real deadlock can be less obvious than the two-row sketch.

An ordinary lock wait is different. If A holds a lock and B waits for it, A may commit and allow B to continue. In a deadlock cycle, waiting alone cannot make progress. MariaDB documents deadlock error 1213 with SQLSTATE 40001; a lock-wait timeout is the separate error 1205 with SQLSTATE HY000. They should not be collapsed into one catch-all retry rule.

InnoDB breaks the cycle by choosing a victim

With innodb_deadlock_detect enabled, as it is by default, InnoDB detects a cycle and rolls back one participating transaction so the other can proceed. The failed transaction is often called the victim. Which participant loses is an engine decision, not an application contract, so code must not rely on a particular request winning.

This rollback is recovery, not evidence that InnoDB has corrupted the data. The failed attempt's database changes do not remain half-applied. The application does, however, have an unfinished business operation. MariaDB's error 1213 reference explicitly says to try restarting the transaction.

The word transaction is essential. Suppose the failed attempt read a balance, updated the first account, wrote a ledger row, and then deadlocked on the second account. Reissuing only the final UPDATE would skip the earlier read and writes that were rolled back. A fresh attempt must repeat the complete unit of database work against the state that exists now.

A bounded retry pattern in PDO

The following helper is an illustrative boundary for a small application. It checks both the SQLSTATE exposed by PDOException::getCode() and MariaDB's driver error number in errorInfo. It retries error 1213 only, limits attempts, and starts a new transaction each time.

<?php
declare(strict_types=1);

function runWithDeadlockRetry(
    PDO $pdo,
    callable $work,
    int $maxAttempts = 3
): mixed {
    if ($maxAttempts < 1) {
        throw new InvalidArgumentException('maxAttempts must be at least 1');
    }

    for ($attempt = 1; $attempt <= $maxAttempts; $attempt++) {
        try {
            $pdo->beginTransaction();
            $result = $work($pdo);
            $pdo->commit();

            return $result;
        } catch (PDOException $error) {
            if ($pdo->inTransaction()) {
                $pdo->rollBack();
            }

            $driverCode = (int) ($error->errorInfo[1] ?? 0);
            $isMariaDbDeadlock = $error->getCode() === '40001'
                && $driverCode === 1213;

            if (!$isMariaDbDeadlock || $attempt === $maxAttempts) {
                throw $error;
            }

            $delayMicroseconds = random_int(20_000, 80_000) * $attempt;
            usleep($delayMicroseconds);
        } catch (Throwable $error) {
            if ($pdo->inTransaction()) {
                $pdo->rollBack();
            }

            throw $error;
        }
    }

    throw new LogicException('Unreachable retry state');
}

The PHP manual documents that a PDOException carries SQLSTATE and driver error information, while PDO's transaction methods define the begin, commit, and rollback boundary. The dual check above is deliberate: SQLSTATE class 40001 can represent transaction rollback conditions beyond this one MariaDB driver error.

Three attempts and the short randomized delay are examples, not measured universal values. A bounded delay can reduce an immediate collision, but it does not repair inconsistent locking order. The final exception remains visible so the request can fail honestly, be logged with safe context, or be placed into a higher-level recovery flow.

The callback must be safe to run again

A database rollback cannot unsend an email, withdraw an HTTP request, delete a message already delivered to a broker, or reverse an arbitrary file write. If $work performs one of those actions before the commit, a retry may perform it twice even though the database ends in a consistent state.

Keep the retryable callback focused on database work whenever possible. A non-critical notification can happen after a successful commit. If delivery must survive a crash between commit and sending, a durable outbox can record the intent in the same database transaction and let a separate worker deliver it. The worker still needs a duplicate-handling policy; a transaction does not turn a network into exactly-once delivery.

Input values also deserve attention. Repeating the same validated request data is different from asking the current time, generating a new identifier, or consuming mutable process state on every attempt. Decide which values belong to the business operation and which should be recalculated inside a fresh transaction.

Reduce the cycle before relying on retries

Acquire locks in a consistent order

If every transfer locks the lower account ID before the higher account ID, the two opposing operations form a queue instead of acquiring the same pair in opposite order. Both the InnoDB handling guide and the PostgreSQL discussion of deadlocks recommend consistent lock acquisition order as a primary structural defense.

This is a reduction, not a proof of impossibility. Different query plans, secondary indexes, foreign-key checks, range locks, and other write paths can introduce locks that a simple row-order rule does not capture. The rule must cover every path participating in the same invariant.

Keep the transaction focused and short

Open the transaction near the database work it protects, then commit promptly. Do not wait for user input, remote APIs, uploads, or unrelated computation while holding locks. A shorter overlap window gives concurrent transactions fewer opportunities to collide, although short transactions can still deadlock.

Use indexes that match the locking statements

A suitable index can let an UPDATE or locking read examine and lock fewer index records. The wrong conclusion is that adding any index cures deadlocks. Review the actual predicates and plans; an index changes the records and ranges the engine visits, and therefore can change locking behavior in ways that need testing.

Read the deadlock report, not just the exception

SHOW ENGINE INNODB STATUS includes a LATEST DETECTED DEADLOCK section after a deadlock has occurred. It can show the transactions, statements, locks held, locks requested, and the transaction rolled back. Read it as a wait cycle:

Which statements participated?
Which indexes and records were involved?
What did each transaction already hold?
What did each transaction request next?
Can both code paths acquire those resources in the same order?

The command shows the latest detected deadlock, not a permanent history of every incident. MariaDB also documents innodb_print_all_deadlocks for writing all deadlock details to the server error log. Enable broader logging deliberately, protect the resulting logs because statements may contain sensitive context, and avoid treating permanent verbose logging as the first fix.

Tempting responses that make the problem worse

Retrying only the failed statement: the rest of the victim transaction was rolled back, so the statement no longer has its original transaction context.
Retrying every PDOException: syntax errors, constraint violations, authentication failures, and many other errors do not become correct with repetition.
Retrying without a limit: a persistent lock-order bug can consume workers and increase contention instead of surfacing a failure.
Assuming one request always wins: victim selection is not a stable business rule.
Disabling deadlock detection casually: MariaDB documents this as a specialized tradeoff for particular high-concurrency workloads; with detection off, the server relies on lock-wait timeout instead. It is not an ordinary application repair.
Hiding frequent deadlocks behind successful retries: retries preserve availability for occasional collisions, but a rising or concentrated pattern still deserves diagnosis.

A practical review sequence

Confirm the error by SQLSTATE and driver code rather than matching message text.
Capture safe request and operation identifiers, but do not log credentials or sensitive bound values.
Inspect the latest deadlock report and draw the lock order for every participant.
Make resource order consistent across code paths where possible.
Move slow or irreversible non-database work outside the open transaction.
Review predicates and indexes that determine how many records or ranges are touched.
Add a bounded whole-transaction retry for the specific retryable condition.
Monitor exhausted retries separately from recovered attempts so retries do not erase the signal.

Conclusion

A deadlock does not mean transactions have failed as a concept. It means concurrent transactions formed a lock cycle, and the database had to end one attempt to preserve progress. The application-side answer is to recognize the exact condition, restart the complete database transaction with a clear limit, and keep irreversible side effects outside that retry boundary.

Retries handle the occasional race. Consistent lock order, narrow transaction boundaries, appropriate indexes, and deadlock reports address the structure that produced it. The honest target is not deadlocks can never happen, but a rare deadlock is recoverable, while a repeated pattern remains visible enough to fix.