The statement was "Call commit (even if you have done only selects) ...". This was what I was referring to.
----------------- Ron Reidy Lead DBA Array BioPharma, Inc. -----Original Message----- From: Peter J. Holzer [mailto:[EMAIL PROTECTED] Sent: Thursday, October 21, 2004 10:20 AM To: [EMAIL PROTECTED] Subject: Re: Slow connection to Oracle 9i 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 This electronic message transmission is a PRIVATE communication which contains information which may be confidential or privileged. The information is intended to be for the use of the individual or entity named above. If you are not the intended recipient, please be aware that any disclosure, copying, distribution or use of the contents of this information is prohibited. Please notify the sender of the delivery error by replying to this message, or notify us by telephone (877-633-2436, ext. 0), and then delete it from your system.
