如何在连接错误时清除 ODP.NET 连接池?
我使用 NHibernate 和 ODP.NET 连接到 Oracle 11g 数据库.当然，可能存在连接错误(网络故障、DB 宕机……).我在我的代码中处理所有这些异常，所以没问题.但是当然用户可以重试他的操作(也许只是短暂的网络故障)，这就是我的问题:
I'm using NHibernate and ODP.NET to connect to a Oracle 11g database. Of course there can be connection errors (network failure, DB down, ...). I'm handling all these exceptions in my code, so no problem there. But of course the user can retry his actions (maybe it was just a short network failure), and there comes my problem:
ODP.NET 默认使用连接池.通常没有问题，但是当用户在连接错误后重试操作时，NHibernate 会从 ODP.NET 获取无效(池化)连接.用户必须多次重试(直到池为空)才能使其再次工作.
ODP.NET is using connection pooling by default. No problem with that usually, but when the user retries an action after a connection error, NHibernate gets an invalid (pooled) connection from ODP.NET. The user has to retry it multiple times (until the pool is empty) to get it working again.
当然我可以在 ODP.NET 中禁用连接池，但我想避免这种情况.我还了解了一个设置，该设置会针对从池中返回的每个连接检查与数据库的连接，但这会为每个连接添加额外的往返行程，我也想避免这种情况.
Of course I can disable connection pooling in ODP.NET, but I'd like to avoid that. I've also read about a setting that checks the connection to the DB for each returned connection from the pool, but this adds an additional round trip to each connection which I'd like to avoid too.
有没有什么办法可以配置ODP.NET在任何连接抛出连接异常时自动清除连接池?
Is there any way to configure ODP.NET to automatically clear the connection pool when any connection throws an connection exception?
推荐答案
如果您可以使用 odac (odp) 11g，则您已为您的池设置验证连接.它可以在您使用之前验证连接.
If you can use odac (odp) 11g, you have setting Validate Connection for your pool. It can validate the connection before you use it.
验证连接 属性验证来自池的连接.仅在绝对必要时才应使用此属性，因为它会导致在将每个连接提供给应用程序之前立即进行到数据库的往返验证每个连接.如果无效连接不常见，开发人员可以创建自己的事件处理程序来检索和验证新连接，而不是使用 Validate Connection 属性.这通常会提供更好的性能.
The Validate Connection attribute validates connections coming out of the pool. This attribute should be used only when absolutely necessary, because it causes a round-trip to the database to validate each connection immediately before it is provided to the application. If invalid connections are uncommon, developers can create their own event handler to retrieve and validate a new connection, rather than using the Validate Connection attribute. This generally provides better performance.
如果还不够好 - 你可以试试 this 来自 oracle 的文档.
If it will not be good enough - you can try this document from oracle.
连接池管理
ODP.NET 连接池管理提供显式连接池控制 ODP.NET 应用程序.应用程序可以明确清除连接池中的连接.
ODP.NET connection pool management provides explicit connection pool control to ODP.NET applications. Applications can explicitly clear connections in a connection pool.
使用连接池管理，应用程序可以执行以下操作:
Using connection pool management, applications can do the following:
注意:.NET 存储过程不支持这些 API.清除使用 ClearPool 方法从连接池连接.
Note: These APIs are not supported in a .NET stored procedure. Clear connections from connection pools using the ClearPool method.
清除应用程序中所有连接池中的连接域，使用 ClearAllPools 方法.
Clear connections in all the connection pools in an application domain, using the ClearAllPools method.
当连接从池中清除时，ODP.NET 重新填充池使用至少设置了连接数的新连接通过连接字符串中的 Min Pool Size.新连接不必然意味着池将具有有效的连接.例如，如果当调用 ClearPool 或 ClearAllPools 时，数据库服务器关闭，ODP.NET 创建新连接，但这些连接仍然存在无效，因为它们无法连接到数据库，即使数据库稍后出现.
When connections are cleared from a pool, ODP.NET repopulates the pool with new connections that have at least the number of connections set by Min Pool Size in the connection string. New connections do not necessarily mean the pool will have valid connections. For example, if the database server is down when ClearPool or ClearAllPools is called, ODP.NET creates new connections, but these connections are still invalid because they cannot connect to the database, even if the database comes up a later time.
建议在调用之前不要调用 ClearPool 和 ClearAllPools应用程序可以创建回数据库的有效连接..NET 开发人员可以开发持续检查是否或可以创建无效的数据库连接并调用 ClearPool 或一旦这是真的，ClearAllPools.
It is recommended that ClearPool and ClearAllPools not be called until the application can create valid connections back to the database. .NET developers can develop code that continuously checks whether or not a valid database connection can be created and calls ClearPool or ClearAllPools once this is true.
另外，可能是这篇文章会对你有所帮助.
Also, may be this post will help you.
更新:正如@MPelletier 所指出的，对于 oracle 12 链接是不同的.
Update: As pointed by @MPelletier, for oracle 12 the link is different.
相关文章