Azure数据库的并发使用问题 [英] Azure database concurrent usage issues
问题描述
只是想通过你们都跑这看看有没有因为我已经搜索之后的整个白天，夜晚和清晨用尽了所有我的想法什么好主意。我们总是遇到这些问题围绕数据库连接中心根据并发使用（Selenium测试），例如，当超时，掉线/关闭的连接，数据库服务器无法访问。
Just wanted to run this by you all to see if there are any bright ideas as I have exhausted all of my ideas after an entire day, night and morning of searching. The issues we’re encountering invariably centre around database connectivity when under concurrent usage (selenium test), e.g. timeouts, dropped/closed connections, database server unreachable.
问题似乎只限于作为天青我们还运行在同一code指向同一个Selenium测试到同一个数据库（SQL Azure中）时遇到问题，甚至本地所以它会指向它是某些问题在SQL Azure中的出站数据库连接。到目前为止，我们已经尝试了以下内容：
The issue does seem to be restricted to Azure as we’re yet to encounter the issue locally even when running the same selenium test on the same code pointing to the same database (SQL Azure) so it would point to it being some issue with outbound database connectivity in SQL Azure. So far we’ve tried the following:
- Azure的瞬态故障处理 - 我们有重试逻辑到位
当有与SQL Azure的服务本身就是一个暂时性的问题。 - 更改通信协议 - 我们已经尝试TCP和命名管道
我们遇到了同样的问题与两个。 - 调整数据库连接超时时间间隔 - 我们试图增加
这无济于事。 - 多活动结果集的加法 - 我们已经将其添加到
连接字符串无济于事。 - 连接状态的检查 - 当我们返回
我们的DataContext检查其连接并重新打开必要。 - 关机状态下连接池 - 我们也试图这样不
成功。 -
改变设计模式 - 我们甚至去实施的长度
工作设计模式的一个单位，那里的数据库连接是
被解雇和处置工作的每一个单位，但在此之后
导致的问题在其他地方与延迟加载，传递对象成
方法，它将会在这个太实质性的返工
点。
对每个查询
- Azure transient fault handling – We have retry logic in place for when there is a temporary issue with the SQL Azure service itself.
- Change communication protocol – We’ve tried both TCP and Named Pipes and we encounter the same issue with both.
- Adjust database connection timeout interval – We’ve tried increasing this to no avail.
- Addition of Multiple Active Result Sets – We’ve add this to the connection string to no avail.
- Connection state check on every query – When we return the DataContext we check for its connection and reopen where necessary.
- Turned off connection pooling - We’ve also attempted this without success.
Changed design pattern – We even went to the lengths of implementing a Unit of Work design pattern, where the database connections were being fired up and disposed of after every unit of work but this caused issues elsewhere with lazy loading, passing objects into methods and it would have been too substantial a rework at this point.
变化中的作用大小 - 我能想到尝试的最后一件事是要了
在Windows中的任何隐含的连接限制情况下的作用大小
Azure的 - 这是目前正在部署所以还是有一半的机会，它
可能工作！
Change role size – The last thing I can think to try is to up the role size in case of any implicit connection restrictions in Windows Azure – that’s currently deploying so there’s still half a chance it might work!
站点基础设施是如下：
- DataContext类（扩展的DbContext），这是一个code首先EF
上下文。 - BusinessLayer类包含一个私人的，非静态的DataContext。
DataContext的构造被注入到每一个经理/ Helper类。 - BusinessLayerService类包含一个公共的，静态的线程
BusinessLayer实例。 - MVC站点使用BusinessLayerService.Instance访问管理器
该班的查询和更新，他们已经通过了DataContext的。
任何帮助将大大AP preciated。
Any help would be greatly appreciated.
更新：：我们调升VM大小中型和它所作的只是意味着同样的问题需要较长的时间发生
UPDATE: We upped the VM size to Medium and all it did was mean that the same issue took longer to occur.
在问题开始发生的历史，团队成员注意到以下出现异常：
When the issues started occuring, a team member noted the following exception occured:
InvalidOperationException异常：该命令的执行需要一个开放和可用的连接。该连接的当前状态被打破了。
InvalidOperationException: Execution of the command requires an open and available connection. The connection's current state is broken.
这开始存在的，只要数据库被击中（是不是特定于code一定面积）。
This started occuring whenever the database was being hit (wasn't specific to a certain area of code).
推荐答案
我以前遇到过这种问题。在我的情况下，它是与实体框架ObjectContexts没有被妥善处理，最终才太多的背景下进行纺丝和现场摔倒了的事情。我们确定使用实体框架事件探查器，有未闭合ObjectContexts了很多正在引发的错误时。
I've encountered this kind of issue before. In my case it was to do with Entity Framework ObjectContexts not being disposed of properly, before eventually too many contexts were spun up and the site toppled over. We identified using the Entity Framework Profiler that there were a LOT of unclosed ObjectContexts when errors were being thrown.
这是不可行的我们，因为这将需要业务层的改写移动到工作设计模式（或类似）的单位，因此，我们通过每一个页面请求后手动关闭ObjectContexts解决它。我们通过在Global.asax中的结束请求事件处置手动上下文的方式，但是其他有效的方法将是上下文存储在HttpContext的或实现IoC容器（如温莎城堡）每个请求与生活方式。
It wasn't feasible for us to move to a Unit of Work design pattern (or similar) as it would have required a rewrite of the business layer, therefore, we resolved it by closing the ObjectContexts manually after every page request. We adopted the approach of disposing the context manually in the End Request event of Global.asax, however, other valid approaches would have been to store the context in the HttpContext or implement an IoC container (such as Castle Windsor) with a "per request" lifestyle.
这篇关于Azure数据库的并发使用问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
