Database Connection - Pooling

Pooling

Database connections are finite and expensive and can take a disproportionately long time to create relative to the operations performed on them. It is very inefficient for an application to create and close a database connection whenever it needs to update a database.

Connection pooling is a technique designed to alleviate this problem. A pool of database connections can be created and then shared among the applications that need to access the database. When an application needs database access, it requests a connection from the pool. When it is finished, it returns the connection to the pool, where it becomes available for use by other applications.

The connection object obtained from the connection pool is often a wrapper around the actual database connection. The wrapper understands its relationship with the pool, and hides the details of the pool from the application. For example, the wrapper object can implement a "close" method that can be called just like the "close" method on the database connection. Unlike the method on the database connection, the method on the wrapper may not actually close the database connection, but instead return it to the pool. The application does not need to be aware of the connection pooling when it calls the methods on the wrapper object.

This approach encourages the practice of opening a connection in an application only when needed, and closing it as soon as the work is done, rather than holding a connection open for the entire life of the application. In this manner, a relatively small number of connections can service a large number of requests. This is also called multiplexing.

In a client–server architecture, on the other hand, a persistent connection is typically used so that server state can be managed. This "state" includes server-side cursors, temporary products, connection-specific functional settings, and so on.

It is desirable to set some limit on the number of connections in the pool. Using too many connections may just cause thrashing rather than get more useful work done. In case an operation is attempted and all connections are in use, the operation can block until a connection is returned to the pool, or an error may be returned.