C# 在执行长时间运行的数据库任务时处理 Web 服务超时

Handling Web Service Timeouts While Performing Long-Running Database Tasks

提问by Paul Williams

The architecture of one of our products is a typical 3-tier solution:

我们其中一款产品的架构是典型的 3 层解决方案：

• C# client
• WCF web service
• SQL Server database

• C#客户端
• WCF 网络服务
• SQL Server 数据库

The client requests information from the web service. The web service hits the database for the information and returns it to the client.

客户端从 Web 服务请求信息。Web 服务访问数据库以获取信息并将其返回给客户端。

Here's the problem. Some of these queries can take a long, long time, and we don't know up-front which ones will be slow. We know some that are often slower than others, but even the simplest requests can be slow given enough data. Sometimes uses query or run reports on large amounts of data. The queries can be optimized only so far before the sheer volume of data slows them down.

这就是问题所在。其中一些查询可能需要很长时间，而且我们不知道哪些查询会很慢。我们知道有些请求通常比其他请求慢，但是如果有足够的数据，即使是最简单的请求也会很慢。有时对大量数据使用查询或运行报告。查询只能在庞大的数据量减慢它们的速度之前进行优化。

If a query in the database hits the maximum query timeout in SQL server, the database query terminates, and the web service returns an error to the client. This is understood. We can handle these errors.

如果数据库中的查询达到 SQL Server 中的最大查询超时，则数据库查询终止，Web 服务向客户端返回错误。这是明白的。我们可以处理这些错误。

The client is waiting for the web service call to complete. If the database call takes a long time, the client may timeout on its call to the web service. The client gives up, but the database request continues processing. At this point, the client is out-of-synch with the database. The database call may or may not succeed. There may have been an error. The client will never know. In some cases, we don't want our users initiating another request that may result in an invalid state given the completion of the previous request.

客户端正在等待 Web 服务调用完成。如果数据库调用需要很长时间，客户端可能会在调用 Web 服务时超时。客户端放弃，但数据库请求继续处理。此时，客户端与数据库不同步。数据库调用可能成功，也可能不成功。可能有错误。客户永远不会知道。在某些情况下，我们不希望我们的用户发起另一个请求，这可能会在前一个请求完成的情况下导致无效状态。

I'm curious to see how others have handled this problem. What strategies have you used to prevent web service timeouts from affecting database calls?

我很想知道其他人是如何处理这个问题的。 您使用了哪些策略来防止 Web 服务超时影响数据库调用？

