Circuit Breaker for Database Connections — Protecting Against Database Outages

In this tutorial, you will learn about Circuit Breaker for Database Connections. We cover key concepts, practical examples, and best practices to help you master this topic.

Database circuit breaker patterns protect applications from database outages by detecting connection failures, query timeouts, and replica lag, then redirecting traffic to read replicas, serving cached data, or providing degraded functionality until the database recovers.

flowchart TD
    App[Application] --> CB[Database Circuit Breaker]
    CB -->|Primary| Primary[(Primary DB)]
    CB -->|Read Replica| Replica[(Read Replica)]
    CB -->|Cache| Cache[(Redis Cache)]
    CB -->|Degraded| Degraded[Return Cached Data]
    Primary -->|Failure| Open[Open Circuit]
    Open -->|Switch| Replica
    Replica -->|Also Fails| CacheMode

What You'll Learn

• Database connection failure detection
• Read replica fallback strategy
• Query timeout circuit breaking
• Connection Pool protection
• Graceful degradation patterns

Why It Matters

Database outages cascade to all application functionality. Without database circuit breakers, a database failure causes connection pool exhaustion, thread starvation, and complete application unavailability within seconds.

Real-World Use

DodaTech's application uses database circuit breakers per query type. When the primary database experiences connection timeouts, write operations circuit-break and the app switches to read-only mode with cached data. The database team receives alerts and the application remains partially functional.

Connection Pool Protection

import time
import threading
from dbutils.pooled_db import PooledDB
import pymysql

class DatabaseCircuitBreaker:
    def __init__(self, pool, fail_threshold=5, recovery_timeout=30):
        self.pool = pool
        self.fail_threshold = fail_threshold
        self.recovery_timeout = recovery_timeout
        self.failures = 0
        self.state = 'CLOSED'
        self.last_failure = 0
        self.lock = threading.Lock()

    def execute(self, query, params=None, fallback=None):
        with self.lock:
            if self.state == 'OPEN':
                if time.time() - self.last_failure > self.recovery_timeout:
                    self.state = 'HALF_OPEN'
                    print("[DB] Half-open probe")
                else:
                    return self._use_fallback(query, fallback)

        try:
            conn = self.pool.connection()
            cursor = conn.cursor()
            cursor.execute(query, params or ())
            result = cursor.fetchall()
            cursor.close()
            conn.close()
            self._record_success()
            return result
        except Exception as e:
            self._record_failure()
            return self._use_fallback(query, fallback)

    def _record_success(self):
        with self.lock:
            self.failures = 0
            if self.state == 'HALF_OPEN':
                self.state = 'CLOSED'
                print("[DB] Recovered, circuit closed")

    def _record_failure(self):
        with self.lock:
            self.failures += 1
            self.last_failure = time.time()
            if self.failures >= self.fail_threshold:
                self.state = 'OPEN'
                print(f"[DB] Circuit open ({self.failures} failures)")

    def _use_fallback(self, query, fallback):
        if fallback:
            return fallback(query)
        raise Exception("Database unavailable")

pool = PooledDB(pymysql, maxconnections=5, host='localhost',
                user='user', password='pass', database='app')

db_cb = DatabaseCircuitBreaker(pool, fail_threshold=3, recovery_timeout=10)

for i in range(6):
    try:
        result = db_cb.execute("SELECT 1", fallback=lambda q: [("cached",)])
        print(f"Query {i+1}: {result}")
    except Exception as e:
        print(f"Query {i+1}: {e}")
    time.sleep(0.1)

Expected output:

Query 1: [(1,)]
Query 2: [(1,)]
[DB] Circuit open (3 failures)
Query 3: [('cached',)]
Query 4: [('cached',)]

Read Replica Fallback

import time
import random

