I can't test it myself to simulate the race condition, but I definitely know that people have been talking about this issue for a very long time. I can remember reading posts about it going back for years, and in those cases, people had tried CFTRANSACTION alone and it didn't make a difference. When they added a named lock, the problem was resolved. So, I'm not sure if the behavior is different between different RDBMSs, or if the isolation level of the transaction makes some difference, but there have definitely been cases in the past where people did not get the desired result with CFTRANSACTION alone. I've read of others having a similar issue where inserting and then trying to select the last inserted value within a CFTRANSACTION block would still return the incorrect id if another thread had also performed an insert at the same time.
A quick Google search reveals this article by Simon, where he seems to indicate that setting the isolation level to "serializable" should work. Maybe that is the key. It would be worth it to try that out before adding the named lock. You know more about databases than I do Dave, does that sound like it would make a difference? On 9/11/07, Dave Watts <[EMAIL PROTECTED]> wrote: > > 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! > > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| ColdFusion 8 - Build next generation apps today, with easy PDF and Ajax features - download now http://download.macromedia.com/pub/labs/coldfusion/cf8_beta_whatsnew_052907.pdf Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:288191 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
