Connection Pooling - Learning Module

Loading content...

0/246

Returning Connections to Pool

The Most Error-Prone Phase

Of all connection pool operations, returning connections is the most error-prone and the source of the most subtle, difficult-to-diagnose bugs. A connection that isn't properly returned causes a connection leak—the pool's available connections slowly decrease until exhaustion, bringing down the entire application.

But the problems go beyond leaks. A connection returned in an improper state—with an uncommitted transaction, temporary tables, modified session variables, or cached query plans from the previous user—can cause data corruption, security violations, or mysterious application errors.

Mastering connection return is essential for building reliable pooled systems.

What You Will Learn

By the end of this page, you will understand why connection return is critical, the types of state that must be cleaned up, patterns for ensuring connections are always returned, leak detection strategies, and best practices for connection release in different programming paradigms.

The Connection Return Contract

When a connection is returned to the pool, it must satisfy a return contract—a set of conditions that ensure the connection is safe for the next user. Violating this contract causes problems ranging from subtle bugs to catastrophic failures.

The Return Contract Requirements:

Conditions for Safe Connection Return

•No Active Transaction — Any open transaction must be committed or rolled back. Uncommitted transactions hold locks that block other connections.
•No Temporary Objects — Temp tables, prepared statements, cursors must be cleaned. They consume memory and may conflict with next user's operations.
•Default Session Settings — Modified session variables (timezone, isolation level, transaction mode) must be reset to defaults.
•No Pending Results — Any unread result sets must be consumed or discarded. Open cursors can block server resources.
•Valid Connection State — The connection must still be functional—not closed, not in error state, not disconnected.
•No Leaked Context — Application-specific context (user ID in session variable, tenant context) must be cleared for security.

Consequences of Contract Violations
Violation	Consequence	Severity	Detection Difficulty
Uncommitted transaction	Locks held indefinitely; blocking; connection stuck	Critical	Easy (immediate blocking)
Temporary tables left	Memory leak; potential name conflicts	Moderate	Moderate (grows slowly)
Modified session variables	Unexpected behavior in next query	High	Hard (intermittent, confusing)
Open cursor/results	Server resource leak; potential blocking	Moderate	Moderate (server metrics)
Connection in error state	Next query fails mysteriously	High	Easy (immediate failure)
Leaked security context	Security violation; wrong user context	Critical	Very hard (security incident)

The Session Variable Security Risk

Some applications set session variables for security context (SET app.current_user_id = '123'). If not cleared on return, the next user inherits this context and may operate with wrong permissions. This is a common security vulnerability in pooled environments. Always clear security-relevant session state.

Connection Cleanup Patterns

Different databases provide different mechanisms for resetting connection state. Understanding your database's options is essential for proper cleanup.

PostgreSQL Cleanup Options

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
-- ROLLBACK: Cancel any uncommitted transaction
-- Always safe to run, even without active transaction
ROLLBACK;
 
-- DISCARD ALL: Comprehensive cleanup, resets:
--   - Prepared statements
--   - Temporary tables
--   - Session variables (SET values)
--   - Sequence values
--   - Plan cache
-- Warning: Cannot run inside transaction block
DISCARD ALL;
 
-- Alternative: Selective DISCARD commands
DISCARD TEMP;        -- Drop temp tables only
DISCARD PLANS;       -- Clear query plan cache
DISCARD SEQUENCES;   -- Reset currval for sequences
 
-- RESET ALL: Reset GUC (configuration) parameters to defaults
-- Less aggressive than DISCARD ALL
RESET ALL;
 
-- For PgBouncer transaction pooling, common pattern:
-- (PgBouncer can run this automatically via server_reset_query)
ROLLBACK;
DISCARD ALL;
 
-- Close any open cursors
-- Note: DISCARD ALL also does this, but if you can't use DISCARD:
CLOSE ALL;
 
-- Unlisten all notification channels
UNLISTEN *;

MySQL Cleanup Options

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
-- ROLLBACK: Same as PostgreSQL
ROLLBACK;
 
