> I'm not using any CFTRANSACTION, though. What might have > an impact here is that this is a code that loops up to four > times and does an insert every time. The user selects up > to four items, and then the code loops through them and > inserts a record in the table in each iteration.
Well, then, see if you're using a clustered index on your identity column, and find out from your DBA what your lock level really is for inserts, and as a stopgap preventative measure, you may want to do the following: 1. Use CFTRANSACTION around your insert block, if for no other reason than if the thing fails, you won't have some of the records added but not others. Alternatively, keep track of which records have been entered successfully on each loop iteration; this will be a tiny bit of extra coding, but won't have the overhead of CFTRANSACTION if you really don't need it otherwise. 2. Use CFTRY/CFCATCH to catch your deadlock errors, and retry the operation in the CFCATCH block. You may even want to nest another CFTRY within there, so that if the second attempt fails, you can provide an alternative, like prompting the user to try again. Then, it's time to actually fix the problem (note that the above measures are just stopgaps). You may need a faster, or more powerful, database server, you may need to improve the speed of your database connection, you may need to port this logic to stored procedures (along with the rest of your db logic), and so on. Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ voice: (202) 797-5496 fax: (202) 797-5444 ______________________________________________________________________ Get the mailserver that powers this list at http://www.coolfusion.com FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
