Re: Multiple db connections

bshah_at_sportsmail.com wrote:

> Hi,
>
> We need multiple database connections for a web-server like
> architecture. The program we are writing is in pro*c, and we wanted to
> know the best (considering both cost and efficiency) to set up the
> connection scheme.
>
> Reading over the documentation, I see that we could use one common
> context, shared between threads/connections. However, this context may
> not be used by more than thread at a given time. This seems to be a big
> limitation if there are thousands of connections.

You also run into massive locking problems. If user A is updating the EMP table, then all other users are going to see the changes even before the commit
happens. If user A subsequently chooses to rollback, all transactions rollback!

>
> Another alternative I see is to have one context per thread/connection.
> Efficiency and cost wise, this seems really expensive. Are there any
> real advantages to this method?

Solves the above problem, but if you have hundreds of users you're going to need one mother of a server.

>
>
> What about not using a context at all? Instead, just having one user
> logged in and making queries. This seems to be a better solution than
> both the aforementioned. Is this a viable solution?

Logging into an Oracle database can be a slow processes, especially if you don't want to use MTS. Even reducing the number of connections from the the number of threads to the number of users still leaves all but the most trivial application with a huge number of connections, and if you use connection time-outs to reduce the number of users you will then have the problem that user's will percieve the application as very slow.

>
>
> This seems like a common issue to web servers. How do they do it?
> Or is there another better solution?
>

The way most web-servers that I've seen work (SilverStream, Oracle Web Server, BEA, Gemstone etc. ) is to use connection pools, where the web-server
logs into the database multiple times, and then allocates one of these connections to
a transaction as needed.

As far as the database is concerned, user X has simply logged in six or so times, but
these six connections are then sub-allocated by the Webserver to different users as
needed. Since most users spend very little of their time actually updating the database -
as opposed to viewing data - you rarely need a large number of connections. It does
however demand that the Development team design their transactions with care, since
if you don't release the connection when the update is complete, the connection pool
will quickly be exhausted, and the Webserver will start establishing more connections
to the database, or simply refuse user updates.

Hope some of that was helpful.

Graham Received on Wed Sep 27 2000 - 07:47:50 CDT