The best ideas I can come up with involve making an actual database layer somewhere-- inside the web service, attached to a message queue-- something. Offloading every single query to another process seems excessive. (Then again, we don't always know if a given request will be fast or slow.)

我能想到的最好的想法是在某处创建一个实际的数据库层——在 Web 服务内部，附加到消息队列——某物。将每个查询卸载到另一个进程似乎过度。（再说一次，我们并不总是知道给定的请求是快还是慢。）

It would be great if we could separate the act of making an HTTP request from the act of initiating and running a database process. I've seen this done with a custom server at a previous company, but it was using straight socket communication, and I'd rather avoid replacing the web service with some custom application.

如果我们能够将发出 HTTP 请求的行为与启动和运行数据库进程的行为分开，那就太好了。我在以前的公司看到过使用自定义服务器完成此操作，但它使用的是直接套接字通信，我宁愿避免用某些自定义应用程序替换 Web 服务。

Note that given the amount of data we deal with, we are all over query optimization. Query optimization, indexes, etc., only takes you so far when the volume of data is high. Sometimes things just take a long time.

请注意，鉴于我们处理的数据量，我们已经完成了查询优化。查询优化、索引等，只在数据量大的时候带你走这么远。有时事情只是需要很长时间。

采纳答案by Paul Williams

One of the solutions we've used lately is to break apart huge database processes into separate parallel operations. Each operation is much smaller and designed to be as efficient as possible. The clients initiate the operations, spawn a few threads, and do whatever they can in parallel.

我们最近使用的解决方案之一是将庞大的数据库进程分解为单独的并行操作。每个操作都小得多，并且设计得尽可能高效。客户端启动操作，产生几个线程，并并行做任何他们能做的事情。

For example, we've broken apart some huge proceses into a series of steps like Start, Process 1 Chunk of Work, Finish, and Gather Report Data. The Process Work steps can run in parallel, but they can't start until the Start step completes. The Finish step needs to wait for all Process Work steps to complete.

例如，我们将一些庞大的流程分解为一系列步骤，例如开始、处理 1 个工作块、完成和收集报告数据。Process Work 步骤可以并行运行，但在 Start 步骤完成之前它们无法启动。Finish 步骤需要等待所有 Process Work 步骤完成。

Since the client is controlling the process, the client can report progress on exactly which step it is on.

由于客户正在控制流程，因此客户可以准确报告其所在步骤的进度。

回答by Serguei

The web service could run the queries in a threadpool and if the thread does not finish within, say 5 seconds (see Thread.Join()), the web service call returns the client a JobID instead of the result set which the client can then use to poll the server every few seconds to see if its query finished. When a thread finishes the results can be stored in a hash table until the client polls again.

Web 服务可以在线程池中运行查询，如果线程没有在 5 秒内完成，例如 5 秒（参见 Thread.Join()），Web 服务调用会向客户端返回一个 JobID，而不是客户端可以使用的结果集使用每隔几秒钟轮询服务器以查看其查询是否完成。当线程完成时，结果可以存储在哈希表中，直到客户端再次轮询。

回答by Bravax

I've encountered similiar problems in the past, and used one of the following 3 methods to resolve it:

我过去遇到过类似的问题，并使用以下3种方法之一来解决它：

1. Add all long running queries to a queue, and process these sequentially.
   In my case these were all complicated reports which where then emailed to the client, or which were stored in permanent 'temporary' tables, for viewing by clients after they had been notified.
2. We called a webservice using a JQuery call, which then called a javascript postback method when it was complete.
   This worked well when we didn't want to make the page load synchronise with what the web service was doing.
   However it did mean that that piece of functionality was not available until the long running process was complete.
3. The most complicated one.
   We popped up another window which displayed a progress bar, which also polled the server periodically.
   This used a session variable to determine how far along to show the progress bar.
   After the progress bar was initiated, a new thread was started which updated the same session variable periodically.
   Once the session variable value was set to 100, the popup window closed itself.
   The clients loved this method.

1. 将所有长时间运行的查询添加到队列中，并按顺序处理这些查询。
   就我而言，这些都是复杂的报告，然后通过电子邮件发送给客户，或者存储在永久的“临时”表中，供客户在收到通知后查看。
2. 我们使用 JQuery 调用调用 Web 服务，然后在完成时调用 javascript 回发方法。
   当我们不想让页面加载与 Web 服务正在做的事情同步时，这很有效。
   然而，这确实意味着在长时间运行的过程完成之前，该功能不可用。
3. 最复杂的一种。
   我们弹出了另一个显示进度条的窗口，它也定期轮询服务器。
   这使用会话变量来确定显示进度条的距离。
   进度条启动后，会启动一个新线程，定期更新相同的会话变量。
   一旦会话变量值设置为 100，弹出窗口就会自行关闭。
   客户喜欢这种方法。

Anyway I hope one of those is of some help to you.

无论如何，我希望其中之一对您有所帮助。

回答by daragua

Breaking the problem to small chunks is certainly a good idea.

将问题分解成小块当然是个好主意。

In addition to that and what others said (and only if you have the hand on the webservice's implementation) I've been using callback urls passed to the webservice. The WS has to call it with errors or results in the query string or post data.

除此之外以及其他人所说的（并且仅当您掌握了 web 服务的实现时）我一直在使用传递给 web 服务的回调 url。WS 必须在查询字符串或发布数据中出现错误或结果时调用它。

The urls typically contain a token which serves to allow the callback to re-enter the client, and which maps to any relevant information needed to do actions after the callback is received (stored in a database or in memory).

url 通常包含一个令牌，用于允许回调重新进入客户端，并映射到接收回调后执行操作所需的任何相关信息（存储在数据库或内存中）。

It's a bit heavy weight (specially if you aren't running in webserver already) but guarantees successful round trip in the case the client times out but the webservice properly received the instruction and is simply slow in processing.

它有点重（特别是如果您还没有在网络服务器中运行），但可以保证在客户端超时但网络服务正确接收指令并且处理速度缓慢的情况下成功往返。

Once this is setup, your webservice is actually a bit closer to being ready to run asynchronously and thus answering quickly to the client : usually, do any checks to answer if its okay or not, and spawn the slow operation on a separate loop, with the callback url so that it can report back to the client.

设置完成后，您的 Web 服务实际上更接近准备好异步运行，从而快速响应客户端：通常，执行任何检查以回答是否正常，并在单独的循环中生成慢速操作，使用回调 url，以便它可以向客户端报告。

I'm not sure how orthodox this is, BTW, but it did solve actual issues.

我不确定这是多么正统，顺便说一句，但它确实解决了实际问题。