使用ADO.NET管理数据库连接 [英] Managing database connectivity with ADO.NET
问题描述
我们有一个建立在ADO.NET应用程序。我们遵循一些简单的最佳实践，使我们能够利用连接池。例如，正在使用的数据库可能会是这个样子code块：
使用（的DbConnection的DbConnection = GetDatabaseConnection（））{ 做工作（）; }
FWIW，有什么特别的GetDatabaseConnection。它建立与运行MSSQL Server 2000。在事实上与数据库的连接，其内容如下：
的DbConnection GetDatabaseConnection（）{ 返回的getConnection（MyConnectionString）; } 的DbConnection的getConnection（字符串的connectionString） { 尝试 { 的SqlConnection的DbConnection =新的SqlConnection（的connectionString）; dbConnection.Open（）; 返回的DbConnection; }赶上（InvalidOperationException异常前）{ handleError的（前）; 扔; }赶上（DbException前）{ handleError的（前）; } }
因此，我们之间的联系是在块作用域结束处理。但是，我们遇到了一点小故障，我们开始测试应用程序。我们发现，我们的应用程序是很突发性的含义，有些时候它变得非常健谈，然后进入沉默了一阵。其结果是，我们可以同时在所有有多个线程上来获得的连接。
所以，想象一下你有10个线程。一批工作（请不要尝试重组批处理工作）到达和跨几个线程分割。每个线程然后尝试获取连接和繁荣，我击中一个InvalidOperationException。我已经调整了ConnectTimeout和所有所做的是伸出的时间，直到我得到了一阵异常。一旦我得到过去的发条阶段，该应用程序是好的。然后再次静默，连接走开，而过程又重新开始。
我也试着调整了LoadBalanceTimeout但例外情况继续蜂拥而至。以前你们任何人看到这个问题？有什么想法......我会扔了几个我自己的。
- 在不断保持一定的联系热
- 在试图再次打开连接了一些＃的尝试
- 在实现自己的连接池（bleah，在重新发明轮子不感兴趣）
编辑：
我读过的大多数论坛已劝阻增加连接池的大小。默认情况下，连接池上限为50个连接（即足够多的 - 如果我要增加出口，有些东西是其他地方根本错误）。我注意到的是，当ConnectTimeout低，InvalidOperationException异常发生。这是因为如果自旋向上的连接过长而未决连接的所有超时。
火星肯定是一个选项...的InvalidOperationException.Message的文字是：
超时过期。超时时间已过之前从池中获取一个连接。出现这种情况可能是因为所有池连接使用，最大池大小共识。
从MSDN（的 http://msdn.microsoft.com/en-us/library/8xx3tyca.aspx ）：
当请求SqlConnection对象，它是从池中获得的，如果一个可用的网络连接。要使用，连接必须是未使用，有一个相匹配的事务上下文或者是无关联的任何事务上下文，然后必须在服务器的有效链接。
连接池程序符合通过重新分配连接，因为它们释放到池的连接请求。如果最大池大小已经达到和没有可用的连接可用时，请求排队。该缓冲池，然后尝试回收任何连接，直到超时为止（默认为15秒）。如果缓冲池无法满足连接超时的请求之前，抛出一个异常 的
翻译：查看您的交易环境......如果你有10个连接，和10个连接池的大小已经在不同的交易创建的，你拧
的请注意，切断连接，可以检测只能试图与服务器以沟通后。如果找到一个连接其不再连接到服务器，它被标记为无效。无效的连接从连接池中删除，只有当它们被关闭或回收。
如果连接存在已经消失了一台服务器，这方面可以从以下任意绘制，即使连接池并没有检测到被切断连接，然后将其标记为无效。是这样的话，因为检查的连接仍然有效将消除具有池进程通过使另一往返发生的服务器的益处的开销。如果发生这种情况，第一次尝试使用该连接将检测到连接已被切断，并抛出一个异常 的
翻译：：你真的不能依靠一个连接来进行连接？文章并没有真正解释如何处理这...
您可以手动尝试清除偶尔使用ClearAllPools和ClearPool，池但仍听起来像一个创可贴给我，让我畏缩。
本文还讨论了安全上下文，他说：
*在一个SQL Server应用程序角色已通过调用sp_setapprole存储过程的系统启动，该连接的安全上下文不能复位。然而，如果启用了连接池，连接返回到池中，并且当池的连接被重新使用时发生错误。*
我开始怀疑我为什么使用连接池...
最后：
池碎片由于集成安全性
连接根据连接字符串加上用户身份汇集。因此，如果您使用的网站和一个集成的安全登录基本身份验证或Windows身份验证，您将获得每个用户一个游泳池。尽管这提高了随后的数据库请求的单个用户的性能，则该用户不能采取其他用户制作的连接优势。这也导致在给数据库服务器每个用户的至少一个连接。的
所以，如果你使用一个Web应用程序集成的安全性，可以填满你的连接池，如果你有足够多的用户。
不知道更多的细节在你的应用程序，很难放大可能被绊倒你，但希望这给你一些想法去哪里找。
心连心
We have an application that is built upon ADO.NET. We follow some simple best practices that allow us to make use of the connection pool. For example, a block of code that is using a database might look something like this:
using( DbConnection dbConnection = GetDatabaseConnection() ) { doWork(); }
FWIW, there's nothing special about GetDatabaseConnection. It establishes a connection with the database which is running MSSQL Server 2000. In fact, it reads as follows:
DbConnection GetDatabaseConnection() { return GetConnection(MyConnectionString); } DbConnection GetConnection(String connectionString) { try { SqlConnection dbConnection = new SqlConnection(connectionString); dbConnection.Open(); return dbConnection; } catch( InvalidOperationException ex ) { handleError(ex); throw; } catch( DbException ex ) { handleError(ex); } }
As such, our connections are disposed of at the end of the block scope. However, we ran into a little glitch as we began to test the application. We found that our application is very bursty meaning there are times when it becomes very chatty and then goes silent for a while. The result is that we can have multiple threads simultaneously all coming up to obtain a connection.
So imagine you have 10 threads. A batch of work (please don't try to rephrase the batch of work) arrives and is segmented across a few threads. Each thread then attempts to obtain a connection and boom, I'm hit with an InvalidOperationException. I've adjusted the ConnectTimeout and all that does is stretch out the time until I get a burst of exceptions. Once I get past the "wind-up" phase, the application is fine. Then it quiesces again, the connections "go away" and the process begins again.
I've also tried tweaking the LoadBalanceTimeout but the exceptions continue to roll in. Have any of you seen this issue before? Any thoughts ... I'll throw out a couple of my own.
- Continually keep some connection "hot"
- Attempt to open the connection again up to some # of attempts
- Implement my own connection pooling (bleah, not interested in reinventing the wheel)
Edit:
Most forums I've read have discouraged increasing the connection pool size. By default the connection pool has an upper bound of 50 connections (that's more than enough -- if I have to increase it, something's fundamentally wrong elsewhere). What I notice is that when the ConnectTimeout is low, the InvalidOperationException occurs. It's as if the spin-up of the connection is too long and the pending connections all timeout.
MARS is certainly an option... The text of the InvalidOperationException.Message is:
Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool. This may have occurred because all pooled connections were in use and max pool size was reached.
From the MSDN (http://msdn.microsoft.com/en-us/library/8xx3tyca.aspx):
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.
Translation: Check your transaction contexts... if you have a pool size of 10 connections, and 10 connections have been created under different transactions, you're screwed.
Note that a severed connection 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. Invalid connections are removed from the connection pool only when they are closed or reclaimed.
If a connection exists to a server that has disappeared, this connection can be drawn from the pool even if the connection pooler has not detected the severed connection and marked it as invalid. This is the case because the overhead of checking that the connection is still valid would eliminate the benefits of having a pooler by causing another round trip to the server to occur. When this occurs, the first attempt to use the connection will detect that the connection has been severed, and an exception is thrown.
Translation: You can't really rely on a connection to be connected? The article doesn't really explain how to handle this...
You could try manually clearing the pool occasionally using ClearAllPools and ClearPool, but this still sounds like a band-aid to me, makes me cringe.
The article also discusses Security Contexts, saying:
*After a SQL Server application role has been activated by calling the sp_setapprole system stored procedure, the security context of that connection cannot be reset. However, if pooling is enabled, the connection is returned to the pool, and an error occurs when the pooled connection is reused.*
I'm starting to wonder why I use connection pooling...
And finally:
Pool Fragmentation Due to Integrated Security
Connections are pooled according to the connection string plus the user identity. Therefore, if you use Basic authentication or Windows Authentication on the Web site and an integrated security login, you get one pool per user. Although this improves the performance of subsequent database requests for a single user, that user cannot take advantage of connections made by other users. It also results in at least one connection per user to the database server.
So if you're using integrated security on a web app, you can fill up your connection pool if you have enough users.
Without knowing more specifics on your application, it's hard to zoom in on what might be tripping you up, but hopefully this gives you some ideas where to look.
HTH
这篇关于使用ADO.NET管理数据库连接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
