Handling Deadlocks and Transaction Retries

Retry a complete failed transaction when the database reports a recognized deadlock or serialization conflict.

The examples focus on the operation itself, including the important distinction: A connection retry fixes transport setup; a transaction retry re-executes all statements.

What It Does

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

Before You Start

Read the input requirements before applying this pattern to production data.

Example 1: Basic Use

For MySQL, error 1213 signals a deadlock; replay the whole transaction, with a bounded retry count.

<?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]
);
for ($attempt = 1; $attempt <= 3; $attempt++) {
    try {
        $pdo->beginTransaction();
        $pdo->prepare("UPDATE users SET active = 1 WHERE id = ?")->execute([3]);
        $pdo->commit();
        break;
    } catch (PDOException $e) {
        if ($pdo->inTransaction()) { $pdo->rollBack(); }
        $deadlock = ($e->errorInfo[1] ?? null) === 1213;
        if (!$deadlock || $attempt === 3) { throw $e; }
        usleep(random_int(20000, 60000) * $attempt);
    }
}

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

Example 2: Checking Behavior

For MySQL, error 1213 signals a deadlock; replay the whole transaction, with a bounded retry count.

<?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]
);
for ($attempt = 1; $attempt <= 3; $attempt++) {
    try {
        $pdo->beginTransaction();
        $pdo->prepare("UPDATE users SET active = 1 WHERE id = ?")->execute([8]);
        $pdo->commit();
        break;
    } catch (PDOException $e) {
        if ($pdo->inTransaction()) { $pdo->rollBack(); }
        $deadlock = ($e->errorInfo[1] ?? null) === 1213;
        if (!$deadlock || $attempt === 3) { throw $e; }
        usleep(random_int(20000, 60000) * $attempt);
    }
}

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

Example 3: Practical Example

For MySQL, error 1213 signals a deadlock; replay the whole transaction, with a bounded retry count.

<?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]
);
for ($attempt = 1; $attempt <= 3; $attempt++) {
    try {
        $pdo->beginTransaction();
        $pdo->prepare("UPDATE users SET active = 1 WHERE id = ?")->execute([21]);
        $pdo->commit();
        break;
    } catch (PDOException $e) {
        if ($pdo->inTransaction()) { $pdo->rollBack(); }
        $deadlock = ($e->errorInfo[1] ?? null) === 1213;
        if (!$deadlock || $attempt === 3) { throw $e; }
        usleep(random_int(20000, 60000) * $attempt);
    }
}

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

Roll back first, limit retry attempts, and avoid non-idempotent external side effects inside the retried block.

Comparison with Related Approaches

Common Beginner Mistakes

Key Takeaways

• Retry a complete failed transaction when the database reports a recognized deadlock or serialization conflict.
• Roll back first, limit retry attempts, and avoid non-idempotent external side effects inside the retried block.
• A connection retry fixes transport setup; a transaction retry re-executes all statements.

Use Handling Deadlocks and Transaction Retries when its documented behavior matches the problem. Test the relevant edge cases in your own runtime before deploying it.