Hello Doug, as ever... inline ;)
>-----Original Message----- >Subject: Get New Record ID in ORACLE >From: Douglas Knudsen <[EMAIL PROTECTED]> >Date: Wed, 8 Jun 2005 15:15:06 -0400 >Thread: >http://www.houseoffusion.com/cf_lists/index.cfm/method=messages >&threadid=40571&forumid=4#209017 > >1) huh? each connection to Oracle from CF creates a new >session. If you >happen to have connection pooling on in the ColdFusion DSN >settings, then >yes you would only see a fixed set of sessions as they are >reused, having >this set to one would get what you are seeing perhaps. If you have >connection pooling off in CF for the DSN, then you could see only one >session in the kill/trace view in Oracle IFF there is only one >connection to >Oracle from CF whilst you are looking. In fact, I have >connection pooling >off for several DSNs on a server. Say the DSN is named FOO. I >can look in >Oracle and see multiple sessions for FOO that die as soon as >the ColdFusion >request containing the CFQUERY/CFSTOREPROC call is done. I was all ready to post a big rebuttal to this... and then i saw you were right ;) I made a cfpage like this: -----------------scribble.cfm------------ <cfloop collection="#cgi#" item="header"> <cfquery name="baseinsert" datasource="trademak"> insert into t (id,name) values(s.nextval,'#header#') </cfquery> <!--- sleeeeeeep ---> <cfset thread = createObject("java", "java.lang.Thread")> <cfset thread.sleep(10000)> </cfloop> and then opened up a bunch of tabs in my browser and cued up the url, made sure i had the "maintain connections" checked and "limit connections" unchecked in the admin, kicked off my tabs, and checked the sessions in v$session -> result: just one session. Luckily for me, before putting my foot in it big time, i also opened the url in the big blue E and checked again: two sessions! I then opened up a truly sick number of tabs and checked again: some more sessions (total of four right now). So we're not talking one session per connection: it's more like cf creates extra sessions/connections on an "as needed" basis. > >Now, what I have not figured out yet is, if you have >connection pooling off >for a DSN, call it GOO, and you have two CFQUERYs in a single >CF page, does >one connection/sessioon get used for both or are there two >connections/sessions? If its two seperate requests to the same >CF page, then >yes, there are two seperate and distinct connections/sessions. >I just don't >know at the page level. Well, i tried turning "limit connections" on and setting "Restrict connections to" to 1 then calling the same page as above (since it satisfies the "multiple queries in a page" criteria) - result: - with "maintain connections" checked: one session, same sid, same serial throughout the page exec time, sticks around after the page ends (and gets reused on reload) - with "maintain connections" UNchecked: one session, same sid, same serial throughout the page exec time, but it disappears once the page as finished. So it seems to me that cf will always execute all the queries in a given page within the same session, but it *may* also execute queries from different pages in the same session as well. I think it has to do with making sure all the queries in a given transaction execute in the same session (duh) - if a transaction is only one query long, then you can reuse the session for another transaction quickly. Conclusion would be: if you have a small pool of connections, be careful of long running transactions because you're going to "hog" a scarce resource. > >2) a comment from my DBA here 'Yup, that comment is dead >wrong. Oracle never >inherently auto-commits inside or after a SP, unless it contains a DDL >operation (create index, analyze table, grant privs, etc).' >I'm agreeing >with tanguy below, we have seen this behaviour. CF >automagically appends >'SET TRANSACTION ISOLATION LEVEL READ COMMITTED' to the end of each >statement passed unless you use CFTRANSACTION. This includes >calling SPs. READ COMMITTED is pretty close to oracle standard, and i don't think we can put **all** the blame squarely on cf - part of it belongs with JDBC, which autocommits by default. Of course, java developers can just do conn.setAutoCommit(false) - it would have been nice if there was a checkbox in the cfadmin that allowed that. On the other hand (there's always an other hand with me), cf is about making things simple, and having to add an extra <cfquery> at the bottom of every page that modified data just to commit is not a simple solution. Cftransaction actually works incredibly well, considering how easy it is to use. Seriously, if you look at the hoops java developers have to jump through, coldfusion's db access stuff is pretty sweet. >Now, if you have a commit withing the body of your SP, this >kind of bypasses >what CF can be concerned about, eh? Well, a lot of people who work with other databases do that because in some databases long running transactions are a no-no, so you try to break them up to remove stress from your db server. This is not the case with oracle. > > >DK ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:209034 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