-- Reset connection to fresh state
-- Clears: temp tables, user variables, prepared statements, and more
RESET CONNECTION;  -- MySQL 5.7.3+
 
-- Alternative for older versions - COM_CHANGE_USER protocol command
-- (done through driver, not SQL)
 
-- Deallocate all prepared statements
-- Note: RESET CONNECTION does this automatically
DEALLOCATE PREPARE stmt1;  -- For each statement
 
-- Drop temporary tables (if RESET CONNECTION unavailable)
DROP TEMPORARY TABLE IF EXISTS temp_results;
 
-- Reset session variables
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;  -- or your default
SET SESSION sql_mode = (SELECT @@GLOBAL.sql_mode);
 
-- Clear user-defined variables
-- There's no single command - they disappear on RESET CONNECTION
SET @my_var = NULL;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
/**
 * Connection Cleanup Handler
 * 
 * Implements thorough cleanup for different database types.
 * Called on connection release before returning to pool.
 */
 
interface CleanupResult {
    success: boolean;
    duration: number;
    step?: string;
    error?: Error;
}
 
type DatabaseType = 'postgresql' | 'mysql' | 'mssql';
 
class ConnectionCleaner {
    private dbType: DatabaseType;
    
    constructor(dbType: DatabaseType) {
        this.dbType = dbType;
    }
    
    /**
     * Clean connection state for safe reuse
     * 
     * Returns success if connection is safely reusable,
     * false if connection should be destroyed instead.
     */
    async cleanup(connection: Connection): Promise<CleanupResult> {
        const start = Date.now();
        
        try {
            switch (this.dbType) {
                case 'postgresql':
                    return await this.cleanupPostgres(connection);
                case 'mysql':
                    return await this.cleanupMySQL(connection);
                case 'mssql':
                    return await this.cleanupMSSQL(connection);
            }
        } catch (error) {
            return {
                success: false,
                duration: Date.now() - start,
                error: error as Error
            };
        }
    }
    
    private async cleanupPostgres(conn: Connection): Promise<CleanupResult> {
        const start = Date.now();
        
        // Step 1: Rollback any active transaction
        try {
            await conn.query('ROLLBACK');
        } catch (e) {
            // ROLLBACK failing is concerning but not fatal
            console.warn('ROLLBACK failed during cleanup:', e);
        }
        
        // Step 2: Full state reset
        try {
            await conn.query('DISCARD ALL');
        } catch (e) {
            // If DISCARD ALL fails (e.g., we're in failed transaction state),
            // try more aggressive recovery
            try {
                await conn.query('ROLLBACK');
                await conn.query('DISCARD ALL');
            } catch (e2) {
                // Connection is in unrecoverable state
                return {
                    success: false,
                    duration: Date.now() - start,
                    step: 'DISCARD ALL',
                    error: e2 as Error
                };
            }
        }
        
        // Step 3: Validate connection still works
        try {
            await conn.query('SELECT 1');
        } catch (e) {
            return {
                success: false,
                duration: Date.now() - start,
                step: 'validation',
                error: e as Error
            };
        }
        
        return {
            success: true,
            duration: Date.now() - start
        };
    }
    
    private async cleanupMySQL(conn: Connection): Promise<CleanupResult> {
        const start = Date.now();
        
        try {
            // Step 1: Rollback
            await conn.query('ROLLBACK');
            
            // Step 2: Reset connection (MySQL 5.7.3+)
            await conn.query('RESET CONNECTION');
            
            // Step 3: Validate
            const result = await conn.query('SELECT 1 AS test');
            if (result.rows[0]?.test !== 1) {
                throw new Error('Validation query returned unexpected result');
            }
            
            return {
                success: true,
                duration: Date.now() - start
            };
        } catch (e) {
            return {
                success: false,
                duration: Date.now() - start,
                error: e as Error
            };
        }
    }
    
