# Connection Pool()me harder

[TOC]

## Abstract

TCP/IP structure inherently cannot detect network errors without timeout configuration.

Applications can hang indefinitely when network errors occur if socket timeout isn't configured ("dead connections")
Higher-level timeouts depend on lower-level timeouts working properly
Socket.write() can also hang when OS kernel buffers are full during network errors

- **Statement timeout doesn't handle network failures** - it only restricts the operation time of a single statement
- **Transaction timeout formula**: Statement Timeout × N (number of statements) + α (overhead like garbage collection)
- **OS-level socket timeout** on Linux servers (30 minutes for KeepAlive, 15 minutes for write operations) can act as a fallback

### Example

- Oracle and MS SQL Server use polling threads
- MySQL and CUBRID create timeout-execution threads

## Configuration Hiearchy

1. **Timeout hierarchy matters**: Higher-level timeouts only work if lower-level timeouts are properly configured
2. **Network failures require DB driver socket timeout**, not statement timeout
3. **Default socket timeout is usually 0** (infinite wait) for most DB drivers
4. **Each timeout serves a different purpose**:
- Transaction timeout: Limits total transaction time
- Statement timeout: Limits individual query execution
- Socket timeout: Handles network failures

### **Socket Timeout Configuration:**

- **Socket timeout is essential** for handling DBMS termination or network errors
- **Socket timeout must be higher than statement timeout** to maintain meaningful statement timeout behavior

> [!WARNING]
> **Dead connections can persist indefinitely without proper timeout configuration**

- **Must be configured** to prevent infinite waiting during network errors
- **Should be much larger than statement timeout**
- **Not recommended** for limiting statement execution time
- Configure both:
- Connect timeout (Socket.connect)
- Read/Write timeout (Socket.setSoTimeout)

## Timeout Matrix

|**Aspect**|**Mathematical Formula**|**Description**|**Impact**|**Failure Scenario**|
|---|---|---|---|---|
|**Timeout Hierarchy**|$$T_{socket} > T_{statement} > 0$$|Socket timeout must exceed statement timeout for proper operation|Lower timeouts become ineffective if higher ones fail|Statement timeout becomes meaningless if socket timeout is smaller|
|**Transaction Timeout**|$$T_{transaction} = \sum_{i=1}^{n} T_{statement_i} + \alpha$$ <br> where $\alpha$ = overhead (GC, framework ops)|Total time for all statements plus processing overhead|Prevents runaway transactions|Transaction continues indefinitely without proper configuration|
|**Effective Timeout**|$$T_{effective} = \min(T_{socket}, T_{OS}, T_{statement})$$|The actual timeout that will trigger first|Determines actual failure detection time|Unexpected timeout source may trigger prematurely|
|**Network Failure Detection**|$$T_{detection} = \begin{cases} T_{socket} & \text{if configured} \ T_{OS} & \text{if socket not configured} \ \infty & \text{if neither configured} \end{cases}$$|Time to detect network failures|Critical for dead connection prevention|Application hangs indefinitely without proper configuration|
|**Connection Pool Wait**|$$T_{pool_wait} \neq T_{socket}$$|Pool wait timeout is independent of socket timeout|Different timeout contexts|Confusion between pool and socket timeouts|
|**Retry Window**|$$T_{retry} = T_{connect} \times n_{hosts}$$ <br> for failover configurations|Total time for connection attempts across multiple hosts|Affects failover behavior|Extended delays during failover scenarios|

## **Timeout Strategy Formulation**

### **Optimal Timeout Formula**

For a robust timeout strategy, use the following formula:

$$T_{optimal} = \begin{cases} T_{connect} = 10 \text{ seconds} \ T_{socket} = \max(T_{statement} \times 1.5, 300) \text{ seconds} \ T_{statement} = P_{95}(query_time) \times 2 \ T_{transaction} = \sum T_{statement} + 30 \text{ seconds} \end{cases}$$

Where:

- $P_{95}(query_time)$ = 95th percentile of query execution time
- Factor of 1.5 provides buffer between statement and socket timeout
- 30 seconds accounts for framework overhead

### **Service-Specific Behavioral Patterns**

|**Service Type**|**Timeout Behavior**|**Special Considerations**|**Formula Adjustment**|
|---|---|---|---|
|**OLTP Systems**|Fast queries, high concurrency|Short timeouts acceptable|$$T_{statement} = P_{99}(query_time) \times 1.2$$|
|**OLAP/Data Warehouse**|Long-running queries|Extended timeouts required|$$T_{socket} = \max(3600, T_{statement} \times 2)$$|
|**Microservices**|Network latency critical|Circuit breaker patterns|$$T_{total} = T_{connect} + T_{socket} < SLA_{timeout}$$|
|**Batch Processing**|Variable execution times|Dynamic timeout adjustment|$$T_{batch} = n_{records} \times T_{per_record} \times 1.5$$|
|**Real-time Systems**|Strict latency requirements|Fail-fast approach|$$T_{max} = \min(T_{SLA}, T_{socket})$$|

- **Statement timeout doesn't handle network failures** - it only restricts the operation time of a single statement
- **Transaction timeout formula**: Statement Timeout × N (number of statements) + α (overhead like garbage collection)

## State Machine

Connection Pool State Machine

stateDiagram-v2
    [*] --> Idle: Pool created
    
    state Idle {
        [*] --> Available: Connections available
        [*] --> Empty: No connections
    }
    
    Idle --> Acquiring: Request for connection
    
    state Acquiring {
        Available --> Reuse: Get existing connection
        Empty --> Create: Create new connection
        
        Reuse --> HealthCheck: Check connection health
        HealthCheck --> Healthy: Connection OK
        HealthCheck --> Unhealthy: Connection bad
        Unhealthy --> Create
        
        Create --> Connecting: Establish connection
        Connecting --> Connected: Success
        Connecting --> Failed: Connection error
        Failed --> Retry: Retry with backoff
        Retry --> Connecting
        Retry --> Exhausted: Max retries reached
    }
    
    Connected --> Active: Connection in use
    Healthy --> Active
    
    state Active {
        [*] --> Processing: Request/Response
        Processing --> Complete: Success
        Processing --> Error: Failure
    }
    
    Complete --> Returning: Return to pool
    Error --> Discarding: Remove from pool
    
    state Returning {
        [*] --> CheckIdle: Check idle timeout
        CheckIdle --> ReturnToPool: Within timeout
        CheckIdle --> Close: Timeout exceeded
        ReturnToPool --> Available
    }
    
    Discarding --> Empty
    Close --> Empty
    Exhausted --> ErrorState: Connection pool error
    
    ErrorState --> [*]: Request failed