Kenneth:
There's a number of good connection pool implementations out there in open
source, perhaps you can start from one of them first to save some effort -
the connection pool we use in Expresso has some of the features you're
looking for though. You can set a limit on the number of connections and say
how long they can be held (with auto release, handling your timeout issue).
We've used it in a number of situations where the number of connections is
limited (either by memory or by the hosting company) and it scales well.
You're welcome to a copy at our site (http://www.javacorporate.com) - hope
it's helpful.
Mike
Javacorporate Ltd
> -----Original Message-----
> From: A mailing list for discussion about Sun Microsystem's
> Java Servlet
> API Technology. [mailto:[EMAIL PROTECTED]]On
> Behalf Of Kwan,
> Kenneth Y
> Sent: Thursday, December 02, 1999 10:34 PM
> To: [EMAIL PROTECTED]
> Subject: Re: Two simultaneous statements in the same connection.
>
>
> Thanks Graig and I��m now have more confidence on sharing
> connections among
> servlets
>
> The reasons behind why I have to build my connection pool
> instead of using
> common ones are:
> - The hit rate of my app will be very large and all
> servlet operations
> (doGet/doPost) require many database queries and updates
> - each connection in Oracle requires around 70M memory
> and so seems
> difficult to built a large pool
> - all updates are auto-commit
> - heard that connection will hold resources in Oracle
> if connection
> keep open for sometime, so I have to refresh (release and re-connect)
> connections periodically and I don��t known whether existing
> connection pool
> implementations have taken care about that
> - heard that connection will timeout if not used for
> long time, so I
> have to set periodic task to do dummy operation
>
> I really don��t want to write my own connection pool but can
> anyone help to
> clear my concerns.
>
> Thanks in advance
>
> Kenneth
>
> -----Original Message-----
> From: Craig R. McClanahan [mailto:[EMAIL PROTECTED]]
> Sent: Friday, December 03, 1999 12:38 AM
> To: [EMAIL PROTECTED]
> Subject: Re: Two simultaneous statements in the same
> connection.
>
> "Kwan, Kenneth Y" wrote:
>
> > So, does anyone know that Oracle 8i JDBC OCI driver
> can support
> concurrent
> > statements on the same connection, e.g. connection
> which is shared
> by many
> > servlets ?
> >
>
> There are really two issues here.
>
> Yes, the Oracle drivers support multiple active statements per
> connection. The
> connection can be shared by many servlets -- a very
> convenient way
> is to store
> the open connection as a servlet context attribute
> (ServletContext.setAttribute()).
>
> However, you need to consider what happens when more
> than one person
> is using
> your servlet-based application. If they are all
> using the same
> connection, the
> fact that you have multiple statements works fine, up
> until the
> point where you
> do a COMMIT or ROLLBACK for a user. At that point,
> the database
> transaction
> which is running is completed -- and this affects *all* of the
> users, whether
> they are through or not! Database transactions are
> global to a
> connection.
>
> This is one of the reasons that connection pools were
> created. The
> idea is
> that, within a single response, you follow this kind
> of a design
> pattern:
> * Allocate a connection from the pool
> * Do all the database work you need
> * Do a COMMIT or ROLLBACK
> * Return the connection to the pool
>
> In this way, the database accesses from different
> simultaneous users
> do not
> interfere with each other, because they are running
> in different
> transactions.
> Within a particular transaction, you can still have multiple
> statements open if
> you need them (because Oracle supports this), but
> they will all be
> aimed at
> performing part of a single overall process.
>
> There have been tons of discussions about connection
> pools on this
> list, along
> with sources for several versions of them. See the
> list archives
> (http://archives.java.sun.com) for details, or check
> some of the FAQ
> sites.
>
> >
> > Kenneth
> >
>
> Craig McClanahan
>
>
> ______________________________________________________________
> _____________
> To unsubscribe, send email to [EMAIL PROTECTED]
> and include in
> the body
> of the message "signoff SERVLET-INTEREST".
>
> Archives:
> http://archives.java.sun.com/archives/servlet-interest.html
> Resources:
> http://java.sun.com/products/servlet/external-resources.html
> LISTSERV Help: http://www.lsoft.com/manuals/user/user.html
>
> ______________________________________________________________
> _____________
> To unsubscribe, send email to [EMAIL PROTECTED] and
> include in the body
> of the message "signoff SERVLET-INTEREST".
>
> Archives: http://archives.java.sun.com/archives/servlet-interest.html
> Resources:
> http://java.sun.com/products/servlet/external-resources.html
> LISTSERV Help: http://www.lsoft.com/manuals/user/user.html
>
___________________________________________________________________________
To unsubscribe, send email to [EMAIL PROTECTED] and include in the body
of the message "signoff SERVLET-INTEREST".
Archives: http://archives.java.sun.com/archives/servlet-interest.html
Resources: http://java.sun.com/products/servlet/external-resources.html
LISTSERV Help: http://www.lsoft.com/manuals/user/user.html