    private async cleanupMSSQL(conn: Connection): Promise<CleanupResult> {
        const start = Date.now();
        
        try {
            // SQL Server specific cleanup
            await conn.query(`
                IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION;
                
                -- Clear any cached query plans for this session
                DBCC FREEPROCCACHE;  -- Note: Heavy operation, use judiciously
                
                -- Reset session options to defaults
                SET IMPLICIT_TRANSACTIONS OFF;
                SET XACT_ABORT OFF;
                SET NOCOUNT OFF;
            `);
            
            // Validate
            await conn.query('SELECT 1');
            
            return {
                success: true,
                duration: Date.now() - start
            };
        } catch (e) {
            return {
                success: false,
                duration: Date.now() - start,
                error: e as Error
            };
        }
    }
}

Guaranteed Return Patterns

The most common cause of connection leaks is forgetting to return connections, especially when exceptions occur. Different programming languages offer patterns to guarantee return.

Pattern 1: Try-Finally (Universal)

The most basic pattern works in every language: acquire in try, release in finally.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
/**
 * Try-Finally Pattern
 * 
 * The fundamental pattern for guaranteed resource cleanup.
 * Works in any language with try-finally support.
 */
 
async function executeQuery(pool: ConnectionPool, sql: string): Promise<any> {
    const connection = await pool.acquire();
    
    try {
        // Use the connection
        const result = await connection.query(sql);
        return result;
    } finally {
        // ALWAYS executes - even if exception thrown
        await pool.release(connection);
    }
}
 
// Even with early returns, finally executes:
async function getUserById(pool: ConnectionPool, id: string): Promise<User | null> {
    const connection = await pool.acquire();
    
    try {
        // Early return case
        if (!id) {
            return null;  // finally still runs!
        }
        
        const result = await connection.query(
            'SELECT * FROM users WHERE id = $1',
            [id]
        );
        
        return result.rows[0] || null;
        
    } finally {
        await pool.release(connection);  // Always executed
    }
}
 
// Exception case - finally runs before exception propagates:
async function riskyOperation(pool: ConnectionPool): Promise<void> {
    const connection = await pool.acquire();
    
    try {
        await connection.query('SELECT * FROM nonexistent_table');
        // Exception thrown here ^
        
    } finally {
        // This runs even though exception was thrown
        await pool.release(connection);
        // Then exception propagates to caller
    }
}

Pattern 2: Resource Wrapper / Dispose Pattern

Modern languages provide syntax sugar for try-finally with resources. Python has with, JavaScript/TypeScript has the upcoming using declaration, C# has using, Java has try-with-resources.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
/**
 * Resource Wrapper Pattern
 * 
 * Uses Symbol.dispose (TC39 proposal, available in TypeScript 5.2+)
 * for automatic resource cleanup similar to C#'s 'using' and
 * Python's 'with'.
 */
 
// Make pool connection disposable
class DisposableConnection implements Disposable {
    private connection: Connection;
    private pool: ConnectionPool;
    private released: boolean = false;
    
    constructor(connection: Connection, pool: ConnectionPool) {
        this.connection = connection;
        this.pool = pool;
    }
    
    get conn(): Connection {
        if (this.released) {
            throw new Error('Connection already released');
        }
        return this.connection;
    }
    
    // Called automatically when leaving scope
    [Symbol.dispose](): void {
        if (!this.released) {
            this.released = true;
            this.pool.release(this.connection);
        }
    }
}
 
// Acquire that returns disposable
async function acquireDisposable(pool: ConnectionPool): Promise<DisposableConnection> {
    const conn = await pool.acquire();
    return new DisposableConnection(conn, pool);
}
 
// Usage with 'using' keyword (TypeScript 5.2+)
async function modernUsage(pool: ConnectionPool): Promise<User[]> {
    using conn = await acquireDisposable(pool);
    
    // Connection automatically released when scope ends,
    // even if exception thrown
    const result = await conn.conn.query('SELECT * FROM users');
    return result.rows;
}  // conn[Symbol.dispose]() called here automatically
 
// For async disposal, use Symbol.asyncDispose
class AsyncDisposableConnection implements AsyncDisposable {
    private connection: Connection;
    private pool: ConnectionPool;
    
