Implementing Database Retry Logic for Transient Failures

Retry only recognized transient errors such as deadlocks and brief connection disruptions.

The examples focus on the operation itself, including the important distinction: Syntax and constraint violations are permanent errors and should not be retried.

What It Does

Use the examples below to observe the behavior on a small dataset before extending it to application code.

Before You Start

The examples use PHP 8.2+ features where shown. Database snippets require the corresponding PDO driver, demo tables, and credentials configured outside source code.

Example 1: Basic Use

Retry only recognizable transient SQLSTATE codes, with bounded backoff; do not blindly replay non-idempotent writes.

<?php
$pdo = new PDO(
    "mysql:host=" . (getenv("DB_HOST") ?: "127.0.0.1") . ";dbname=" . (getenv("DB_NAME") ?: "demo") . ";charset=utf8mb4",
    getenv("DB_USER") ?: "app",
    getenv("DB_PASSWORD") ?: "",
    [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, PDO::ATTR_EMULATE_PREPARES => false]
);
$maxAttempts = 3;
for ($attempt = 1; $attempt <= $maxAttempts; $attempt++) {
    try {
        $stmt = $pdo->prepare("SELECT email FROM users WHERE id = 3");
        $stmt->execute();
        echo $stmt->fetchColumn() ?: "not found";
        break;
    } catch (PDOException $e) {
        $transient = in_array($e->getCode(), ["40001", "08S01"], true);
        if (!$transient || $attempt === $maxAttempts) { throw $e; }
        usleep(50000 * (2 ** ($attempt - 1)));
    }
}

This example is meant for a configured application environment; output depends on the installed extension, external service, or database contents.

Example 2: Checking Behavior

Retry only recognizable transient SQLSTATE codes, with bounded backoff; do not blindly replay non-idempotent writes.

<?php
$pdo = new PDO(
    "mysql:host=" . (getenv("DB_HOST") ?: "127.0.0.1") . ";dbname=" . (getenv("DB_NAME") ?: "demo") . ";charset=utf8mb4",
    getenv("DB_USER") ?: "app",
    getenv("DB_PASSWORD") ?: "",
    [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, PDO::ATTR_EMULATE_PREPARES => false]
);
$maxAttempts = 3;
for ($attempt = 1; $attempt <= $maxAttempts; $attempt++) {
    try {
        $stmt = $pdo->prepare("SELECT email FROM users WHERE id = 8");
        $stmt->execute();
        echo $stmt->fetchColumn() ?: "not found";
        break;
    } catch (PDOException $e) {
        $transient = in_array($e->getCode(), ["40001", "08S01"], true);
        if (!$transient || $attempt === $maxAttempts) { throw $e; }
        usleep(50000 * (2 ** ($attempt - 1)));
    }
}

This example is meant for a configured application environment; output depends on the installed extension, external service, or database contents.

Example 3: Practical Example

Retry only recognizable transient SQLSTATE codes, with bounded backoff; do not blindly replay non-idempotent writes.

<?php
$pdo = new PDO(
    "mysql:host=" . (getenv("DB_HOST") ?: "127.0.0.1") . ";dbname=" . (getenv("DB_NAME") ?: "demo") . ";charset=utf8mb4",
    getenv("DB_USER") ?: "app",
    getenv("DB_PASSWORD") ?: "",
    [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, PDO::ATTR_EMULATE_PREPARES => false]
);
$maxAttempts = 3;
for ($attempt = 1; $attempt <= $maxAttempts; $attempt++) {
    try {
        $stmt = $pdo->prepare("SELECT email FROM users WHERE id = 21");
        $stmt->execute();
        echo $stmt->fetchColumn() ?: "not found";
        break;
    } catch (PDOException $e) {
        $transient = in_array($e->getCode(), ["40001", "08S01"], true);
        if (!$transient || $attempt === $maxAttempts) { throw $e; }
        usleep(50000 * (2 ** ($attempt - 1)));
    }
}

This example is meant for a configured application environment; output depends on the installed extension, external service, or database contents.

Important Behavior and Edge Cases

Avoid retrying non-idempotent operations without transaction/uniqueness safeguards; use bounded exponential backoff.

Comparison with Related Approaches

Common Beginner Mistakes

Key Takeaways

• Retry only recognized transient errors such as deadlocks and brief connection disruptions.
• Avoid retrying non-idempotent operations without transaction/uniqueness safeguards; use bounded exponential backoff.
• Syntax and constraint violations are permanent errors and should not be retried.

Use Implementing Database Retry Logic for Transient Failures when its documented behavior matches the problem. Test the relevant edge cases in your own runtime before deploying it.