OLE DB, ODBC, and Oracle connection pooling
When you pool connections, you can significantly enhance the performance and scalability of your application. This article discusses connection pooling for the .NET Framework data providers for OLE DB, ODBC, and Oracle.
OleDb
The .NET Framework Data Provider for OLE DB automatically pools connections using OLE DB session pooling. Connection string arguments can be used to enable or disable OLE DB services including pooling. For example, the following connection string disables OLE DB session pooling and automatic transaction enlistment.
Provider=SQLOLEDB;OLE DB Services=-4;Data Source=localhost;Integrated Security=SSPI;
Important
Microsoft recommends that you use the most secure authentication flow available. If you're connecting to Azure SQL, Managed Identities for Azure resources is the recommended authentication method.
We recommend that you always close or dispose of a connection when you're finished using it in order to return the connection to the pool. Connections that aren't explicitly closed might not get returned to the pool. For example, a connection that has gone out of scope but that has not been explicitly closed will only be returned to the connection pool if the maximum pool size is reached and the connection is still valid.
For more information about OLE DB session or resource pooling, as well as how to disable pooling by overriding OLE DB provider service defaults, see the OLE DB Programmer's Guide.
ODBC
Connection pooling for the .NET Framework Data Provider for ODBC is managed by the ODBC Driver Manager that's used for the connection, and is not affected by the .NET Framework Data Provider for ODBC.
To enable or disable connection pooling, open ODBC Data Source Administrator in the Administrative Tools folder of Control Panel. The Connection Pooling tab allows you to specify connection pooling parameters for each ODBC driver installed. Connection pooling changes for a specific ODBC driver affect all applications that use that ODBC driver.
OracleClient
The .NET Framework Data Provider for Oracle provides connection pooling automatically for your ADO.NET client application. You can also supply several connection string modifiers to control connection pooling behavior (see "Controlling Connection Pooling with Connection String Keywords," later in this article).
Create and assign pools
When a connection is 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.
Once created, connection pools are not destroyed until the active process ends. Maintaining inactive or empty pools uses very few system resources.
Connection addition
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.
When an OracleConnection object is requested, it is obtained from the pool if a usable connection is available. To be usable, the connection must currently be unused, have a matching transaction context or not be associated with any transaction context, and have a valid link to the server.
If the maximum pool size has been reached and no usable connection is available, the request is queued. The connection pooler satisfies these requests by reallocating connections as they are released back into the pool. Connections are released back into the pool when they are closed or disposed.
Connection removal
The connection pooler removes a connection from the pool after it has been idle for an extended period of time or if the pooler detects that the connection with the server has been severed. This can be detected only after attempting to communicate with the server. If a connection is found that is no longer connected to the server, it is marked as invalid. The connection pooler periodically scans connection pools looking for objects that have been released to the pool and are marked as invalid. These connections are then permanently removed.
If a connection exists to a server that has disappeared, this connection can be drawn from the pool if the connection pooler has not detected the severed connection and marked it as invalid. When this occurs, an exception is generated. However, you must still close the connection in order to release it back into the pool.
Do not call Close or Dispose on a Connection, a DataReader, or any other managed object in the Finalize method of your class. In a finalizer, only release unmanaged resources that your class owns directly. If your class does not own any unmanaged resources, do not include a Finalize method in your class definition. For more information, see Garbage Collection.
Transaction support
Connections are drawn from the pool and assigned based on transaction context. The context of the requesting thread and the assigned connection must match. Therefore, each connection pool is subdivided into connections with no associated transaction context and into N subdivisions that each contain connections with a particular transaction context.
When a connection is closed, it is released back into the pool and into the appropriate subdivision based on its transaction context. Therefore, you can close the connection without generating an error, even though a distributed transaction is still pending. This allows you to commit or abort the distributed transaction at a later time.
Control connection pooling with connection string keywords
The ConnectionString property of the OracleConnection object supports connection string key/value pairs that can be used to adjust the behavior of the connection pooling logic.
The following table describes the ConnectionString values you can use to adjust connection pooling behavior.
| Name | Default | Description |
|---|---|---|
Connection Lifetime |
0 | When a connection is returned to the pool, its creation time is compared with the current time, and the connection is destroyed if that time span (in seconds) exceeds the value specified by Connection Lifetime. This is useful in clustered configurations to force load balancing between a running server and a server just brought online.A value of zero (0) will cause pooled connections to have the maximum time-out. |
Enlist |
'true' | When true, the pooler automatically enlists the connection in the current transaction context of the creation thread if a transaction context exists. |
Max Pool Size |
100 | The maximum number of connections allowed in the pool. |
Min Pool Size |
0 | The minimum number of connections maintained in the pool. |
Pooling |
'true' | When true, the connection is drawn from the appropriate pool, or if necessary, created and added to the appropriate pool. |