I spent a whole day trying to debug why the backend would stop sending data after a while (-> it filled up its connection pool)
but after I turned pooling off, there were a number of dangling Client-Idle-Read DB connections
//not using Entity Framework in these N services, it's pure SQL and DB-AccessLayer here

Never happened before, looked up conf, looked up SO
Changed connectionClose to mandatory Dispose
Still didnt work.

long story short. Turns out in 1 of the services, the DataReader to POCO static method, was disposing/closing the READER and not the CONNECTION.

I sent it as tuple instead of 2 separate variables (reader, connection), and the Item1, Item2 got mixed up (:

Totally my fault, and imma avoid name-less tuples from now on like they a plague -.- thats my 2024-resolution

Were they typed object or something? I still like to avoid nameless tuples but unnamed and untyped is scary.

One should never turn off connection pooling.

If you feel the next time the need to do that, remember this situation.

Really glad you found it so fast, that could have been an nightmare for ages.

@spongessuck they were typed, but Both Reader and Connection had 'close' and 'dispose' functions

and I didnt unwrap them so accessing via Item1, Item2, caused the issue

The SOPs totally make sense now lol

@IntrusionCM ikr, but the fact that pooling can "fill up" and stops accepting requests means it has a potential to be overwhelmed (I think 100 is the connection pool limit)

I could rate-limit endpoints but thats all I can think of

might have to keep 2 data access layers, the 2nd with a separate non-pooled connection to the DB, only if the connection pool is filled

il look up what the "ideal design" for this situation is

@azuredivay You're over anticipating.

Yes, a connection pool has a limit, let's say 100.

That means that the connection pool holds up at max. 100 connections to the database.

Each connection can be reused - that's the whole point of the connection pool. Avoiding the cost of establishing a connection, which is quite high in databases.

You want to have as few connections as possible in a database, as you wish to keep resource usage manageable and calculable.

100 is already *very* high, which suggests there is something going on. ;)

The connection pool serves as a DDOS / OOM protection.

Thus, my hint to not disable connection pooling, as that's usually one step into the abyss of "database goes boom".

If the connection pool continuously runs out of connections, it is a sign that something is very wrong.

It might be long-running or "not properly closed" connections, issues in transaction handling...

Fix the issues, but don't open the flood gate.

@IntrusionCM true.. instead of trying to chase the "what Il do when my connections are overwhelmed"

Focus should be on code-level to avoid that, else it's a never-ending chase and at this point it'd be needless trouble -,-

imma go through the DB flows again and see what I missed