Project 3: Database Connection Pool

Build a connection pool that enforces lifecycle states and safe resource reuse.

Quick Reference

Attribute Value
Difficulty Advanced
Time Estimate 1-2 weeks
Language C
Prerequisites Threads/locks basics, sockets or DB client API
Key Topics Resource lifecycle, state transitions, contention

1. Learning Objectives

By completing this project, you will:

  1. Model connection lifecycle states explicitly.
  2. Implement acquisition and release with timeouts.
  3. Prevent invalid state transitions (double release, use-after-close).
  4. Handle failures while keeping pool consistent.

2. Theoretical Foundation

2.1 Core Concepts

  • Resource lifecycle: Idle -> InUse -> Closing -> Closed.
  • Pool invariants: Total connections <= max, no duplicates, no leaks.
  • Contention: Multiple threads compete for limited resources.

2.2 Why This Matters

Connection pools are a classic example of state-driven resource management. Bugs cause leaks, deadlocks, and outages.

2.3 Historical Context / Background

Early database systems embedded pooling in middleware; now most high-performance services depend on well-behaved pools to stay within limits.

2.4 Common Misconceptions

  • “A pool is just a queue”: Lifecycle and failure handling make it a state machine.
  • “Closed means gone”: The pool must recycle or destroy safely.

3. Project Specification

3.1 What You Will Build

A thread-safe connection pool with fixed maximum size, timeout-based acquisition, and safe release/cleanup semantics.

3.2 Functional Requirements

  1. Acquire: Get a connection or wait with timeout.
  2. Release: Return a connection to the pool.
  3. Health checks: Validate idle connections before reuse.
  4. Shutdown: Cleanly close all connections.

3.3 Non-Functional Requirements

  • Reliability: No double-free or leaked connections.
  • Concurrency: Correct under multiple threads.
  • Latency: Acquire should be bounded by timeout.

3.4 Example Usage / Output

pool_init(max=10)
conn = pool_acquire(timeout=1000ms)
pool_release(conn)

3.5 Real World Outcome

Threads can safely share a pool without leaking or over-allocating connections:

[pool] acquire -> conn #3
[pool] release -> conn #3
[pool] shutdown -> closed 10 connections

4. Solution Architecture

4.1 High-Level Design

┌──────────────┐     ┌──────────────┐     ┌──────────────┐
│ threads      │────▶│ pool manager │────▶│ connections  │
└──────────────┘     └──────────────┘     └──────────────┘

4.2 Key Components

Component Responsibility Key Decisions
Pool struct Track connections and state Fixed array + mutex
Connection state Idle/InUse/Closed Enum with invariants
Wait queue Block on acquire Condition variable

4.3 Data Structures

typedef enum {
    CONN_IDLE,
    CONN_IN_USE,
    CONN_CLOSING,
    CONN_CLOSED
} ConnState;

typedef struct {
    ConnState state;
    int fd; /* or DB handle */
} PoolConn;

4.4 Algorithm Overview

Key Algorithm: Acquire/Release

  1. Lock pool.
  2. Find idle connection or create new.
  3. If none, wait with timeout.
  4. Mark IN_USE and return.

Complexity Analysis:

  • Time: O(N) for scanning pool.
  • Space: O(N) for connections.

5. Implementation Guide

5.1 Development Environment Setup

gcc --version

5.2 Project Structure

project-root/
├── src/
│   ├── pool.c
│   ├── pool.h
│   └── main.c
└── Makefile

5.3 The Core Question You’re Answering

“What state is each connection in, and what operations are valid right now?”

5.4 Concepts You Must Understand First

Stop and research these before coding:

  1. Mutex + condition variables
    • Correct wait/notify usage
  2. Resource lifecycle
    • Acquire/use/release patterns
  3. Timeout handling
    • Avoid blocking forever

5.5 Questions to Guide Your Design

  1. How will you prevent two threads from using the same connection?
  2. What happens if a connection dies while idle?
  3. How do you enforce the max size?

