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.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
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:208923
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=11502.10531.4
Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
