James,
Interesting test. This strongly contradicts what our DBAs have been
telling me. I'm going to bring this up with them. (I love when they
are proved wrong!). The only thing is that you are using only a single
session. ie. same user to perfrom both actions since I'm assuming u/p
is hardcoded into your DSN. Although you get all sorts of errors with
cftransaction if they aren't hardcoded.
What about with stored procedures? I think the second a procedure is
completed and returned the commit is automatically applied, regardless
of the cftransaction. So is it safe to say that cftransaction should
only be used with cfquery and not cfstoredproc?
-Adam
On 6/7/05, James Holmes <[EMAIL PROTECTED]> wrote:
> Here's an example to demonstrate that CFTRANSACTION does use row locking
> with Oracle, even with different DSNs.
>
> Page 1:
>
> <cftransaction>
> <cfquery datasource="mydsn">
> update testtransaction
> set somedata = 0
> where pkid = 5
> </cfquery>
> <cfset thread = createObject("java", "java.lang.Thread")>
> <cfset thread.sleep(10000)>
> <cfquery datasource="esc_divweb" name="QGetValue">
> SELECT somedata
> from testtransaction
> where pkid = 5
> </cfquery>
> <cfoutput>#QGetValue.somedata#</cfoutput>
> </cftransaction>
>
>
> Page 2:
>
> <cftransaction>
> <cfquery datasource="mydns2">
> update testtransaction
> set somedata = 10
> where pkid = 5
> </cfquery>
> <cfquery datasource="esc_divweb2" name="QGetValue">
> SELECT somedata
> from testtransaction
> where pkid = 5
> </cfquery>
> <cfoutput>#QGetValue.somedata#</cfoutput>
> </cftransaction>
>
>
> Note that transaction 1 takes 10 seconds (and a bit) to run. As Oracle
> locks rows in the update such that other writers to the same row are
> blocked, the second page, if run say 1 second after page 1 starts, waits
> 9 seconds for the transaction in 1 to end before it can do anything. As
> the SELECT and CFOUTPUT are all within the transaction, the race
> condition is avoided and each page presents consistent data. This is
> with completely separate DSN definitions pointing to the same schema.
>
> Just for fun, I put page 2 on the CF7 server in the other roon and got
> exactly the same behaviour - page 2 waits for page 1 to finish (and
> that's a different physical server). CFTRANSACTION provides a real
> transaction on Oracle.
>
>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Discover CFTicket - The leading ColdFusion Help Desk and Trouble
Ticket application
http://www.houseoffusion.com/banners/view.cfm?bannerid=48
Message: http://www.houseoffusion.com/lists.cfm/link=i:4:208966
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
