Re: RAC, SELECT FOR UPDATE, connection pools

  • From: Nigel Thomas <>
  • To: Dave.Herring@xxxxxxxxxx, Oracle-L Freelists <oracle-l@xxxxxxxxxxxxx>
  • Date: Fri, 12 Feb 2010 14:51:16 +0000


On 12 February 2010 13:46, Herring Dave - dherri <Dave.Herring@xxxxxxxxxx>wrote:

> I'm wondering if I could get a little more detail about what Martin listed
> below:
A connection pool works great as long as the interactions between the web
application and the database are effectively stateless. So when your web
application starts an interaction by pulling an available connection from
the pool, it has to obey the rule that it will do one of the following
before releasing the connection back to the pool. It MUST either
a) commit
b) rollback
c) never start a DB transaction in the first place (arguably this should be
handled by b anyway 0 - and watch out for selects from dblinks)

The web app also shouldn't normally (*) rely on any connection state
(including PL/SQL package variables etc) except during the period between
getting and releasing the connection. So you can build up state, but once
you commit and release the connection, consider it gone (same goes for GTTs
of course). And the web app should always release the connection at the end
of the interaction, so the pattern is:
- user does something
- web app processing may include one or more instances of [getting, using
and releasing] a connection.
- user does something else

So of course you can't SELECT FOR UPDATE in one instance of a connection,
then give the connection back to the pool and hope that when the user
decides to commit 5 seconds or 10 minutes later, the same connection will be
given back, with the same DB transaction in progress.

NOTE (*) I did work on an OC4J app where we did rely on package state
remaining across invocations, but that was effectively a cache that was the
same for everyone; so it didn't matter that it had been loaded by an earlier
user of the connection. Gradually all connections in the pool would load up
the cache as the connections were used - after which the load to get that
static data from the database disappeared. The cache would be cleared by
recycling the app server itself.


Regards Nigel

Other related posts: