is it safe to keep database connections open for long time

Question

I have a .net client application which is connected to a remote database. Is it safe to keep a single connection open for the lifetime of the client (hours)?

Does the answer hold if I have multiple (10 or 100) clients running?

Answer 1

Absolutely it is safe to do this. This is how client-server applications work. If you are using a three-tier application, the application server will keep a pool of connections open anyway.

Scalability is an issue, or at least used to be in the days that machines had less memory than modern kit. With a two-tier (client-server) application, each client opened a connection and held it open. This had several effects:

• Memory was used per-connection, so large numbers of (relatively) idle connections would use up machine memory. However, a modern 64-bit server can have tens or hundreds of GB of memory, so it could support a very large number of such connections.

• If a transaction was left uncommitted on the client machine, the locks would be held open for as long as the transaction was open. This led to a class of problems when someone could start a transaction, go off to lunch and forget they had left something open. This would lock any records referenced by the transaction for hours at a time.

• Transactions could, however easily cover multiple acceses to the database, which is harder to do with a conneciton pool.

A pooled architecture, which is common on 3-tier architectures has a finite number of connections between the application server and the database. Queries simply use the next available connection and updates are committed immediately. This uses less resources as you only have a finite number of connections open, and (in conjunction with an optimistic concurrency strategy) will eliminate a large of potential application concurrency issues.

In order to use long transactions (i.e. transactions that cover more than one call to the database) one has to de-couple the transaction from the connection. This is the basic architecture of a TP monitor and there are some standard protocols such as XA or OLE Transactions to support this. If this type of externally managed transaction is unavailable the application has to construct a compensating transaction that undoes the changes made by the application's transaction. This type of architecture is often used by workflow management systems.

Answer 2

Open and close your connection per business operation

If you are talking about a client/server application, I would recommend closing each connection as soon as you are done using it. While each individual application instance might take a small performance hit opening the connection, your application as a whole will scale better. This is somewhat dependent on the database server you are using. SQL Server will handle different numbers of concurrent connections based on the hardware it is installed on. If you want to scale up a client/server app to thousands of desktop, a small DB server might not handle all those desktops with open connections but could very well handle thousands of desktops with only some of the connections open.

I saw this first hand a few years ago. An application that was deployed to a few departments with no trouble was then deployed across the entire organization. The application was soon very, very slow. The organization was considering buying a very expensive chunk of hardware for their DB server to gain some performance. I recommended they open and close the db connection after each business operation. Luckily they had architected the application so that this was not a difficult change. They made the change and rolled it out during one of their weekly network updates. Overnight the application performance had improved significantly. They saved thousands of dollars.