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.