    constructor(connection: Connection, pool: ConnectionPool) {
        this.connection = connection;
        this.pool = pool;
    }
    
    async [Symbol.asyncDispose](): Promise<void> {
        // Can do async cleanup
        await this.pool.release(this.connection);
    }
}
 
// Usage with 'await using'
async function asyncDisposableUsage(pool: ConnectionPool): Promise<void> {
    await using conn = await acquireAsyncDisposable(pool);
    
    await conn.connection.query('UPDATE users SET active = true');
    
}  // await conn[Symbol.asyncDispose]() called here

Pattern 3: Callback/Closure Pattern

Instead of returning a connection for the caller to manage, provide a callback that receives the connection. The pool manages acquisition and release.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
/**
 * Callback Pattern
 * 
 * The pool manages the entire lifecycle - caller cannot leak
 * because they never directly hold the connection.
 */
 
class CallbackPool {
    /**
     * Execute work with a connection from the pool.
     * Connection is automatically returned after callback completes.
     */
    async withConnection<T>(
        work: (connection: Connection) => Promise<T>
    ): Promise<T> {
        const connection = await this.acquire();
        
        try {
            return await work(connection);
        } finally {
            await this.release(connection);
        }
    }
    
    /**
     * Execute work within a transaction.
     * Transaction is committed on success, rolled back on error.
     */
    async withTransaction<T>(
        work: (connection: Connection) => Promise<T>
    ): Promise<T> {
        return this.withConnection(async (connection) => {
            await connection.query('BEGIN');
            
            try {
                const result = await work(connection);
                await connection.query('COMMIT');
                return result;
            } catch (error) {
                await connection.query('ROLLBACK');
                throw error;
            }
        });
    }
}
 
// Usage - impossible to leak!
async function createUser(pool: CallbackPool, user: User): Promise<void> {
    await pool.withTransaction(async (conn) => {
        await conn.query(
            'INSERT INTO users (id, name, email) VALUES ($1, $2, $3)',
            [user.id, user.name, user.email]
        );
        
        await conn.query(
            'INSERT INTO user_profiles (user_id) VALUES ($1)',
            [user.id]
        );
        
        // If either INSERT fails, transaction is rolled back
        // Connection is always returned to pool
    });
}
 
// Can't forget to release - no connection variable in scope
async function getUsers(pool: CallbackPool): Promise<User[]> {
    return pool.withConnection(async (conn) => {
        const result = await conn.query('SELECT * FROM users');
        return result.rows;
    });
}

Prefer Callback Pattern for Safety

The callback pattern is the safest approach—it's impossible to forget to release because you never hold the connection directly. Most modern ORMs and database libraries use this pattern (e.g., Prisma's transaction callbacks, TypeORM's transaction manager). Only use direct acquisition when you need finer control over connection timing.

Connection Leak Detection

Despite best practices, connection leaks happen. Robust pools detect leaks early, before they cause outages.

Leak Detection Strategies

