dml_write_exception SQLSTATE 40001 on deadlock: retrying delegated transactions without duplicate writes

Direct answers

1. Detection: Yes, catching dml_write_exception and checking getSQLState() === '40001' is the correct, portable way to detect a retryable deadlock on both MySQL and PostgreSQL.
2. Idempotency: Guard every INSERT that could run twice with a duplicate‑key catch (SQLSTATE 23505 Postgres, 23000/1062 MySQL) or an existence check. Naturally idempotent updates need no extra code.
3. Scope: Wrap only the delegated transaction block (start_delegated_transaction() … allow_commit()) in the retry loop, not the whole execute() method.

Recommended retry loop

const int MAX_RETRIES = 3;
$attempt = 0;
while (true) {
    try {
        $DB->start_delegated_transaction();
        // … all writes that must be atomic …
        $DB->allow_commit();
        break;
    } catch (dml_write_exception $e) {
        if ($e->getSQLState() === '40001' && ++$attempt < MAX_RETRIES) {
            // deadlock – loop retries the whole transaction
            continue;
        }
        throw; // non‑retryable or retries exhausted
    }
}

Making inserts idempotent

Each logical insert needs one of two guards:

• Duplicate‑key catch (preferred under concurrency):

  try {
      $DB->insert_record('mytable', $record);
  } catch (dml_exception $e) {
      if (in_array($e->getSQLState(), ['23505', '23000'], true) || $e->getCode() === 1062) {
          // row already exists – treat as success
      } else {
          throw;
      }
  }

• Existence check (lower contention, but race window):

  if (!$DB->get_record('mytable', ['unique_col' => $value])) {
      $DB->insert_record('mytable', $record);
  }

Requirement: the table must have a unique key (primary or unique index) on the columns that define logical uniqueness; otherwise the duplicate‑key exception never fires.

Why the loop belongs around the transaction only

Code before start_delegated_transaction() (logging, cache warm-up, etc.) has already executed and must not run again. Code after allow_commit() runs only on success. Re‑executing the whole execute() would repeat those side effects.

One missing detail

Do any of your inserts target tables that lack a unique constraint on the columns you consider logically unique? If yes, the duplicate‑key guard cannot work and you must add a unique index or redesign the key.