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.