> We have a CF application through which users complete a form. > When they submit, data is inserted into an Access database using > logic like this: > > <cftransaction action="begin"> > <cfset CommitIt=TRUE> > <cftry> > <cfquery...> > >>delete any existing records for this user...<< > </cfquery> > <cfcatch type='database'> > <cfset CommitIt=FALSE> > </cfcatch> > </cftry> > <cfloop index="i" from="1" to="255"> > <cftry> > <cfquery...> > >>insert new record from form data<< > </cfquery> > <cfcatch type='database'> > <cfset CommitIt=FALSE> > <cfexit> > </cfcatch> > </cftry> > </cfloop> > <cfif CommitIt is TRUE> > <cftransaction action='Commit'> > <cfelse> > <cftransaction action='Rollback'> > </cfif> > </cftransaction> > > The problem we're hitting is that the delete/insertion process takes a > few seconds and when two users click the button almost simultaneously, > one of them is locked out and his data is never written. > > Is there a sensible way to queue the delete/insertion so that it works > correctly once the database lock is cleared, rather than failing? Well, one thing you can do is minimize the amount of time spent in the transaction. For example, it's probably not necessary to have a loop from 1 to 255, unless you're absolutely sure you're going to have exactly 255 records inserted every time. You might also decide that it's not really necessary to contain all of this logic within a single transaction; if, for example, you're sure that people aren't going to be editing the same records simultaneously, you might do without the transactional logic entirely in this case. You'll also get better transactional performance out of a database that supports locking at a more granular level than the entire table. Finally, I don't see the purpose of all of the CFTRY and CommitIt logic in the above code; if any of the queries fail, the entire transaction will fail - that's the primary purpose of using the CFTRANSACTION tag! Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ voice: (202) 797-5496 fax: (202) 797-5444 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Archives: http://www.mail-archive.com/[email protected]/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
