¶ (c) Technocore - All Rights Reserved.
NOTICE: All information contained herein is, and remains
the property of TechnoCore.
The intellectual and technical concepts contained
herein are proprietary to TechnoCore and dissemination of this information or reproduction of this material
is strictly forbidden unless prior written permission is obtained
from TechnoCore.
¶ ObjConnectionPool
ObjConnectionPool is a production-ready, thread-safe database connection pooling implementation designed to reduce connection overhead and prevent connection exhaustion in multi-threaded environments within the Axion framework.
¶ Version
Current Version: 8.2
¶ Version History
- 8.2: Added validation, retry logic, multi-driver support, direct connection creation
- 8.1: Added semaphore-based connection creation serialization
- 8.0: Initial implementation with basic pooling
¶ Overview
Connection pooling is essential for applications that need to maintain multiple database connections efficiently. ObjConnectionPool provides a robust solution that manages connection lifecycle, validates connection health, and automatically handles connection recycling.
¶ Key Features
¶ Core Capabilities
- Direct MySQL Connection Creation: Bypasses Objects.Connection wrapper to avoid garbage collection and threading issues under high concurrent load
- Thread-Safe Connection Pooling: Queue-based exclusivity ensures thread-safe operations
- Automatic Connection Validation: Detects and replaces corrupted connections automatically
- Retry Logic: Automatic connection replacement on failure
- Serialized Connection Creation: Prevents race conditions using semaphore
- Multi-Driver Support: Works with both pymysql and mysql-connector-python
- Automatic Connection Recycling: Based on configurable age thresholds
- Connection Overflow Handling: Configurable limits for peak demand
- Comprehensive Statistics: Real-time tracking of pool operations
¶ Supported Drivers
-
pymysql (Recommended)
- Pure Python MySQL driver
- More stable under high concurrency
- Better performance in multi-threaded environments
-
mysql-connector-python
- Oracle's official MySQL driver
- Stricter protocol handling
- Alternative for specific use cases
¶ Architecture
¶ Thread Safety
All operations are thread-safe through:
- Queue-based connection management: Atomic operations for acquire/release
- Locks for tracking: Dictionaries and counters protected by locks
- Semaphore for creation: Serialized connection creation prevents race conditions
¶ Performance Characteristics
- Connection acquisition: O(1) from pool, O(1) + network latency for creation
- Connection release: O(1)
- Validation overhead: ~1-2ms per acquire (SELECT 1 query)
- Memory overhead: ~100 bytes per tracked connection
¶ Usage
¶ Basic Usage with Context Manager
from ObjConnectionPool import ConnectionPool
# Create a connection pool
pool = ConnectionPool(size=10, config_name="primary")
# Use with context manager (recommended)
with pool as conn:
cursor = conn.cursor()
cursor.execute("SELECT * FROM users")
results = cursor.fetchall()
cursor.close()
# Connection automatically returned to pool
¶ Manual Acquire/Release
# Create pool with pymysql driver
pool = ConnectionPool(size=10, driver="pymysql")
# Manually acquire connection
conn = pool.acquire()
try:
cursor = conn.cursor()
cursor.execute("SELECT * FROM users")
results = cursor.fetchall()
cursor.close()
finally:
# Always release in finally block
pool.release(conn)
¶ Using Alternative Driver
# Use mysql-connector driver
pool = ConnectionPool(size=10, driver="mysql-connector")
with pool as conn:
cursor = conn.cursor()
cursor.execute("SELECT COUNT(*) FROM products")
count = cursor.fetchone()[0]
cursor.close()
¶ Global Pool Pattern
from ObjConnectionPool import get_global_pool
# Create/get global pool instance
pool = get_global_pool(size=20, driver="pymysql")
# Subsequent calls return the same pool
same_pool = get_global_pool() # Returns existing pool
¶ Production Configuration Example
# Production-ready configuration
pool = ConnectionPool(
size=20, # Base pool size
config_name="primary", # Database config name
timeout=30.0, # Acquisition timeout
max_overflow=10, # Allow 10 extra connections
recycle=1800, # Recycle connections after 30 minutes
driver="pymysql" # Use pymysql driver
)
¶ Configuration Recommendations
¶ Pool Sizing
| Application Size | Base Size | Max Overflow |
|---|---|---|
| Small | 5-10 | 5 |
| Medium | 10-20 | 10 |
| Large | 20-50 | 20 |
¶ Worker Limits
- Keep concurrent workers reasonable (<15-20 per pool)
- For high concurrency, use multiple pools or increase pool size
- Monitor pool statistics to identify exhaustion
¶ Connection Recycling
| Traffic Pattern | Recycle Time |
|---|---|
| High-traffic | 1800s (30 min) |
| Default | 3600s (1 hour) |
| Low-traffic | 7200s (2 hours) |
¶ Driver Selection
Use pymysql when:
- You need maximum stability under high concurrency
- Working in multi-threaded environments
- Pure Python implementation is preferred
Use mysql-connector when:
- You need Oracle's official driver
- Stricter protocol handling is required
- Specific compatibility requirements exist
¶ API Reference
¶ Class: ConnectionPool
Thread-safe connection pool for database connections.
¶ Constructor
ConnectionPool(
size: int = 10,
config_name: str = "primary",
timeout: float = 30.0,
max_overflow: int = 5,
recycle: int = 3600,
driver: str = "pymysql"
)
Parameters:
size: Number of connections to maintain in the poolconfig_name: Database configuration name from config.yamltimeout: Max seconds to wait for connection (default: 30)max_overflow: Max connections beyond pool size (default: 5)recycle: Seconds before recycling connection (default: 3600)driver: MySQL driver to use ("pymysql" or "mysql-connector")
¶ Methods
¶ acquire(timeout=None, max_retries=3)
Acquire a connection from the pool with validation and retry logic.
Parameters:
timeout: Max seconds to wait (None = use pool default)max_retries: Maximum number of retries for corrupted connections
Returns: Database connection object
Raises: Empty if no connection available within timeout
¶ release(conn)
Return a connection to the pool.
Parameters:
conn: Connection to return
If the pool is full, overflow connections are closed instead of being returned.
¶ close_all()
Close all connections in the pool.
Warning: This will close connections even if in use!
¶ get_stats()
Get pool statistics.
Returns: Dictionary with pool statistics:
created: Number of connections createdrecycled: Number of connections recycledacquired: Number of connection acquisitionsreleased: Number of connection releasestimeouts: Number of acquisition timeoutstotal_connections: Current total connections (in pool + in use)pool_size: Current number of connections in poolmax_size: Maximum pool size (size + max_overflow)
¶ Function: get_global_pool
get_global_pool(
size: int = 10,
config_name: str = "primary",
timeout: float = 30.0,
max_overflow: int = 5,
recycle: int = 3600,
driver: str = "pymysql"
) -> ConnectionPool
Get or create the global connection pool.
Note: Parameters are only used when creating a new pool. Subsequent calls return the existing pool regardless of parameters provided.
¶ Troubleshooting
¶ Connection Pool Exhausted
Symptom: RuntimeError: Connection pool exhausted
Solutions:
- Increase pool size or max_overflow
- Check for connection leaks (acquire without release)
- Verify connections are being released in finally blocks
- Review worker/thread count
Example:
# Increase pool capacity
pool = ConnectionPool(size=20, max_overflow=15)
¶ Connection Timeout
Symptom: Empty: Connection pool timeout after Xs
Solutions:
- Increase timeout parameter
- Reduce concurrent workers
- Check database server capacity
- Monitor pool statistics
Example:
# Increase timeout
pool = ConnectionPool(size=10, timeout=60.0)
¶ Connection Validation Failed
Symptom: Intermittent validation failures
Causes:
- Database server may be restarting
- Network issues between app and database
- Connection idle timeout on server
Behavior:
The pool will automatically retry and replace bad connections. This is normal behavior for long-running applications.
¶ Memory Leaks
Prevention:
Always release connections using try/finally or context managers:
# Good - using context manager
with pool as conn:
# Use connection
pass
# Good - using try/finally
conn = pool.acquire()
try:
# Use connection
pass
finally:
pool.release(conn)
# Bad - connection may leak on exception
conn = pool.acquire()
# Use connection
pool.release(conn) # May not execute if exception occurs
¶ Integration with ObjData
The connection pool is integrated with ObjData to provide efficient connection management for remote database operations. ObjData uses thread-local storage to cache remote connections, which are backed by the connection pool.
¶ Example: ObjData with Connection Pool
from ObjData import ObjData
# ObjData automatically uses connection pool for remote connections
data = ObjData()
# Remote connection is pooled
remote_conn = data.db_connect_remote("secondary")
# Use connection
cursor = remote_conn.cursor()
cursor.execute("SELECT * FROM remote_table")
results = cursor.fetchall()
cursor.close()
¶ Testing
The test cases for this object are located in resource.test/pytests/factory.core/test_ObjConnectionPool.py.
¶ Test Suite Overview
The comprehensive test suite validates all aspects of the connection pool implementation across 22 test cases organized into 8 test classes:
¶ Test Results Summary (2025-12-23)
All 22 tests PASSED ✅
¶ TestConnectionPoolBasics (2 tests)
- ✅
test_release_connection- Validates connection release back to pool - ✅
test_acquire_release_multiple- Tests multiple acquire/release cycles
¶ TestConnectionPoolLimits (3 tests)
- ✅
test_pool_size_limit- Verifies pool respects size limit - ✅
test_overflow_connections- Tests overflow connection creation - ✅
test_overflow_limit_reached- Validates exception when overflow limit reached
¶ TestConnectionPoolTimeout (1 test)
- ✅
test_acquire_succeeds_after_release- Tests timeout and retry behavior
¶ TestConnectionRecycling (2 tests)
- ✅
test_connection_recycling_disabled- Validates recycling disabled when recycle=0 - ✅
test_young_connection_not_recycled- Ensures young connections aren't recycled
¶ TestThreadSafety (4 tests)
- ✅
test_concurrent_acquire_release- Validates thread safety with 10 concurrent workers - ✅
test_concurrent_context_manager- Tests concurrent context manager usage - ✅
test_no_connection_leaks- Verifies no connection leaks with 10 threads - ✅
test_threadpool_executor- Tests pool with ThreadPoolExecutor and 100 tasks
¶ TestStatistics (4 tests)
- ✅
test_stats_creation_tracking- Validates connection creation tracking - ✅
test_stats_acquire_release_tracking- Tests acquire/release statistics - ✅
test_stats_timeout_tracking- Verifies timeout tracking - ✅
test_stats_pool_size- Validates pool size statistics
¶ TestErrorHandling (3 tests)
- ✅
test_release_none_connection- Tests graceful handling of None release - ✅
test_close_all_connections- Validates closing all connections - ✅
test_acquire_after_close_all- Tests pool recovery after close_all
¶ TestGlobalPool (2 tests)
- ✅
test_get_global_pool_creates_once- Validates singleton pattern - ✅
test_get_global_pool_thread_safe- Tests thread-safe global pool creation
¶ TestStressTesting (1 test)
- ✅
test_rapid_acquire_release- Stress test with rapid acquire/release cycles
¶ Test Coverage
The test suite covers:
- Basic pool operations (acquire/release)
- Thread safety with concurrent access (up to 20 concurrent workers)
- Pool size limits and overflow handling
- Timeout behavior and retry logic
- Connection recycling based on age
- Statistics tracking and reporting
- Context manager integration
- Error conditions and edge cases
- Stress testing under high load
- Global pool singleton pattern
¶ Running Tests
# Run all tests
dev-env/bin/pytest resource.test/pytests/factory.core/test_ObjConnectionPool.py -v
# Run specific test class
dev-env/bin/pytest resource.test/pytests/factory.core/test_ObjConnectionPool.py::TestThreadSafety -v
# Run specific test
dev-env/bin/pytest resource.test/pytests/factory.core/test_ObjConnectionPool.py::TestThreadSafety::test_concurrent_acquire_release -v
¶ Best Practices
¶ 1. Always Use Context Managers or Try/Finally
# Best practice - context manager
with pool as conn:
cursor = conn.cursor()
cursor.execute("SELECT * FROM users")
cursor.close()
# Also good - try/finally
conn = pool.acquire()
try:
cursor = conn.cursor()
cursor.execute("SELECT * FROM users")
cursor.close()
finally:
pool.release(conn)
¶ 2. Monitor Pool Statistics
# Periodically check pool health
stats = pool.get_stats()
print(f"Pool size: {stats['pool_size']}/{stats['max_size']}")
print(f"Timeouts: {stats['timeouts']}")
print(f"Total connections: {stats['total_connections']}")
¶ 3. Configure Appropriate Pool Sizes
# Base pool size on expected concurrent workers
workers = 15
pool = ConnectionPool(
size=workers, # Match worker count
max_overflow=workers//2 # 50% overflow capacity
)
¶ 4. Use Global Pool for Shared Access
# Application initialization
pool = get_global_pool(size=20, driver="pymysql")
# Throughout application
def some_function():
pool = get_global_pool()
with pool as conn:
# Use connection
pass
¶ 5. Handle Connection Errors Gracefully
from queue import Empty
try:
conn = pool.acquire(timeout=10.0)
try:
# Use connection
pass
finally:
pool.release(conn)
except Empty:
# Handle timeout
print("Could not acquire connection - pool exhausted")
except Exception as e:
# Handle other errors
print(f"Database error: {e}")
¶ Performance Tuning
¶ For High-Traffic Applications
pool = ConnectionPool(
size=30, # Larger base pool
max_overflow=20, # More overflow capacity
timeout=60.0, # Longer timeout
recycle=1800, # Recycle every 30 minutes
driver="pymysql" # Most stable driver
)
¶ For Low-Traffic Applications
pool = ConnectionPool(
size=5, # Smaller base pool
max_overflow=3, # Limited overflow
timeout=15.0, # Shorter timeout
recycle=7200, # Recycle every 2 hours
driver="pymysql"
)
¶ For Long-Running Background Workers
pool = ConnectionPool(
size=10,
max_overflow=5,
timeout=120.0, # Longer timeout for slow queries
recycle=3600, # Standard recycling
driver="pymysql"
)
¶ See Also
ObjData.md- Main data management class that uses connection poolingObjDataSql.md- SQL-specific operationsObjects.md- Core framework objects