is it safe to keep database connections open for long time

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, solarge numbers of (relatively) idleconnections would use up machinememory. However, a modern 64-bitserver can have tens or hundreds ofGB of memory, so it could support avery large number of suchconnections.

• If a transaction was leftuncommitted on the client machine,the locks would be held open for aslong as the transaction was open. This led to a class of problems whensomeone could start a transaction,go off to lunch and forget they hadleft something open. This wouldlock any records referenced by thetransaction for hours at a time.

• Transactions could, however easilycover multiple acceses to thedatabase, 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.

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.

The difficulty with long-lived connections is that you might not be entirely sure that they're still there. A network failure, server restart or stateful firewall forgetting some of its state could all result in a "stale" connection which looks open, but then gives an error when you try to use it.

Connection pooling schemes normally resolve this by having some system to occasionally check that connections in the pool are healthy, or having a timeout after which unused connections are dropped.

Generally speaking, in a distributed system you need to code around failures of all kinds, keeping long-lived connections open makes this more difficult - but if you're happy to do it, great.