Connection Pooling & Isolation

Key Points

• ADO.NET connection pool is per-process, keyed on the exact connection string. Each unique string spawns its own pool.
• Default pool sizes: Min Pool Size=0, Max Pool Size=100 — tune for high-RPS services.
• Long-lived connections in long-running queries / streaming = pool exhaustion.
• Five SQL isolation levels: Read Uncommitted, Read Committed (default), Repeatable Read, Serializable, Snapshot.
• Transaction.Current with TransactionScope enrolls connections in an ambient transaction — easy to misuse.
• Deadlock vs lock escalation are separate phenomena; both manifest as latency spikes / timeouts.

Concepts (deep dive)

Pool key = full connection string

"Server=A;Database=X;User=u;Password=p;" → pool 1
"Server=A;Database=X;User=u;Password=p;Application Name=svc1;" → pool 2 (different!)

Even differing whitespace or property order can fragment pools. Use a canonical form (e.g., centralized config; or SqlConnectionStringBuilder).

Pool size

Server=...;Min Pool Size=10;Max Pool Size=200;Connection Timeout=15;Pooling=true

Tune Max Pool Size for sustained connections × replicas. Common bug: burst load + slow queries → exhaust pool → InvalidOperationException: Timeout expired waiting for connection.

Lifetime under pooling

SqlConnection.Open() → check pool → reuse, or create new
SqlConnection.Close()/Dispose() → return to pool (still open at TCP level, just available)

Connections aren't actually torn down on Dispose — they go back to the pool. Set Connection Lifetime=300 (seconds) to force periodic recycling (useful behind load-balanced SQL Server with rolling failover).

Pool exhaustion symptoms

catch (InvalidOperationException ex) when (ex.Message.Contains("Timeout expired"))
{
    // Pool exhausted; check:
    // - Open connection leaks (missing Dispose)
    // - Long-running queries
    // - Pool size too small for load
}

Diagnose: - SELECT count(*) FROM sys.dm_exec_connections (SQL Server) — total open - dotnet-counters db.client.connections.usage - App Insights "Database" tab

Five isolation levels

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

Default Read Committed is right for most apps. Snapshot (SQL Server, requires DB option READ_COMMITTED_SNAPSHOT) eliminates many reader-vs-writer blocks.

READ_COMMITTED_SNAPSHOT (RCSI)

ALTER DATABASE [MyDb] SET READ_COMMITTED_SNAPSHOT ON;

Changes default behavior: reads use row versions instead of taking shared locks. Reduces lock contention significantly. Recommended for OLTP workloads unless you have specific reasons.

Deadlock vs lock escalation

Deadlock: two transactions hold locks the other needs; SQL Server picks a victim, kills it (Error 1205).

Lock escalation: SQL Server promotes many fine-grained locks to a single coarse lock (e.g., 5000 row locks → 1 table lock). Reduces overhead but blocks others.

-- Detect deadlocks (SQL Server):
SELECT * FROM sys.dm_tran_locks WHERE request_status = 'WAIT';

-- Lock escalation hint (use sparingly):
ALTER TABLE Orders SET (LOCK_ESCALATION = DISABLE);

Both surface as latency spikes / timeouts. Mitigate by: - Smaller transactions. - Consistent lock ordering. - Snapshot isolation (RCSI). - Index tuning (lock fewer rows).

Connection resiliency in .NET

opt.UseSqlServer(connStr, sql =>
    sql.EnableRetryOnFailure(maxRetryCount: 5, maxRetryDelay: TimeSpan.FromSeconds(30), null));

Wraps queries with retry on transient errors. Caveat: doesn't compose with explicit transactions automatically; use ExecutionStrategy.ExecuteAsync for transactions.

For Azure SQL: EnableRetryOnFailure() with no args picks reasonable defaults.

MARS (Multiple Active Result Sets)

Server=...;MultipleActiveResultSets=true

SQL Server feature: multiple open IDataReaders on the same connection. Useful when iterating results and triggering inner queries. EF Core enables it by default for SQL Server. Disable only if you have specific reasons.

