What happens when you make more database requests than the pool of available SQL connections?

Question

As far as I'm aware, in ASP.NET Core when creating SqlConnection's they are fetched from a pool of already open such connections, as the operation for opening a single connection is very costly and expensive, so we're effectively re-using these connections.

If we have 10 pooled connections but we end up creating 40 of them and querying the database - what exactly will happen? Will the 40 connections effectively go through the pool of 10 available connections in some FIFO basis? Will new connections be created if all of those in the pool are currently in use, similarly to how a new thread gets added to the thread pool?

How exactly does it all work?

The reason for my interest in learning this is to gauge how likely it is to get a socket exhaustion from opening a ton of connections, but first I need to understand how the pool works.

Answer 1

You can read the documentation here:

When a connection is first opened, a connection pool is created based on an exact matching algorithm that associates the pool with the connection string in the connection. Each connection pool is associated with a distinct connection string. When a new connection is opened, if the connection string is not an exact match to an existing pool, a new pool is created. Connections are pooled per process, per application domain, per connection string and when integrated security is used, per Windows identity. Connection strings must also be an exact match; keywords supplied in a different order for the same connection will be pooled separately. A connection pool is created for each unique connection string. When a pool is created, multiple connection objects are created and added to the pool so that the minimum pool size requirement is satisfied. Connections are added to the pool as needed, up to the maximum pool size specified (100 is the default). Connections are released back into the pool when they are closed or disposed.

When a SqlConnection object is requested, it is obtained from the pool if a usable connection is available. To be usable, a connection must be unused, have a matching transaction context or be unassociated with any transaction context, and have a valid link to the server.

The connection pooler satisfies requests for connections by reallocating connections as they are released back into the pool. If the maximum pool size has been reached and no usable connection is available, the request is queued. The pooler then tries to reclaim any connections until the time-out is reached (the default is 15 seconds). If the pooler cannot satisfy the request before the connection times out, an exception is thrown.