5.6 Thinking Exercise

If a thread forgets to release a connection, how can your pool detect and mitigate it?

5.7 The Interview Questions They’ll Ask

  1. Why is a connection pool a state machine?
  2. How do you handle timeouts in acquisition?
  3. What invariants must always hold in a pool?

5.8 Hints in Layers

Hint 1: Start single-threaded

  • Validate state transitions first.

Hint 2: Add locking

  • Protect pool with mutex.

Hint 3: Add condition variable

  • Wait when no connections free.

5.9 Books That Will Help

Topic Book Chapter
Concurrency “Operating Systems: Three Easy Pieces” Threads
Resource mgmt “APUE” File descriptors and lifecycle

5.10 Implementation Phases

Phase 1: Foundation (3-4 days)

Goals:

  • Implement basic pool with states.

Tasks:

  1. Add enum states.
  2. Acquire/release without threads.

Checkpoint: Correct transitions and invariants.

Phase 2: Core Functionality (4-5 days)

Goals:

  • Add concurrency.

Tasks:

  1. Add mutex/condvar.
  2. Implement timeouts.

Checkpoint: Multiple threads acquire safely.

Phase 3: Polish & Edge Cases (2-3 days)

Goals:

  • Failure handling and cleanup.

Tasks:

  1. Add health checks.
  2. Add shutdown logic.

Checkpoint: Clean shutdown with no leaks.

5.11 Key Implementation Decisions

Decision Options Recommendation Rationale
Pool size fixed vs dynamic fixed Simpler invariants
Idle scan linear vs queue linear first Simplicity

6. Testing Strategy

6.1 Test Categories

Category Purpose Examples
State tests Valid transitions Idle->InUse->Idle
Concurrency No double use Two threads acquire
Timeout Bounded waits Acquire returns timeout

6.2 Critical Test Cases

  1. Acquire/release preserves pool count.
  2. Two threads cannot get same connection.
  3. Timeout returns error after limit.

6.3 Test Data

max=2, threads=4

7. Common Pitfalls & Debugging

7.1 Frequent Mistakes

Pitfall Symptom Solution
Missing lock Double use Lock all state changes
Missed signal Thread hangs Use while loop on condvar
Leak on error Pool grows Ensure cleanup path

7.2 Debugging Strategies

  • Add logging around state changes.
  • Use assertions for invariants.

7.3 Performance Traps

Linear scans under high concurrency can be slow; optimize later.

8. Extensions & Challenges

8.1 Beginner Extensions

  • Add metrics for pool usage.

8.2 Intermediate Extensions

  • Add per-connection timeouts and recycling.

8.3 Advanced Extensions

  • Implement dynamic resize with safe transitions.

9. Real-World Connections

9.1 Industry Applications

  • Databases: Connection pools are core infrastructure.
  • Network clients: Reuse of sockets and TLS sessions.
  • pgbouncer: PostgreSQL pooling service.
  • HikariCP: JVM pool with strict invariants.

9.3 Interview Relevance

  • Demonstrates state management under concurrency.

10. Resources

10.1 Essential Reading

  • APUE - resource lifecycle patterns.
  • OSTEP - concurrency and locks.

10.2 Video Resources

  • Search: “connection pool design”.

10.3 Tools & Documentation

  • pthread: man pthreads.

11. Self-Assessment Checklist

11.1 Understanding

  • I can explain pool invariants.
  • I can describe valid transitions.

11.2 Implementation

  • Pool works under concurrency.
  • No leaks on shutdown.

11.3 Growth

  • I can apply lifecycle logic to other resources.

12. Submission / Completion Criteria

Minimum Viable Completion:

  • Thread-safe acquire/release with timeouts.

Full Completion:

  • Health checks and clean shutdown.

Excellence (Going Above & Beyond):

  • Dynamic resizing with strong invariants.

This guide was generated from SPRINT_3_CONTROL_FLOW_STATE_PROJECTS.md. For the complete learning path, see the parent directory README.