Database Retry Patterns — Complete Implementation Guide
In this tutorial, you will learn about Database Retry Patterns. We cover key concepts, practical examples, and best practices to help you master this topic.
Database retry patterns handle transient database errors like deadlocks, connection timeouts, and serialization failures by automatically retrying the failed operation with appropriate backoff.
What You'll Learn
By the end of this tutorial, you will implement database retry logic for PostgreSQL, handle different error codes appropriately, and build resilient database access layers.
Why It Matters
Database transient failures are common in production. Without retry logic, a simple Deadlock causes a 500 error. DodaTech's services retry database operations to maintain availability.
Real-World Use
DodaZIP's conversion queue uses PostgreSQL with retry logic that catches serialization errors and deadlocks, retrying the Transaction up to 3 times before reporting failure.
Database Retry Learning Path
flowchart LR
A[HTTP Retry] --> B[Database Retry]
B --> C[Error Codes]
C --> D[Transaction Retry]
B --> E{You Are Here}
style E fill:#f90,color:#fff
Understanding Retryable Database Errors
Not all database errors should be retried. Understanding PostgreSQL error codes helps make the right decision.
| Error Code | Description | Retryable? |
|---|---|---|
| 40001 | Serialization failure | Yes |
| 40P01 | Deadlock detected | Yes |
| 08003 | Connection does not exist | Yes |
| 08006 | Connection failure | Yes |
| 23505 | Unique violation | No |
| 23503 | Foreign key violation | No |
| 22001 | String too long | No |
Basic Query Retry
Wrap individual database queries in retry logic that catches retryable errors and re-executes the query.
const { Pool } = require("pg");
const pool = new Pool({
connectionString: process.env.DATABASE_URL,
max: 20,
idleTimeoutMillis: 30000,
connectionTimeoutMillis: 5000
});
const RETRYABLE_CODES = new Set(["40001", "40P01", "08003", "08006"]);
async function queryWithRetry(text, params, retries = 3) {
for (let attempt = 0; attempt < retries; attempt++) {
try {
return await pool.query(text, params);
} catch (err) {
if (attempt === retries - 1) throw err;
if (!RETRYABLE_CODES.has(err.code)) {
throw err;
}
const delay = Math.min(100 * Math.pow(2, attempt), 5000);
console.log(`DB retry ${attempt + 1}/${retries} (${err.code}): waiting ${delay}ms`);
await new Promise(r => setTimeout(r, delay));
}
}
}
Full Transaction Retry
For transaction retries, the entire transaction must be retried because partial execution may have modified state.
async function transactionWithRetry(callback, maxRetries = 3) {
for (let attempt = 0; attempt < maxRetries; attempt++) {
const client = await pool.connect();
try {
await client.query("BEGIN");
const result = await callback(client);
await client.query("COMMIT");
return result;
} catch (err) {
await client.query("ROLLBACK").catch(() => {});
if (attempt === maxRetries - 1) throw err;
if (!RETRYABLE_CODES.has(err.code)) {
throw err;
}
const delay = Math.min(100 * Math.pow(2, attempt), 3000);
console.log(`Transaction retry ${attempt + 1} (${err.code}): waiting ${delay}ms`);
await new Promise(r => setTimeout(r, delay));
} finally {
client.release();
}
}
}
// Usage
const result = await transactionWithRetry(async (client) => {
const { rows } = await client.query(
"UPDATE accounts SET balance = balance - $1 WHERE id = $2 RETURNING balance",
[100, 1]
);
await client.query(
"UPDATE accounts SET balance = balance + $1 WHERE id = $2",
[100, 2]
);
return rows[0];
});
Connection Pool Retry
When the database connection fails, the pool should retry with backoff instead of immediately giving up.
const { Pool } = require("pg");
class ResilientPool {
constructor(config) {
this.config = config;
this.pool = null;
this.connecting = false;
this.connect();
}
async query(text, params) {
if (!this.pool) {
await this.waitForConnection();
}
try {
return await this.pool.query(text, params);
} catch (err) {
if (err.code === "08006" || err.code === "08003") {
console.log("Connection lost, reconnecting...");
this.pool = null;
return this.query(text, params);
}
throw err;
}
}
async connect() {
if (this.connecting) return;
this.connecting = true;
let attempt = 0;
while (!this.pool) {
try {
this.pool = new Pool(this.config);
await this.pool.query("SELECT 1");
console.log("Database connected");
this.connecting = false;
} catch (err) {
const delay = Math.min(1000 * Math.pow(2, attempt), 30000);
console.log(`Connection failed (${err.code}), retrying in ${delay}ms`);
await new Promise(r => setTimeout(r, delay));
attempt++;
}
}
}
async waitForConnection() {
while (!this.pool) {
await new Promise(r => setTimeout(r, 100));
}
}
}
Common Mistakes
Retrying non-retryable errors -- Unique constraint violations will never succeed. Check error codes before retrying.
Not rolling back on retry -- When retrying a transaction, the previous attempt must be rolled back first.
Retrying without releasing connections -- Connection leaks happen when clients are not released in error paths.
Setting retry delay too short -- Deadlocks often require 100-500ms to resolve. Immediate retries hit the same deadlock.
Not logging transaction retries -- A high retry rate indicates database contention issues. Monitor and alert.
Practice Questions
Which PostgreSQL error codes should trigger a retry? 40001 (serialization failure), 40P01 (deadlock), 08003/08006 (connection failures).
Why must the entire transaction be retried, not just the failed query? Partial transaction execution may have modified data. Rolling back and retrying the entire transaction ensures consistency.
When should you retry a connection failure vs report it? Retry connection failures with backoff. Report if the database has been down for longer than a configured threshold.
Challenge: Implement a retry wrapper that distinguishes between read and write queries.
function isReadQuery(text) {
const trimmed = text.trim().toUpperCase();
return trimmed.startsWith("SELECT") || trimmed.startsWith("WITH");
}
async function retryQuery(text, params) {
const retries = isReadQuery(text) ? 5 : 3;
// Apply different retry strategies
}
FAQ
Mini Project
Build a resilient database access layer with retry logic, error code filtering, connection pool recovery, and transaction retry.
class ResilientDatabase {
constructor(connectionString) {
this.pool = new Pool({ connectionString });
this.retryableCodes = new Set(["40001", "40P01", "08003", "08006", "08001"]);
}
async query(text, params, options = {}) {
const maxRetries = options.maxRetries || 3;
for (let attempt = 0; attempt < maxRetries; attempt++) {
try {
return await this.pool.query(text, params);
} catch (err) {
if (attempt === maxRetries - 1) throw err;
if (!this.retryableCodes.has(err.code)) throw err;
const delay = Math.min(200 * Math.pow(2, attempt), 5000);
await new Promise(r => setTimeout(r, delay));
}
}
}
async transaction(callback, options = {}) {
const maxRetries = options.maxRetries || 3;
for (let attempt = 0; attempt < maxRetries; attempt++) {
const client = await this.pool.connect();
try {
await client.query("BEGIN");
const result = await callback(client);
await client.query("COMMIT");
return result;
} catch (err) {
await client.query("ROLLBACK").catch(() => {});
if (attempt === maxRetries - 1) throw err;
if (!this.retryableCodes.has(err.code)) throw err;
const delay = Math.min(100 * Math.pow(2, attempt), 3000);
await new Promise(r => setTimeout(r, delay));
} finally {
client.release();
}
}
}
}
What's Next
Now that you understand database retry, explore retrying async message processing. Then learn about defining retry policies for different operations.
Built by the developers of DodaTech
Doda Browser, DodaZIP & Durga Antivirus Pro