class DatabaseWithReplicaFallback:
    def __init__(self, primary, replica, fail_threshold=3):
        self.primary = primary
        self.replica = replica
        self.fail_threshold = fail_threshold
        self.failures = 0
        self.state = 'CLOSED'
        self.mode = 'primary'

    def execute_read(self, query):
        if self.state == 'OPEN':
            return self._query_replica(query)
        if self.state == 'HALF_OPEN':
            result = self._query_primary(query)
            if result:
                self.state = 'CLOSED'
                self.mode = 'primary'
                return result
            return self._query_replica(query)

        try:
            return self._query_primary(query)
        except Exception:
            self.failures += 1
            if self.failures >= self.fail_threshold:
                self.state = 'OPEN'
                self.mode = 'read_replica'
                print("[DB] Switching to read replica")
            return self._query_replica(query)

    def _query_primary(self, query):
        if random.random() < 0.4:
            raise ConnectionError("Primary unavailable")
        return f"[Primary] Result: {query}"

    def _query_replica(self, query):
        if random.random() < 0.1:
            raise ConnectionError("Replica unavailable")
        return f"[Replica] Result: {query}"

db = DatabaseWithReplicaFallback(primary=True, replica=True)

for i in range(6):
    result = db.execute_read("SELECT * FROM products")
    print(f"Query {i+1}: {result}")
    time.sleep(0.1)

Expected output:

Query 1: [Primary] Result: SELECT * FROM products
Query 2: [Primary] Result: SELECT * FROM products
[DB] Switching to read replica
Query 3: [Replica] Result: SELECT * FROM products
Query 4: [Replica] Result: SELECT * FROM products
Query 5: [Replica] Result: SELECT * FROM products
Query 6: [Replica] Result: SELECT * FROM products

Common Mistakes

• Circuit breaker per connection instead of per database -- creating a circuit breaker for each connection leads to inconsistent state. Use one circuit breaker per database endpoint (primary, replica, each shard).
• No distinction between read and write operations -- reads can safely fallback to replicas. Writes must fail when the primary is down. Create separate circuit breakers for read and write operations.
• Connection pool exhaustion before circuit opens -- connection pools queue requests when all connections are busy. By the time the circuit breaker detects failures, the pool is exhausted. Set pool timeouts shorter than circuit breaker thresholds.
• Half-open probe causing replica overload -- half-open probes should use a lightweight query (SELECT 1) that does not load the database. Avoid complex queries for recovery probes.
• Not monitoring Replication lag -- read replicas may have significant lag. If the replica serves stale data during primary failure, users see inconsistent state. Monitor replica lag and include it in the circuit breaker decision.

Practice Questions

1. How does a database circuit breaker differ from a service circuit breaker?
2. Why should read and write operations have separate circuit breakers?
3. How do read replicas serve as fallback during primary database failure?
4. What query should half-open probes use?
5. How does replication lag affect read replica fallback?

Challenge

Build a database resilience layer: (1) circuit breakers per database endpoint (primary-write, primary-read, replica-1, replica-2), (2) automatic failover: primary writes fail -> reject writes, primary reads fail -> route to replica, all replicas fail -> serve from cache, (3) circuit breaker opens after 5 consecutive query failures (timeout or connection error), (4) half-open probes use SELECT 1 with 500ms timeout, (5) replica lag monitoring: if replica lag > 5 seconds, route to primary even when primary is slow, (6) connection pool protection: reduce pool size when circuit is open, (7) Prometheus metrics for database state, query latency per endpoint, and fallback activation rate.

FAQ

Mini Project

Build a complete database resilience system: (1) circuit breaker for primary database (fail after 5 connection failures), (2) read replica circuit breaker with replica lag detection (> 2s lag = treat as failure), (3) automatic failover from primary to replica for reads, (4) cache fallback (Redis) when all database endpoints are down, (5) write queue that stores write operations during primary outage and replays on recovery, (6) connection pool management: reduce max_connections by 50% when circuit is open, (7) health check endpoint showing database circuit breaker states and current routing mode, (8) Prometheus metrics for all database operations.

What's Next

Continue with API Gateway to learn gateway-level circuit breaking patterns. Then explore gRPC Integration for gRPC circuit breaker patterns.