Re: connection pooling from an application server to oracle

At 05:59 AM 12/15/2003, ryan_oracle_at_cox.net wrote:
>The software engineers here are using an application server with
>connection pooling to connect to our oracle instances.
>They are doing it with a dedicated connection to Oracle. No MTS.
>
>This concerns me. how do you handle transaction control in this type of
>environment? in this type of environment do you have to commit after every
>DML statement? since multiple users will access the database with the same
>conneciton?

You may have only, say, 10 connections that are servicing 100 users, but each user will appear to have his or her own, dedicated connection. This works because users are going to tend to "open" a connection, do some work, and "close" a connection in short order.

The classic example of the utility of a connection pool is a web site. Since HTTP is a stateless protocol, most JSP pages will open and close a connection each time a user requests a page. Within the scope of generating the page, the JSP engine will issue a few SELECT statements and may do a little DML. Since it takes a fraction of a second (we hope) to generate a single web page, opening & closing a physical connection would obviously take an order of magnitude more time than generating the contents of the page, the application server can be set to maintain a set of permanent database connections which it will parcel out to the individual JSP engines. As an added benefit, you now only need a number of connections in the connection pool equal to the number of truly concurrent sessions you have. Since users spend far more time digesting page contents than the servlet engine spends building them, this typically means that you can service hundreds of users with tens of connections. From a transaction control standpoint, however, the important thing to remember is that each connection will be assigned to a particular user for the length of that user's transaction.
Justin Cave
Distributed Database Consulting

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Justin Cave
  INET: jcave_at_cableone.net

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).