Re: connection pooling and oracle

rgaffuri_at_cox.net (Ryan Gaffuri) wrote:
> Bare with me on this. I dont know much about connection pooling. My
> take on connection pooling is that you open a small number of sessions
> to the database and all users connect to a middle tier which decides
> who connects to the database. Am I correct? I have some questions
> about how this affects oracle.

You are correct for application server-side connection pooling (as opposed to MTS, which is DBMS-side).

> 1. Do you need to commit after every sql statement?

Not the way I've seen it implemented. You check out a connection from the app server's pool, do what you need to do, commit (or rollback), and return the connection to the pool. In fact, the connection handle automatically has autocommit turned on, you need to turn it off. Then when you turn it back in, if autocommit is turned off it will commit (or maybe rollback, I don't recall) for you.

> If there are just
> a few sessions open and another user comes in. He commits, your data
> will be committed anyway.

That would only happen if you didn't commit before you turned it back in, which would be a bad thing to do.

> if so doesnt this kill recovery with all
> sorts of bad data problems and inconsistencies? Perfomance must be
> impacted also right?

If the application doesn't naturally call for frequent commits, it probably isn't all that suitable for connection pooling in the first place.

Xho

-- 
-------------------- http://NewsReader.Com/ --------------------
Usenet Newsgroup Service              New Rate! $9.95/Month 50GB