数据库连接池安全与资源控制（泄漏检测-超时-上限）最佳实践

一、参数与校验type PoolOpt = { max: number; idleTimeoutMs: number; acquireTimeoutMs: number }

function validPool(opt: PoolOpt): boolean { return opt.max > 0 && opt.max <= 1000 && opt.idleTimeoutMs >= 1000 && opt.acquireTimeoutMs >= 100 }

二、封装与泄漏检测class LeakDetector {

inUse = new Set<string>()

start(id: string) { this.inUse.add(id) }

end(id: string) { this.inUse.delete(id) }

count(): number { return this.inUse.size }

}

type Conn = { id: string; query: (sql: string, params?: any[]) => Promise<any>; close: () => Promise<void> }

class DbPool {

opt: PoolOpt

leak = new LeakDetector()

queue: Conn[] = []

constructor(opt: PoolOpt) { this.opt = opt }

async acquire(): Promise<Conn> {

const start = Date.now()

while (Date.now() - start < this.opt.acquireTimeoutMs) {

if (this.queue.length > 0) {

const c = this.queue.pop() as Conn

this.leak.start(c.id)

return c

}

await new Promise(r => setTimeout(r, 10))

}

throw new Error('acquire_timeout')

}

async release(c: Conn) { this.leak.end(c.id); this.queue.push(c) }

}

三、慢查询与审计type Audit = { id: string; sql: string; durationMs: number; timestamp: string }

function nowIso(): string { return new Date().toISOString() }

async function timedQuery(c: Conn, sql: string, params: any[], thresholdMs: number): Promise<{ rows: any[]; audit?: Audit }> {

const t0 = Date.now()

const rows = await c.query(sql, params)

const dt = Date.now() - t0

const audit = dt >= thresholdMs ? { id: c.id, sql, durationMs: dt, timestamp: nowIso() } : undefined

return { rows, audit }

}

四、断路与回退class Circuit {

fail = 0; open = false

threshold: number

constructor(threshold: number) { this.threshold = threshold }

trip() { this.fail++; if (this.fail >= this.threshold) this.open = true }

reset() { this.fail = 0; this.open = false }

}

async function guardedQuery(pool: DbPool, circuit: Circuit, sql: string, params: any[]): Promise<any> {

if (circuit.open) throw new Error('circuit_open')

const c = await pool.acquire()

try { const r = await timedQuery(c, sql, params, 200); circuit.reset(); return r }

catch (e) { circuit.trip(); throw e }

finally { await pool.release(c) }

}

五、验收清单最大连接、空闲与获取超时参数校验通过；队列可用且超时返回错误。泄漏检测在获取与释放时记录；慢查询审计包含SQL与耗时。断路器在失败达到阈值时打开并回退；恢复后重置计数。