Leak Detection Approaches
Strategy	How It Works	Pros	Cons
Timeout Detection	Alert if connection held > threshold	Simple, catches most leaks	False positives with long operations
Stack Trace Capture	Record stack at acquisition, log on leak	Shows exactly where leak occurred	Performance overhead
Metrics Monitoring	Alert when available connections decline over time	Non-invasive, catches trends	Slow detection; can't pinpoint source
Periodic Scan	Background thread checks connection ages	Low overhead	Delayed detection
Reference Tracking	Use weak references to detect unreturned connections	Catches actual leaks (GC'd references)	Language-specific; complex

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
/**
 * Connection Leak Detector
 * 
 * Implements multiple strategies for detecting unreturned connections.
 */
 
interface LeakRecord {
    connectionId: string;
    acquiredAt: Date;
    acquireStack: string;
    lastActivityAt: Date;
}
 
interface LeakDetectionConfig {
    // Time threshold before warning
    warningThresholdMs: number;     // e.g., 30 seconds
    
    // Time threshold before considering leaked
    leakThresholdMs: number;        // e.g., 5 minutes
    
    // Whether to capture stack traces (performance impact)
    captureStackTraces: boolean;
    
    // How often to scan for leaks
    scanIntervalMs: number;         // e.g., 30 seconds
    
    // Callback when potential leak detected
    onLeakDetected: (leak: LeakRecord) => void;
}
 
class LeakDetectingPool {
    private config: LeakDetectionConfig;
    private activeConnections: Map<string, LeakRecord> = new Map();
    
    constructor(config: LeakDetectionConfig) {
        this.config = config;
        this.startLeakScanner();
    }
    
    async acquire(): Promise<Connection> {
        const connection = await this.doAcquire();
        
        // Record acquisition for tracking
        const record: LeakRecord = {
            connectionId: this.getConnectionId(connection),
            acquiredAt: new Date(),
            acquireStack: this.config.captureStackTraces 
                ? new Error().stack || '' 
                : '',
            lastActivityAt: new Date()
        };
        
        this.activeConnections.set(record.connectionId, record);
        
        // Set up warning timer
        const warningTimeout = setTimeout(() => {
            console.warn(
                `Connection ${record.connectionId} held for >` +
                `${this.config.warningThresholdMs}ms. 
` +
                `Acquired at: ${record.acquiredAt.toISOString()}
` +
                `Stack: ${record.acquireStack}`
            );
        }, this.config.warningThresholdMs);
        
        // Wrap connection to clear timer on release
        return this.wrapConnection(connection, record.connectionId, warningTimeout);
    }
    
    private wrapConnection(
        connection: Connection,
        connectionId: string,
        warningTimeout: NodeJS.Timeout
    ): Connection {
        const pool = this;
        
        return new Proxy(connection, {
            get(target, prop) {
                // Track activity
                const record = pool.activeConnections.get(connectionId);
                if (record) {
                    record.lastActivityAt = new Date();
                }
                
                // Intercept close/release
                if (prop === 'close' || prop === 'release') {
                    return async () => {
                        clearTimeout(warningTimeout);
                        pool.activeConnections.delete(connectionId);
                        await pool.release(target);
                    };
                }
                
                return target[prop as keyof Connection];
            }
        });
    }
    
    /**
     * Background scanner for leaked connections
     */
    private startLeakScanner(): void {
        setInterval(() => {
            const now = Date.now();
            
            for (const [connId, record] of this.activeConnections) {
                const heldDuration = now - record.acquiredAt.getTime();
                
                if (heldDuration > this.config.leakThresholdMs) {
                    // This is likely a leak
                    this.config.onLeakDetected(record);
                    
                    // Log detailed information
                    console.error(
                        `🚨 CONNECTION LEAK DETECTED 🚨
` +
                        `Connection ID: ${connId}
` +
                        `Held for: ${(heldDuration / 1000).toFixed(1)} seconds
` +
                        `Acquired at: ${record.acquiredAt.toISOString()}
` +
                        `Last activity: ${record.lastActivityAt.toISOString()}
` +
                        `Acquisition stack:
${record.acquireStack}`
                    );
                    
                    // Optionally: force reclaim the connection
                    // WARNING: This can cause errors in the code still using it
                    // this.forceReclaim(connId);
                }
            }
        }, this.config.scanIntervalMs);
    }
    
    /**
     * Force reclaim a leaked connection
     * 
     * Use with extreme caution - can cause errors in code
     * still thinking it has a valid connection.
     */
    private forceReclaim(connectionId: string): void {
        const record = this.activeConnections.get(connectionId);
        if (!record) return;
        
        console.warn(
            `Force reclaiming connection ${connectionId}. ` +
            `Code still using this connection will experience errors.`
        );
        
        this.activeConnections.delete(connectionId);
        
        // Find the actual connection and return/destroy it
        // Implementation depends on pool internals
    }
    
    /**
     * Get leak statistics for monitoring
     */
    getLeakStats(): {
        activeConnections: number;
        suspiciousConnections: number;  // Held > warning threshold
        likelyLeaks: number;            // Held > leak threshold
    } {
        const now = Date.now();
        let suspicious = 0;
        let leaks = 0;
        
        for (const record of this.activeConnections.values()) {
            const duration = now - record.acquiredAt.getTime();
            
            if (duration > this.config.leakThresholdMs) {
                leaks++;
            } else if (duration > this.config.warningThresholdMs) {
                suspicious++;
            }
        }
        
        return {
            activeConnections: this.activeConnections.size,
            suspiciousConnections: suspicious,
            likelyLeaks: leaks
        };
    }
}
 
// Configuration for production
const leakConfig: LeakDetectionConfig = {
    warningThresholdMs: 30_000,      // Warn after 30 seconds
    leakThresholdMs: 300_000,        // Leak after 5 minutes
    captureStackTraces: true,         // Enable in development/staging
    scanIntervalMs: 30_000,           // Scan every 30 seconds
    onLeakDetected: (leak) => {
        // Send to monitoring system
        metrics.increment('pool.leaks.detected');
        alerting.warn('Connection leak detected', { 
            connectionId: leak.connectionId,
            heldSince: leak.acquiredAt,
            stack: leak.acquireStack
        });
    }
};

Stack Trace Performance Impact

Capturing stack traces on every acquisition has measurable overhead (~1-5ms per acquisition). Enable in development and staging for debugging, but consider disabling or sampling in production. Even without stack traces, timeout detection catches most leaks.

Transaction Boundary Management

The most dangerous state for a returned connection is an uncommitted transaction. Transactions hold locks that can block other connections, cause deadlocks, or result in data being unexpectedly rolled back.

Transaction-Safe Release Pattern

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
/**
 * Transaction-Aware Connection Release
 * 
 * Ensures transactions are properly terminated before
 * returning connections to the pool.
 */
 
enum TransactionState {
    NONE = 'none',
    ACTIVE = 'active',
    FAILED = 'failed',
    COMMITTED = 'committed',
    ROLLED_BACK = 'rolled_back'
}
 
class TransactionTrackingConnection {
    private connection: Connection;
    private txState: TransactionState = TransactionState.NONE;
    private txStartStack?: string;
    
    constructor(connection: Connection) {
        this.connection = connection;
    }
    
    async query(sql: string, params?: any[]): Promise<any> {
        const upperSql = sql.trim().toUpperCase();
        
        // Track transaction state
        if (upperSql.startsWith('BEGIN') || upperSql.startsWith('START TRANSACTION')) {
            this.txState = TransactionState.ACTIVE;
            this.txStartStack = new Error().stack;
        } else if (upperSql.startsWith('COMMIT')) {
            this.txState = TransactionState.COMMITTED;
        } else if (upperSql.startsWith('ROLLBACK')) {
            this.txState = TransactionState.ROLLED_BACK;
        }
        
        try {
            return await this.connection.query(sql, params);
        } catch (error) {
            // Query failure in transaction puts it in failed state
            if (this.txState === TransactionState.ACTIVE) {
                this.txState = TransactionState.FAILED;
            }
            throw error;
        }
    }
    
    get transactionState(): TransactionState {
        return this.txState;
    }
    
    get hasActiveTransaction(): boolean {
        return this.txState === TransactionState.ACTIVE || 
               this.txState === TransactionState.FAILED;
    }
    
    /**
     * Safely terminate any active transaction
     */
    async terminateTransaction(): Promise<void> {
        if (!this.hasActiveTransaction) {
            return;
        }
        
        console.warn(
            `Connection released with active transaction. ` +
            `State: ${this.txState}. Rolling back.
` +
            `Transaction started at:
${this.txStartStack}`
        );
        
        try {
            await this.connection.query('ROLLBACK');
            this.txState = TransactionState.ROLLED_BACK;
        } catch (error) {
            // Rollback failed - connection may be unusable
            throw new Error(
                `Failed to rollback transaction on release: ${error}`
            );
        }
    }
}
 
class TransactionSafePool {
    /**
     * Release with transaction safety
     */
    async release(connection: TransactionTrackingConnection): Promise<void> {
        // Ensure no transaction leaks
        if (connection.hasActiveTransaction) {
            try {
                await connection.terminateTransaction();
            } catch (error) {
                // Transaction cleanup failed - destroy connection
                console.error('Transaction cleanup failed:', error);
                await this.destroy(connection);
                return;
            }
        }
        
        // Full cleanup
        await this.cleanAndReturn(connection);
    }
    
    /**
     * Safe transaction wrapper - guarantees transaction terminates
     */
    async transaction<T>(
        work: (conn: TransactionTrackingConnection) => Promise<T>
    ): Promise<T> {
        const connection = await this.acquire();
        
        try {
            await connection.query('BEGIN');
            
            const result = await work(connection);
            
            await connection.query('COMMIT');
            return result;
            
        } catch (error) {
            // Rollback on any error
            try {
                await connection.query('ROLLBACK');
            } catch (rollbackError) {
                console.error('Rollback failed:', rollbackError);
            }
            throw error;
            
        } finally {
            await this.release(connection);
        }
    }
}
 
// Usage - transactions are always properly terminated
const pool = new TransactionSafePool(config);
 
await pool.transaction(async (conn) => {
    await conn.query('INSERT INTO orders VALUES ($1)', [order.id]);
    await conn.query('UPDATE inventory SET count = count - 1 WHERE id = $1', [item.id]);
    
    // If anything throws, entire transaction rolls back
    // Connection always returns clean to pool
});

The Implicit Commit Trap

Some databases (MySQL, SQL Server) have 'implicit commit' for DDL statements. Running 'CREATE TABLE' or 'ALTER TABLE' inside a transaction automatically commits. If code expects rollback capability after DDL, it won't work. Be especially careful with DDL in transactional code.

Error Handling During Release

What happens when errors occur during connection release? The cleanup commands fail, the validation fails, or the network drops during return? Handling these scenarios correctly prevents both leaks and corrupt connections.

Error Scenarios During Release

•Cleanup Query Fails — ROLLBACK or DISCARD ALL throws error (connection in bad state, network issue)
•Validation Fails — Post-cleanup validation query fails (connection dead)
•Network Timeout — Cleanup takes too long (network partition, database overload)
•Pool Already Closed — Application shutdown in progress, pool destroyed
•Connection Already Released — Double-release attempt (programming error)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
/**
 * Robust Connection Release
 * 
 * Handles errors during release to prevent both leaks
 * and returning corrupted connections to the pool.
 */
 
class RobustPool {
    private isShuttingDown: boolean = false;
    private releasedConnections: WeakSet<Connection> = new WeakSet();
    
    async release(connection: Connection): Promise<void> {
        // Guard: Already released
        if (this.releasedConnections.has(connection)) {
            console.warn('Attempted to release already-released connection');
            return;  // Silently ignore double-release
        }
        
        // Guard: Pool shutting down
        if (this.isShuttingDown) {
            // During shutdown, destroy connections instead of pooling
            await this.destroySafely(connection);
            return;
        }
        
        // Track that we're handling this connection
        this.releasedConnections.add(connection);
        
        // Attempt cleanup with timeout
        const cleanupResult = await this.cleanupWithTimeout(
            connection, 
            5000  // 5 second timeout for cleanup
        );
        
        if (!cleanupResult.success) {
            // Cleanup failed - connection is not safe to reuse
            console.warn(
                `Connection cleanup failed: ${cleanupResult.error?.message}. ` +
                `Destroying connection instead of returning to pool.`
            );
            
            await this.destroySafely(connection);
            
            // Ensure pool maintains minimum connections
            await this.ensureMinimumConnections();
            return;
        }
        
        // Validate connection is still healthy
        const isHealthy = await this.validateWithTimeout(connection, 3000);
        
        if (!isHealthy) {
            console.warn('Post-cleanup validation failed. Destroying connection.');
            await this.destroySafely(connection);
            await this.ensureMinimumConnections();
            return;
        }
        
        // All checks passed - return to pool
        this.returnToPool(connection);
    }
    
    private async cleanupWithTimeout(
        connection: Connection,
        timeoutMs: number
    ): Promise<{ success: boolean; error?: Error }> {
        try {
            await Promise.race([
                this.doCleanup(connection),
                this.timeout(timeoutMs, 'Cleanup timed out')
            ]);
            
            return { success: true };
            
        } catch (error) {
            return { 
                success: false, 
                error: error as Error 
            };
        }
    }
    
    private async validateWithTimeout(
        connection: Connection,
        timeoutMs: number
    ): Promise<boolean> {
        try {
            await Promise.race([
                connection.query('SELECT 1'),
                this.timeout(timeoutMs, 'Validation timed out')
            ]);
            
            return true;
            
        } catch {
            return false;
        }
    }
    
    private async destroySafely(connection: Connection): Promise<void> {
        try {
            await Promise.race([
                connection.close(),
                this.timeout(5000, 'Close timed out')
            ]);
        } catch (error) {
            // Even close failed - just abandon the connection
            // It will eventually be cleaned up by the database
            console.error('Connection close failed:', error);
        }
    }
    
    private timeout(ms: number, message: string): Promise<never> {
        return new Promise((_, reject) => {
            setTimeout(() => reject(new Error(message)), ms);
        });
    }
    
    /**
     * Graceful shutdown sequence
     */
    async shutdown(): Promise<void> {
        this.isShuttingDown = true;
        
        // Wait for in-flight queries to complete (with timeout)
        const shutdownTimeout = 30_000;  // 30 seconds
        const start = Date.now();
        
        while (this.inUse.size > 0) {
            if (Date.now() - start > shutdownTimeout) {
                console.warn(
                    `Shutdown timeout. ${this.inUse.size} connections still in use. ` +
                    `Force closing.`
                );
                break;
            }
            await new Promise(resolve => setTimeout(resolve, 100));
        }
        
        // Close all available connections
        const closePromises = this.available.map(conn => 
            this.destroySafely(conn)
        );
        
        await Promise.all(closePromises);
        
        // Force close any remaining in-use connections
        for (const conn of this.inUse) {
            await this.destroySafely(conn);
        }
        
        console.log('Pool shutdown complete');
    }
}

Summary: Returning Connections Safely

We've completed our deep dive into the final phase of connection lifecycle: safe return. Let's consolidate the key insights:

Key Takeaways

•The return contract is non-negotiable — Connections must be returned without active transactions, temp objects, or modified session state. Security context must be cleared.
•Use database-specific cleanup — DISCARD ALL (Postgres), RESET CONNECTION (MySQL), and similar commands provide thorough state reset. Know your database's options.
•Prefer callback patterns — The pool.withConnection() pattern makes leaks impossible. Direct acquisition should be rare and wrapped in try-finally.
•Implement leak detection — Timeout warnings, stack trace capture, and metrics monitoring catch leaks before they cause outages.
•Track transaction state — Explicit transaction tracking ensures automatic rollback on release. Never return a connection with open transaction.
•Handle release errors gracefully — When cleanup fails, destroy the connection rather than returning a potentially corrupted connection to the pool.

Module Complete!

You've now mastered connection pooling from end to end:

Why Pool — Understood the cost of connections and why pooling is essential
Pool Design — Explored architecture patterns, configuration, and validation strategies
Pool Sizing — Learned the mathematics of sizing, database constraints, and monitoring
Connection Return — Mastered cleanup, leak detection, and transaction safety

Connection pooling is a foundational skill for building scalable, reliable systems. The patterns and principles you've learned apply not just to databases, but to any expensive, reusable resource.

Module Complete

Congratulations! You've completed the Connection Pooling module. You now understand why pooling is essential, how to design effective pools, how to size them correctly, and how to ensure connections are safely returned. These skills are fundamental to building production-grade applications that scale reliably.