If he is using MS SQL server wouldn't with(tablock holdlock) also need to be used on the delete AS WELL as a transaction to actually prevent another process from inserting into the table until the transaction was complete.
It is my understanding that with(holdlock) would be necessary for the lock to be maintained until the transaction was committed. Furthermore wouldn't the DB attempt to only acquire an exclusive lock on the rows being modified? If course, depending of your lock escalation threshold, the row locks may have been escalated to a table lock anyway. Regardless, with(tablock) would be the only way to ensure an exclusive lock was placed on the entire table. A named cflock would help but ONLY if CF code on ONE server was the only thing accessing that table in the database. Code from another machine, or a SQL Agent job would not care about a named cflock. ~Brad -----Original Message----- From: Dave Watts [mailto:[EMAIL PROTECTED] Sent: Tuesday, September 11, 2007 1:32 PM To: CF-Talk Subject: RE: Odd database behavior: duplicate key error > Yes. CFTRANSACTION only creates a transaction for the current > thread (actually the current database connection from the > connection pool). If multiple threads could be running the > same process at the same time, resulting in a race condition, > then you need to lock that set of queries as well. Use a > named lock so that it only applies to that block of code, or > move the code into a stored procedure which should allow the > database itself to manage any concurrency issues. This is not correct at all, according to my understanding. CFTRANSACTION creates a database transaction, which uses either locking or MVCC (depending on the specifics of the database used) to prevent concurrent access to database objects that would cause data integrity problems. It doesn't just affect the current thread; it affects any attempt to simultaneously manipulate the same database objects - not just from within other threads in CF, but even from other database clients. The default isolation level for CFTRANSACTION is SERIALIZABLE, if I recall correctly. http://en.wikipedia.org/wiki/Isolation_(computer_science) In short, CFTRANSACTION will not only prevent race conditions within a series of related queries across multiple requests, it should be used instead of CFLOCK for this purpose. And, moving the code into a stored procedure, by itself, would make absolutely no difference, unless you also placed transactional logic within it - which would have the same effect as using CFTRANSACTION. Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ Fig Leaf Software provides the highest caliber vendor-authorized instruction at our training centers in Washington DC, Atlanta, Chicago, Baltimore, Northern Virginia, or on-site at your location. Visit http://training.figleaf.com/ for more information! ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Check out the new features and enhancements in the latest product release - download the "What's New PDF" now http://download.macromedia.com/pub/labs/coldfusion/cf8_beta_whatsnew_052907.pdf Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:288197 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
