Java 是否可以异步调用 jdbc？

Is asynchronous jdbc call possible?

提问by Steve Gury

I wonder if there is a way to make asynchronous calls to a database?

我想知道是否有办法对数据库进行异步调用？

For instance, imagine that I've a big request that take a very long time to process, I want to send the request and receive a notification when the request will return a value (by passing a Listener/callback or something). I don't want to block waiting for the database to answer.

例如，假设我有一个需要很长时间来处理的大请求，我想发送请求并在请求返回值时接收通知（通过传递侦听器/回调或其他东西）。我不想阻塞等待数据库回答。

I don't consider that using a pool of threads is a solution because it doesn't scale, in the case of heavy concurrent requests this will spawn a very large number of threads.

我不认为使用线程池是一种解决方案，因为它不能扩展，在大量并发请求的情况下，这将产生大量线程。

We are facing this kind of problem with network servers and we have found solutions by using select/poll/epoll system call to avoid having one thread per connection. I'm just wondering how to have a similar feature with database request?

我们正面临网络服务器的这种问题，我们通过使用 select/poll/epoll 系统调用找到了解决方案，以避免每个连接有一个线程。我只是想知道如何在数据库请求中使用类似的功能？

Note: I'm aware that using a FixedThreadPool may be a good work-around, but I'm surprised that nobody has developed a system really asynchronous (without the usage of extra thread).

注意：我知道使用 FixedThreadPool 可能是一个很好的解决方法，但我很惊讶没有人开发出真正异步的系统（不使用额外的线程）。

** Update **
Because of the lack of real practical solutions, I decided to create a library (part of finagle) myself: finagle-mysql. It basically decodes/decodes mysql request/response, and use Finagle/Netty under the hood. It scales extremely well even with huge number of connections.

** 更新**
由于缺乏真正实用的解决方案，我决定自己创建一个库（finagle 的一部分）：finagle-mysql。它基本上解码/解码 mysql 请求/响应，并在后台使用 Finagle/Netty。即使有大量连接，它也能很好地扩展。

采纳答案by johnlon

I don't understand how any of the proposed approaches that wrap JDBC calls in Actors, executors or anything else can help here - can someone clarify.

我不明白将 JDBC 调用包装在 Actor、执行器或其他任何东西中的任何建议方法如何在这里提供帮助 - 有人可以澄清一下。

Surely the basic problem is that the JDBC operations block on socket IO. When it does this it blocks the Thread its running on - end of story. Whatever wrapping framework you choose to use its going to end up with one thread being kept busy/blocked per concurrent request.

当然，基本问题是 JDBC 操作阻塞了套接字 IO。当它这样做时，它会阻止它运行的线程 - 故事的结尾。无论您选择使用哪种包装框架，最终都会使每个并发请求保持忙碌/阻塞一个线程。

If the underlying database drivers (MySql?) offers a means to intercept the socket creation (see SocketFactory) then I imagine it would be possible to build an async event driven database layer on top of the JDBC api but we'd have to encapsulate the whole JDBC behind an event driven facade, and that facade wouldn't look like JDBC (after it would be event driven). The database processing would happen async on a different thread to the caller, and you'd have to work out how to build a transaction manager that doesn't rely on thread affinity.

如果底层数据库驱动程序（MySql？）提供了一种拦截套接字创建的方法（参见 SocketFactory），那么我想有可能在 JDBC api 之上构建一个异步事件驱动的数据库层，但我们必须封装整个 JDBC 位于事件驱动的 Facade 后面，并且该 Facade 看起来不像 JDBC（在它是事件驱动的之后）。数据库处理将在与调用者不同的线程上异步发生，您必须弄清楚如何构建不依赖于线程关联的事务管理器。

Something like the approach I mention would allow even a single background thread to process a load of concurrent JDBC exec's. In practice you'd probably run a pool of threads to make use of multiple cores.

类似于我提到的方法甚至允许单个后台线程处理并发 JDBC exec 的负载。在实践中，您可能会运行一个线程池来使用多个内核。

