database 始终保持数据库连接打开可以吗？

Is it okay to always leave a database connection open?

提问by Andrew Arnold

I'm working on a single-user desktop database application sort of thing in my spare time, and I'm always unsure about the design choices I'm making. Right now, as it stands, whenever the user wants to interact with the database (which is a local SQLite database, so generally only one user ever sees it at once), the application creates a new connection, does whatever it needs to do, and then closes the connection. Thereforee, over the course of one execution of the application, lots of connections are created and disposed of.

我在业余时间从事单用户桌面数据库应用程序之类的工作，我总是不确定我正在做的设计选择。现在，就目前而言，每当用户想要与数据库（这是一个本地 SQLite 数据库，因此通常只有一个用户一次看到它）进行交互时，应用程序会创建一个新连接，执行它需要执行的任何操作，然后关闭连接。因此，在应用程序的一次执行过程中，会创建和处理大量连接。

Is this generally the "best" way to go about it, or should the application open the connection at startup and only close it when the application exits? What are the advantages/disadvantages of each method?

这通常是解决它的“最佳”方式，还是应用程序应该在启动时打开连接并仅在应用程序退出时关闭它？每种方法的优点/缺点是什么？

采纳答案by FrustratedWithFormsDesigner

I would say it's fine in this case, since there will only ever be one user and the database is hosted on the same machine (more likely in the same memory space, as I think SQLite just loads as a DLL with the main application) as the application. Constantly opening and closing the connection is unnecessary.

我会说在这种情况下很好，因为永远只有一个用户并且数据库托管在同一台机器上（更有可能在同一内存空间中，因为我认为 SQLite 只是作为主应用程序的 DLL 加载）作为应用程序。不断地打开和关闭连接是不必要的。

One possible exception might be if you need to have multiple threads of your application accessing the database at the same time. Then you could either force them to wait and share a single connection object, ORyou could try to create new connections for the different threads. I have never actually tried this in SQLite. This is one situation where closing the main connection and opening/closing multiple connections might be better for a desktop app.

一种可能的例外情况可能是您需要让应用程序的多个线程同时访问数据库。然后您可以强制它们等待并共享单个连接对象，或者您可以尝试为不同的线程创建新连接。我从来没有在 SQLite 中真正尝试过这个。这是关闭主连接和打开/关闭多个连接可能更适合桌面应用程序的一种情况。

For web applications, or client/server desktop apps, I'd suggest against leaving connections open.

对于 Web 应用程序或客户端/服务器桌面应用程序，我建议不要让连接保持打开状态。

回答by Aaron McIver

Typically the connection is closed after use; freeing it back into the pool of available connections. If there are a high number of transactions taking place on a single client it makes sense to leverage a single connection instead of creating multiple connections only to immediately close them.

通常连接在使用后关闭；将其释放回可用连接池。如果在单个客户端上发生大量事务，那么利用单个连接而不是创建多个连接只是为了立即关闭它们是有意义的。

It is somewhat circumstantial however the typical best practice is to close it after use so that it becomes available within the pool again.

这在某种程度上是偶然的，但是典型的最佳做法是在使用后关闭它，以便它再次在池中可用。

回答by Imran Omar Bukhsh

Imagine you have 1000 users accessing your application at the same time. That would means 1000 open connections. Eventually you could run out of connections. So let each user open a connection, use it, and then close it so that connection is free for others to use.

假设您有 1000 个用户同时访问您的应用程序。这意味着 1000 个打开的连接。最终，您可能会耗尽连接。所以让每个用户打开一个连接，使用它，然后关闭它，这样连接就可以免费供其他人使用。

Further Clarification

进一步澄清

Imagine him having multiple modules that would simultaneous need the same connection? Image simultaneously running controls needing the connection. What is he going to do? Have a global connection object? Use a Singleton Pattern? Tell me if I am wrong

想象一下他有多个模块同时需要相同的连接吗？图像同时运行需要连接的控件。他要做什么？有一个全局连接对象？使用单例模式？告诉我如果我错了

回答by quentin-starin

Connection pooling shouldmake this a moot point. The pool should keep a connection around and open for you to reuse. This should allow you to follow the generally best practice of using resources for the shortest reasonable amount but without sacrificing performance.

连接池应该使这成为一个有争议的问题。池应该保持连接并打开供您重用。这应该允许您遵循以最短的合理数量使用资源但不牺牲性能的一般最佳实践。

回答by Jonathan Wood

I just started asking myself this same question. Like you, it's just a single app and I'm not worried about running out of connections.

我刚开始问自己同样的问题。像您一样，它只是一个应用程序，我不担心连接用完。

I did however run into a potential issue with leaving the database open though: transactions. If you do a BEGIN TRANSACTIONand there's an error or something else happens where your code doesn't hit a COMMITor ROLLBACK, then you leave that transaction open.

然而，我确实遇到了一个潜在的问题，即让数据库保持打开状态：事务。如果您执行 aBEGIN TRANSACTION并且在您的代码未命中 aCOMMIT或 的情况下发生错误或其他情况ROLLBACK，则您将该事务保持打开状态。

So it might be better to close it solely for the purpose of ensuring everything is restored after work is done to the database.

因此，最好仅出于确保在对数据库完成工作后恢复所有内容的目的而关闭它。

I really don't know how much overhead there is in opening and closing the database though. I'd love to hear some other people's thoughts on this.

我真的不知道打开和关闭数据库有多少开销。我很想听听其他人对此的看法。

回答by guiman

When dealing with database connection my policy is that in depends on what you need to do. For example, if your are loading a lot of realted critical data my advice would be to encapsulate all your operations in one transaction and with one conection that should be close after the transaction is commited.

在处理数据库连接时，我的政策是取决于您需要做什么。例如，如果您正在加载大量相关的关键数据，我的建议是将您的所有操作封装在一个事务中，并且在提交事务后应关闭一个连接。

Now, if you need to do lots of queries retrieving data, opening and closing a connection for each query could be quite expensive so, leaving a connection open is worthwhile.

现在，如果您需要执行大量查询来检索数据，则为每个查询打开和关闭连接可能会非常昂贵，因此，保持连接打开是值得的。

回答by Mahfuzur Rahman

Opening new connection is expensive. Therefore, the best way to use a connection is to mark it as idle after finishing a transaction. The connection will then be returned to a pool of idle connections.

打开新连接很昂贵。因此，使用连接的最佳方式是在完成事务后将其标记为空闲。然后连接将返回到空闲连接池。

When application requests a connection, the code must check the pool of idle {free} connection and return one. If there is no idle connection in the pool then a new connection should be used. When a connection is provided to a service or to an entity, the connection must be marked 'active'; so that the connection is not provided to any subsequent request.

当应用程序请求连接时，代码必须检查空闲 {free} 连接池并返回一个。如果池中没有空闲连接，则应使用新连接。当向服务或实体提供连接时，该连接必须标记为“活动”；以便不向任何后续请求提供连接。

When the connection is not closed, you must handle the connection properly regarding commit, rollback etc.

当连接未关闭时，您必须正确处理有关提交、回滚等的连接。

Check your language or framework. Most of the frameworks already maintain a container of idle connections; and therefore, you do not need to worry about it. Some of the languages also provide this functionality out of the box. For example Golang core package type sql.DB maintains a pool of idle connections, capable of being used concurrently, so you do not need to worry about closing the connection.

检查您的语言或框架。大多数框架已经维护了一个空闲连接容器；因此，您无需担心。一些语言还提供了开箱即用的功能。比如 Golang 核心包类型 sql.DB 维护了一个空闲连接池，可以并发使用，所以你不需要担心关闭连接。