SQL Server 如何处理并发请求?

发布于 2024-08-19 19:37:52 字数 288 浏览 5 评论 0原文

如果对同一个大表的多个查询同时到达 SQL Server,那么 SQL Server 如何解决这种情况下的冲突?

当在 WPF 应用程序中使用 LINQ to SQL 查询安装在许多 PC 上,并与 SQL Server 数据库在网络中工作时,我是否应该以某种方式实现解决此类问题的机制,或者我是否可以通过 SQL Server 和 LINQ to SQL 来保护自己免受此类问题的影响?

(我不是指对同一条记录的并发操作,这是通过使用 TIMESTAMP 字段解决的。我的意思是当从多个网络站查询同一个大表时出现一些队列问题)

If multiple queries to one and the same large table come to SQL Server at the same time, how SQL Server resolves conflicts in this case?

When using LINQ to SQL queries in WPF application for install on many PC's, working in network with SQL Server database, should I somehow implement mechanism for resolving such issues or am I protected from issues of this kind by SQL Server and LINQ to SQL?

(I don't mean concurrent operations to the same record, which resolved by using TIMESTAMP field. I mean some queue issue, when the same large table is queried from multiple network stations)

如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(3

苏大泽ㄣ 2024-08-26 19:37:52

如果您的查询导致死锁,SQL Server 有一个内部机制来处理它。

如果它检测到死锁情况,它会选择回滚“受害者”事务。它选择受害者的方式首先基于事务的优先级设置,然后基于回滚每个查询的总成本。回滚成本较低的通常是受害者。

实际上,您可以使用SET DEADLOCK_PRIORITY来控制优先级(LOW、MEDIUM 或 HIGH,或 -10 到 10 之间的数字)。如果您发现自己处于这种情况,那么您确实应该首先努力减少僵局。

If your queries cause a deadlock, SQL Server has an internal mechanism to deal with it.

If it detects a deadlock situation, it choose a "victim" transaction to be rolled back. The way it chooses the victim is based first on the priority setting of the transaction, and then on the total cost of rolling back each query. The one that costs less to rollback is usually the victim.

You can actually use the SET DEADLOCK_PRIORITY to control the priority (LOW, MEDIUM or HIGH, or a number between -10 and 10). If you find you're in this situation though, you should really be putting your effort into reducing deadlocks in the first place.

审判长 2024-08-26 19:37:52

SQL Server 将管理每个连接的连接和查询执行,您将无法控制如何、何时、谁何时进行查询。

您可以通过确保拥有正确的索引来控制查询所需的时间,但这只是您所关心的。

SQL Server is going to manage the connections and the query execution for each of your connections, you are not going to have control of how, when, who gets to query when.

You can control the time the query takes by making sure that you have proper indexes, but that is as far as your concern will go.

鼻尖触碰 2024-08-26 19:37:52

数据库使用隔离级别来解决您所询问的问题。

当您需要隔离事务的资源并保护该资源免受其他事务的影响时,隔离级别就会发挥作用。保护是通过获取锁来完成的。事务需要设置什么锁以及如何建立锁,由 SQL Server 参考已设置的隔离级别来确定。较低的隔离级别允许多个用户同时访问资源(并发),但它们可能会引入与并发相关的问题,例如脏读和数据不准确。更高的隔离级别可以消除并发相关问题并提高数据准确性,但可能会引入阻塞。

有关 SQL Server 2005 隔离级别的详细信息,请阅读本文

您可以调整隔离级别,甚至可以针对每个查询进行调整。但真正会引起问题的是先进的原理。

Databases use isolation levels to resolve the issues you're asking about.

Isolation levels come into play when you need to isolate a resource for a transaction and protect that resource from other transactions. The protection is done by obtaining locks. What locks need to be set and how it has to be established for the transaction is determined by SQL Server referring to the Isolation Level that has been set. Lower Isolation Levels allow multiple users to access the resource simultaneously (concurrency) but they may introduce concurrency related problems such as dirty-reads and data inaccuracy. Higher Isolation Levels eliminate concurrency related problems and increase the data accuracy but they may introduce blocking.

For more info on SQL Server 2005 isolation levels, read this article.

You can tweak isolation levels, even on a per query basis. But it's advanced principle that can really cause problems.

~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文