Retry transactions that fail on a conflict

How to do it

Under Repeatable Read and Serializable, PostgreSQL cancels a transaction that would break isolation with SQLSTATE 40001. A deadlock gives 40P01 in PostgreSQL and error 1213 in MySQL. The PostgreSQL docs say the retry must rerun the complete transaction, including the code that decided what to write. Retrying only the failed statement can write a result based on stale reads. So wrap the transaction in one function and call it again. Retry nothing else by default. A unique violation may be a lasting error, not a passing one. Make sure the work inside has no side effects outside the database, or send them through Send side effects through an outbox. Count retries in a metric, since a rising rate means two code paths fight over the same rows.

Examples

JavaScript example

async function withSerializableRetry(work, maxAttempts = 3) {
  for (let attempt = 1; ; attempt++) {
    try {
      return await db.tx({ mode: new TransactionMode({ tiLevel: isolationLevel.serializable }) }, work);
    } catch (error) {
      const isRetryable = error.code === '40001' || error.code === '40P01';
      if (!isRetryable || attempt === maxAttempts) throw error;
      await new Promise(resolve => setTimeout(resolve, Math.random() * 50 * attempt));
    }
  }
}

Python example

connection.isolation_level = psycopg.IsolationLevel.SERIALIZABLE

def with_serializable_retry(work, max_attempts=3):
    for attempt in range(1, max_attempts + 1):
        try:
            with connection.transaction():
                return work(connection)
        except (psycopg.errors.SerializationFailure, psycopg.errors.DeadlockDetected):
            if attempt == max_attempts:
                raise
            time.sleep(random.random() * 0.05 * attempt)

How to check

A test runs two serializable transactions that read and write the same row in crossed order. Both finish after at most 3 attempts, and the retry metric rises by at least 1. A test that always fails with 40001 gets an error after exactly 3 attempts.

Sources

• PostgreSQL serialization failure handling
• MySQL deadlocks in InnoDB

Related rules

• DAT-15 Know your isolation level in Data and databases
• EVT-6 Send side effects through an outbox in Integration and events