Re: RAC, SELECT FOR UPDATE, connection pools

From: Nigel Thomas <nigel.cl.thomas_at_googlemail.com>
Date: Fri, 12 Feb 2010 14:51:16 +0000
Message-ID: <53258cd51002120651q507ca661l4f7d44f747ad041c_at_mail.gmail.com>

Dave

On 12 February 2010 13:46, Herring Dave - dherri <Dave.Herring_at_acxiom.com>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.

HTH Regards Nigel

--
http://www.freelists.org/webpage/oracle-l

Received on Fri Feb 12 2010 - 08:51:16 CST