是否可以打开 sql 连接? [英] Is it ok to leave a sql connection open?

问题描述

我正在通过一个 com 端口连续接收数据并进行一些解码.解码完成后，我必须将结果存储在 sql 数据库中.我在想，因为解码已经完成(在一个while循环中总是运行)每秒几十次，如果明智地打开和关闭与sql server的连接，数据需要每秒存储到数据库中几十次每个while循环，或者只是让它保持打开状态并继续将数据写入数据库.首先这可能吗?其次，如果连接保持打开状态，第三方应用程序或计算机能否在我的程序存储数据的同时访问数据库并读取数据?

I am receiving data through a com port continuously and doing some decoding. When decoding is done i have to store the results in a sql database. I am thinking since the decoding is done (in a while loop always running) dozens of times per second and data need to be stored to the database dozen of times every second if it is wise to open and close the connection to the sql server in each while loop or just leave it open and continue to write data to the database. First of all is this possible? Secondly if the connection remains open can third party applications or computer access the database at the same time and read data as my programm stores data?

推荐答案

一个数据库支持多个并发连接，所以在这种情况下让数据库连接保持打开状态是非常可行的——只有在有一个长时间运行的查询会导致行/表锁定.完成后关闭连接即可.

A database supports more than one concurrent connection, so yes it is very feasible in this scenario to leave the DB connection open - you will only lock out others if you have i.e. a long running query that results in row/ table locking. Just close the connection when you are done.

还要考虑尽管大多数 DB(即 SQL Server)使用 连接池 在内部，因此即使您关闭了数据库连接，它也只是返回到池中并且没有物理关闭-池管理物理数据库连接-这会带来更好的性能，因此快速打开/关闭连接的影响是减少.

Also consider though that most DBs (i.e. SQL Server) use connection pooling internally, so even though you close a DB connection it just goes back to the pool and is not physically closed - the pool manages the physical DB connections - this results in much better performance, so the impact of opening/closing connections rapidly is reduced.

来自 MSDN:

连接池减少了新连接的次数必须打开.池子保持物理的所有权联系.它通过保持一组活动来管理连接每个给定连接配置的连接.每当用户在连接上调用 Open，池化程序会寻找可用的池中的连接.如果一个池连接可用，它将其返回给调用者，而不是打开新连接.当...的时候应用程序在连接上调用 Close，pooler 将其返回给池化的活动连接集，而不是关闭它.一旦连接被返回到池中，它已准备好在下一次公开通话.

Connection pooling reduces the number of times that new connections must be opened. The pooler maintains ownership of the physical connection. It manages connections by keeping alive a set of active connections for each given connection configuration. Whenever a user calls Open on a connection, the pooler looks for an available connection in the pool. If a pooled connection is available, it returns it to the caller instead of opening a new connection. When the application calls Close on the connection, the pooler returns it to the pooled set of active connections instead of closing it. Once the connection is returned to the pool, it is ready to be reused on the next Open call.

这篇关于是否可以打开 sql 连接?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！