(Of course I'm not commenting on the logic of the original question just the responses that imply that concurrency in a scenario with blocking socket IO is possible without the user of a selector pattern - simpler just to work out your typical JDBC concurrency and put in a connection pool of the right size).

（当然，我不是在评论原始问题的逻辑，只是在没有选择器模式的用户的情况下，暗示在阻塞套接字 IO 的场景中并发是可能的——更简单的只是计算出典型的 JDBC 并发并把在大小合适的连接池中）。

---

---

Looks like MySql probably does something along the lines I'm suggesting --- http://code.google.com/p/async-mysql-connector/wiki/UsageExample

看起来 MySql 可能按照我建议的方式做了一些事情 --- http://code.google.com/p/async-mysql-connector/wiki/UsageExample

回答by Aravind Yarram

There is no direct support in JDBC but you have multiple options like MDB, Executors from Java 5.

JDBC 中没有直接支持，但您有多种选择，例如 MDB、Java 5 的 Executors。

"I don't consider that using a pool of threads is a solution because it doesn't scale, in the case of heavy concurrent requests this will spawn a very large number of threads."

“我不认为使用线程池是一种解决方案，因为它不能扩展，在大量并发请求的情况下，这将产生大量线程。”

I am curious why would a bounded pool of threads is not going to scale? It is a pool not thread-per-request to spawn a thread per each request. I have been using this for quite sometime on a heavy load webapp and we have not seen any issues so far.

我很好奇为什么有界线程池不会扩展？它是一个池而不是每个请求的线程来为每个请求生成一个线程。我已经在重负载 web 应用程序上使用了很长一段时间，到目前为止我们还没有看到任何问题。

回答by Amir Raminfar

I am just thinking ideas here. Why couldn't you have a pool of database connections with each one having a thread. Each thread has access to a queue. When you want to do a query that takes a long time, you can put on the queue and then one of threads will pick it up and handle it. You will never have too many threads because the number of your threads are bounded.

我只是在这里思考想法。为什么不能有一个数据库连接池，每个连接池都有一个线程。每个线程都可以访问一个队列。当你想做一个需要很长时间的查询时，你可以把它放在队列中，然后一个线程将它捡起来并处理它。您永远不会有太多线程，因为您的线程数是有限的。

Edit: Or better yet, just a number of threads. When a thread sees something in a queue, it asks for a connection from the pool and handles it.

编辑：或者更好，只是一些线程。当线程看到队列中的某些内容时，它会从池中请求连接并处理它。

回答by Tomas Narros

Perhaps you could use a JMS asynchronous messaging system, which scales pretty well, IMHO:

也许你可以使用一个 JMS 异步消息系统，它的伸缩性很好，恕我直言：

• Send a message to a Queue, where the subscribers will accept the message, and run the SQL process. Your main process will continue running and accepting or sending new requests.

• When the SQL process ends, you can run the opposite way: send a message to a ResponseQueue with the result of the process, and a listener on the client side accept it and execute the callback code.

• 向队列发送消息，订阅者将在队列中接受消息并运行 SQL 进程。您的主进程将继续运行并接受或发送新请求。

• 当SQL进程结束时，你可以反其道而行之：将进程的结果发送到ResponseQueue，客户端的监听器接受并执行回调代码。

回答by Vasil Remeniuk

It's impossible to make an asynchronous call to the databasevia JDBC, but you can make asynchronous calls to JDBCwith Actors(e.g., actor makes calls to the DB via JDBC, and sends messages to the third parties, when the calls are over), or, if you like CPS, with pipelined futures (promises)(a good implementation is ScalazPromises)

通过JDBC对数据库进行异步调用是不可能的，但是您可以使用Actors对JDBC进行异步调用（例如，actor通过JDBC对数据库进行调用，并在调用结束后将消息发送给第三方），或者，如果你喜欢 CPS，使用流水线期货（promises）（一个很好的实现是Scalaz Promises）

I don't consider that using a pool of threads is a solution because it doesn't scale, in the case of heavy concurrent requests this will spawn a very large number of threads.

我不认为使用线程池是一种解决方案，因为它不能扩展，在大量并发请求的情况下，这将产生大量线程。

Scala actors by default are event-based (not thread-based) - continuation scheduling allows creating millions of actors on a standard JVM setup.

默认情况下，Scala actor 是基于事件的（而不是基于线程的）——持续调度允许在标准的 JVM 设置上创建数百万个 actor。

If you're targeting Java, Akka Frameworkis an Actor model implementation that has a good API both for Java and Scala.

如果您的目标是 Java，Akka 框架是一个 Actor 模型实现，它具有适用于 Java 和 Scala 的良好 API。

---

---

Aside from that, the synchronous nature of JDBC makes perfect sense to me. The cost of a database session is far higher than the cost of the Java thread being blocked (either in the fore- or background) and waiting for a response. If your queries run for so long that the capabilities of an executor service (or wrapping Actor/fork-join/promise concurrency frameworks) are not enough for you (and you're consuming too many threads) you should first of all think about your database load. Normally the response from a database comes back very fast, and an executor service backed with a fixed thread pool is a good enough solution. If you have too many long-running queries, you should consider upfront (pre-)processing - like nightly recalculation of the data or something like that.

除此之外，JDBC 的同步特性对我来说非常有意义。数据库会话的成本远高于 Java 线程被阻塞（在前台或后台）和等待响应的成本。如果您的查询运行时间太长以至于执行程序服务（或包装 Actor/fork-join/promise 并发框架）的功能对您来说还不够（并且您消耗了太多线程），您应该首先考虑您的数据库负载。通常，来自数据库的响应很快就会返回，并且由固定线程池支持的执行程序服务是一个足够好的解决方案。如果您有太多长时间运行的查询，您应该考虑预先（预）处理 - 例如每晚重新计算数据或类似的事情。

回答by Bozho

The Java 5.0 executorsmight come handy.

在Java 5.0中执行人可能来得心应手。

You can have a fixed number of threads to handle long-running operations. And instead of Runnableyou can use Callable, which return a result. The result is encapsulated in a Future<ReturnType>object, so you can get it when it is back.

您可以拥有固定数量的线程来处理长时间运行的操作。而不是Runnable你可以使用Callable，它返回一个结果。结果被封装在一个Future<ReturnType>对象中，所以当它返回时你可以得到它。

回答by jwinandy

Just a crazy idea : you could use an Iteratee pattern over JBDC resultSet wrapped in some Future/Promise

只是一个疯狂的想法：您可以在 JBDC resultSet 上使用 Iteratee 模式，该结果集包含在一些 Future/Promise 中

Hammersmith does that for MongoDB.

Hammersmith 为MongoDB做到了这一点。

回答by William Speirs

The commons-dbutils library has support for an AsyncQueryRunnerwhich you provide an ExecutorServiceto and it returns a Future. Worth checking out as it's simple to use and ensure you won't leak resources.

commons-dbutils 库支持AsyncQueryRunner您提供一个ExecutorServiceto 并返回一个Future. 值得一试，因为它易于使用并确保您不会泄漏资源。

回答by Sebastien

Ajdbc project seems to answer this problem http://code.google.com/p/adbcj/

Ajdbc 项目似乎可以回答这个问题http://code.google.com/p/adbcj/

There is currently 2 experimental natively async drivers for mysql and postgresql.

目前有 2 个用于 mysql 和 postgresql 的实验性本机异步驱动程序。

回答by Erik Brandsberg

An old question, but some more information. It is not possible to have JDBC issue asynchronous requests to the database itself, unless a vendor provides an extension to JDBC and a wrapper to handle JDBC with. That said, it is possible to wrap JDBC itself with a processing queue, and to implement logic that can process off the queue on one or more separate connections. One advantage of this for some types of calls is that the logic, if under heavy enough load, could convert the calls into JDBC batches for processing, which can speed up the logic significantly. This is most useful for calls where data is being inserted, and the actual result need only be logged if there is an error. A great example of this is if inserts are being performed to log user activity. The application won't care if the call completes immediately or a few seconds from now.

一个老问题，但更多信息。除非供应商提供对 JDBC 的扩展和用于处理 JDBC 的包装器，否则不可能让 JDBC 向数据库本身发出异步请求。也就是说，可以用处理队列包装 JDBC 本身，并实现可以在一个或多个单独连接上处理队列之外的逻辑。对于某些类型的调用，这样做的一个优点是，如果在足够大的负载下，逻辑可以将调用转换为 JDBC 批处理以进行处理，这可以显着加快逻辑速度。这对于插入数据的调用最有用，并且只有在出现错误时才需要记录实际结果。一个很好的例子是执行插入以记录用户活动。应用程序不会

As a side note, one product on the market provides a policy driven approach to allowing asynchronous calls like those I described to be made asynchronously (http://www.heimdalldata.com/). Disclaimer: I am co-founder of this company. It allows regular expressions to be applied to data transformation requests such as insert/update/deletes for any JDBC data source, and will automatically batch them together for processing. When used with MySQL and the rewriteBatchedStatements option (MySQL and JDBC with rewriteBatchedStatements=true) this can significantly lower overall load on the database.

附带说明一下，市场上的一个产品提供了一种策略驱动的方法来允许异步调用，如我所描述的异步调用 ( http://www.heimdalldata.com/)。免责声明：我是这家公司的联合创始人。它允许将正则表达式应用于任何 JDBC 数据源的插入/更新/删除等数据转换请求，并自动将它们批处理在一起进行处理。当与 MySQL 和 rewriteBatchedStatements 选项（带有 rewriteBatchedStatements=true 的 MySQL 和 JDBC）一起使用时，这可以显着降低数据库的整体负载。