RE: cftransaction isolation=repeatable_read
Bump... Casey - is this method thread safe? -Original Message- From: Brook Davies [mailto:cft...@logiforms.com] Sent: December-13-11 10:15 PM To: cf-talk Subject: RE: cftransaction isolation=repeatable_read So this would update and select the rows at the same time? Would I need to use transaction or would this guarantee if another request for the same query at the exact same time happened, it would not return or update the same rows? Brook -Original Message- From: Casey Dougall [mailto:ca...@uberwebsitesolutions.com] Sent: December-13-11 6:29 PM To: cf-talk Subject: Re: cftransaction isolation=repeatable_read On Tue, Dec 13, 2011 at 6:13 PM, Brook Davies cft...@logiforms.com wrote: I guess my question is, will this lead to performance problems? Is there a more elegant way to do this? Are you using MS SQL server? If so you only need one query which will still output rows like the first query with OUTPUT. cfquery name=getEvents update t1 set t1.enabled = 0 OUTPUT inserted.ID, inserted.cfc, inserted.runMethod, inserted.arguments, inserted.frequency, inserted.frequencytype from scheduledTasks t1 inner join scheduledTasks t2 on t1.ID = t2.ID where t2.enabled = 1 and t2.nextDate cfqueryparam cfsqltype=cf_sql_timestamp value=#createodbcdatetime(Now())# and t2.startDate cfqueryparam cfsqltype=cf_sql_timestamp value=#createodbcdatetime(Now())# and t2.endDate cfqueryparam cfsqltype=cf_sql_timestamp value=#createodbcdatetime(Now())# /cfquery ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:349127 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: cftransaction isolation=repeatable_read
So this would update and select the rows at the same time? Would I need to use transaction or would this guarantee if another request for the same query at the exact same time happened, it would not return or update the same rows? The INSERTED scope corresponds to the record you're updating, so yes, it's transaction-safe. Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ http://training.figleaf.com/ Fig Leaf Software is a Veteran-Owned Small Business (VOSB) on GSA Schedule, and provides the highest caliber vendor-authorized instruction at our training centers, online, or onsite. ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:349128 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: cftransaction isolation=repeatable_read
On Tue, Dec 13, 2011 at 6:13 PM, Brook Davies cft...@logiforms.com wrote: I guess my question is, will this lead to performance problems? Is there a more elegant way to do this? Are you using MS SQL server? If so you only need one query which will still output rows like the first query with OUTPUT. cfquery name=getEvents update t1 set t1.enabled = 0 OUTPUT inserted.ID, inserted.cfc, inserted.runMethod, inserted.arguments, inserted.frequency, inserted.frequencytype from scheduledTasks t1 inner join scheduledTasks t2 on t1.ID = t2.ID where t2.enabled = 1 and t2.nextDate cfqueryparam cfsqltype=cf_sql_timestamp value=#createodbcdatetime(Now())# and t2.startDate cfqueryparam cfsqltype=cf_sql_timestamp value=#createodbcdatetime(Now())# and t2.endDate cfqueryparam cfsqltype=cf_sql_timestamp value=#createodbcdatetime(Now())# /cfquery ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:349091 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
RE: cftransaction isolation=repeatable_read
So this would update and select the rows at the same time? Would I need to use transaction or would this guarantee if another request for the same query at the exact same time happened, it would not return or update the same rows? Brook -Original Message- From: Casey Dougall [mailto:ca...@uberwebsitesolutions.com] Sent: December-13-11 6:29 PM To: cf-talk Subject: Re: cftransaction isolation=repeatable_read On Tue, Dec 13, 2011 at 6:13 PM, Brook Davies cft...@logiforms.com wrote: I guess my question is, will this lead to performance problems? Is there a more elegant way to do this? Are you using MS SQL server? If so you only need one query which will still output rows like the first query with OUTPUT. cfquery name=getEvents update t1 set t1.enabled = 0 OUTPUT inserted.ID, inserted.cfc, inserted.runMethod, inserted.arguments, inserted.frequency, inserted.frequencytype from scheduledTasks t1 inner join scheduledTasks t2 on t1.ID = t2.ID where t2.enabled = 1 and t2.nextDate cfqueryparam cfsqltype=cf_sql_timestamp value=#createodbcdatetime(Now())# and t2.startDate cfqueryparam cfsqltype=cf_sql_timestamp value=#createodbcdatetime(Now())# and t2.endDate cfqueryparam cfsqltype=cf_sql_timestamp value=#createodbcdatetime(Now())# /cfquery ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:349092 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm