On 2004-10-21 08:51:04 -0600, Reidy, Ron wrote: > My comments are below ... > > ----------------- > Ron Reidy > Lead DBA > Array BioPharma, Inc. > > > -----Original Message----- > From: Peter J. Holzer [mailto:[EMAIL PROTECTED] > Sent: Wednesday, October 20, 2004 9:10 AM > To: [EMAIL PROTECTED] > Subject: Re: Slow connection to Oracle 9i > > > [Rearranged quotes for better readability] > > On 2004-10-19 08:23:53 -0600, Reidy, Ron wrote: > > BAXTER, LINCOLN A [mailto:[EMAIL PROTECTED]: > >> Ok, then if you use Apache and mod_perl this should be easy... just > >> open connection if you don't have it (or you get and error on it), > >> and keep it around. > >> > >> Call commit (even if you have done only selects), at the end of > >> each "event" instead of close. > > This is an **extremely** bad idea. A commit() should be issues only > > when necessary - the cost in the database of a commit is large and > > doing so in this random fashion is an invitation to other performance > > problems. > > >I don't know what lincoln means with "event", but if it is a single > >http request, then that is not a "random fashion". > > Maybe I misstated or was misunderstood. To clarify: > > Issuing commits in the manner described is (IMHO) random fashion. > Commit/rollback is for transactional use; a select statement by itself > is not a transaction. If it were, then when one uses SQL*Plus or > iSQL*Plus, you would need to issue commits all the time.
Where did I (or anyone else in this thread) talk about a "select
statement by itself"? The original question was about a web application,
and I believe we are still talking about web applications. If this
thread took a turn I missed, I'll shut up and apologize.
A web application is driven by HTTP requests.
The browser sends either GET or POST requests to the web application,
and the application answers these requests.
By convention (and I know that there are many applications which ignore
this convention) GET requests are used to retrieve information and POST
requests are used to send information.
Thus to process a GET request, the application may issue a number of
SELECT commands against a database, process the resultset in some
arbitrary manner (usually converting it into HTML, an image, or
whatever) and send that to the client. Since no data was changed, no
commit is necessary. (There may be side effects - for example, a counter
may be incremented - which may necessitate a commit, but this is a side
effect, not a direct cause of the request).
To process a POST request, the application usually has to issue at least
one INSERT or UPDATE command. It may also have to issue a number of
SELECT commands. Again, a result is sent back to the browser. The
important thing is that when the user sees the result, he expects the
effect of the POST request to be permanent. He has clicked on the
"submit" button of a form, and the result should be either "the
information has been processed" or "an error occured". It should not be
"I got your information, but I may choose to throw it away at some
arbitrary point in the future". Thus from the application-logic of a web
application, a POST request is a transaction. It may issue 50 selects,
20 inserts and 30 updates, but before it sends back the result to the
user, it has to either commit or rollback.
There are some cases where a logical transaction spans several HTTP requests.
You may want to start a transaction, then select some data, present that
to the user, get some input from the user, and finally ask the user to
commit or roll back. The straightforward way would be to implement it in
this fashion. Unfortunately, in many web application environments, you
can't. For example, if you use Apache mod_perl, each HTTP request will
be routed randomly to one of the running Apache processes (and also new
processes will be created if necessary, and old processes will die if
unused for some time). Thus each session to the database will get
completely unrelated requests; and requests from the same user session
will use different database sessions. So, as an application programmer
writing for this environment, you will still have to commit after every
POST request because otherwise 1) the next request simply won't see the
result in the database and 2) an error in a completely unrelated request
may cause a rollback and delete data.
> As pointed out in the link I supplied to Steve Adam's website, random
> commits are a bad idea.
I agree with this. But a commit at the end of each HTTP request (which
changed any data) isn't random. It is pretty much required by the
structure of web applications.
hp
--
_ | Peter J. Holzer | Shooting the users in the foot is bad.
|_|_) | Sysadmin WSR / LUGA | Giving them a gun isn't.
| | | [EMAIL PROTECTED] | -- Gordon Schumacher,
__/ | http://www.hjp.at/ | mozilla bug #84128
pgpZiU3r6UgrZ.pgp
Description: PGP signature