Async + connection pool

OpenAsync returns the connection to caller; the call doesn't block a thread waiting for the network. Always prefer over sync Open in async code paths.

Code: correct vs wrong

❌ Wrong: connection-string fragmentation

var conn1 = new SqlConnection("Server=A;Database=X;User=u;Password=p");
var conn2 = new SqlConnection("Server=A;Database=X;User=u;Password=p; ");
// Whitespace different → 2 pools

✅ Correct: canonical builder

var b = new SqlConnectionStringBuilder { DataSource = "A", InitialCatalog = "X", UserID = "u", Password = "p" };
string canonical = b.ConnectionString;

❌ Wrong: long-running query holding pool connection

await foreach (var row in db.Orders.AsAsyncEnumerable().WithCancellation(ct))
    await SlowProcessAsync(row, ct);   // connection held the whole time

✅ Correct: materialize then process

var rows = await db.Orders.ToListAsync(ct);
foreach (var row in rows) await SlowProcessAsync(row, ct);

❌ Wrong: not setting Pool Size for high RPS

"Server=...;Database=...;..."   // defaults to Max=100; 10K RPS exhausts

✅ Correct

"Server=...;Database=...;Max Pool Size=500;Min Pool Size=20"

Design patterns for this topic

Pattern 1 — "Canonical connection strings"

• Intent: single pool per logical destination.

Pattern 2 — "RCSI for OLTP"

• Intent: reads don't block writers.

Pattern 3 — "Retry on transient failure"

• Intent: weather network blips, deadlock victims, throttling.

Pattern 4 — "Materialize before slow processing"

• Intent: release connection promptly.

Pattern 5 — "Tune pool sizes for sustained load"

• Intent: match Max Pool Size to RPS × concurrency factor.

Pros & cons / trade-offs

When to use / when to avoid

• Use canonical connection strings to avoid pool fragmentation.
• Use RCSI for OLTP (set DB option once).
• Avoid string-fragmenting connection strings across services.
• Avoid streaming queries that hold the connection during slow processing.

Interview Q&A

Q1. What's the connection-pool key? The exact connection string (with whitespace, property order, etc.). Subtle differences spawn separate pools.

Q2. What's Max Pool Size? Maximum simultaneous open connections per pool per process. Default 100.

Q3. Default isolation level in SQL Server? Read Committed (or Read Committed Snapshot if RCSI is on for the DB).

Q4. What's the deadlock vs lock escalation difference? Deadlock: two transactions block each other; one is killed. Lock escalation: SQL coarsens many fine locks to one big lock to save overhead.

Q5. What's RCSI? READ_COMMITTED_SNAPSHOT — reads use row versions; readers don't block writers and vice versa.

Q6. How do you detect pool exhaustion? Look for Timeout expired exceptions on Open; check dotnet-counters connection-usage metrics.

Q7. Should Min Pool Size be 0 or higher? For latency-sensitive services with predictable load, set Min Pool Size to keep N warm. Avoids cold-connection delay on first request.

Q8. What's MARS? Multiple Active Result Sets — multiple open readers per connection. SQL Server feature; EF Core enables by default.

Q9. Why does Connection Lifetime=N matter? Forces periodic recycle of pooled connections. Useful with rolling SQL upgrades or load balancers — old connections get retired.

Q10. Why might EnableRetryOnFailure skip transactions? Inside a BeginTransaction, EF's strategy can't retry the whole block. Use ExecutionStrategy.ExecuteAsync to wrap the entire transaction.

Gotchas / common mistakes

• ⚠️ Pool fragmentation from string differences.
• ⚠️ Streaming queries holding connections.
• ⚠️ Pool too small for production RPS.
• ⚠️ Mixed sync/async open — sync blocks threadpool.
• ⚠️ No transient retry on Azure SQL.
• ⚠️ Long transactions — deadlock risk + lock waits.

Further reading

• SQL Server connection pooling
• Isolation levels
• RCSI