IMHO, for an "insert parent Record" + "get @@identity" + "insert child records", any isolation type would be fine. it's being under the umbrella of a transaction that matters. return the @@identity, store in a variable and use that for the life of the process (the transaction)
actually, on second thoughts, "SELECT @@identity from tablename" is designed for returning autonumbers after insert (but you can only do it once). I mean, what's the alternative? "select Max(autonumberField) from tablename"? how can you trust that someone else doing an insert has got the same "Max(autonumberField)" because their thread also did that query before your thread could do anything about it. "isolation=serializable" is sounding better all the time... One thing to keep in mind is that CF is a multi-threaded webserver (LOL! is there any othertype?). cftransaction is a way of controlling which threads are accessing the db at any one time. Imagine three queries one after the other. it looks like big one block of code. But the way the CPU executes the commands, it's as if 1000 lines of code are between each cfquery with the other 900 lines executed under the context of diferent users. Additionally, if it was an update with multiple steps then I'd definatly pick the strongest isolation possible. if this was SQLServer you could put all the SQL in one cfquery seperated by ";" and bypass the whole issue but MSAccess/Jet db can't do that so separate query blocks it is. well, that's pretty much all I know about cftransaction (I've forgoten the rest at the moment...). as I said earlier, if anyone has additional info or corrections please speak up (no, they're all busy hasseling Taco or Steve Onnis - go figure!) good luck Andrew cheers barry.b -----Original Message----- From: Andrew Scott [mailto:[EMAIL PROTECTED] Sent: Thursday, 29 July 2004 10:59 AM To: CFAussie Mailing List Subject: [cfaussie] RE: CFTransaction and CFC's Hmm, Wasn't aware the serializable was default in a cftransaction, but my problem or query on this is that I use a try/catch on the outside of this just for general error handling anyway. What I was hoping for was that if I was to use another isolate method instead of serializable, would the @@identiy be better than a separate query for the same reason you describe and not return the wrong ID, I would assume that it only returns the id in that query as it is just the one query? Regards Andrew Scott Technical Consultant NuSphere Pty Ltd Level 2/33 Bank Street South Melbourne, Victoria, 3205 Phone: 03 9686 0485 - Fax: 03 9699 7976 -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Barry Beattie Sent: Thursday, 29 July 2004 10:50 AM To: CFAussie Mailing List Subject: [cfaussie] RE: CFTransaction and CFC's I personally can't see any problems with using <cftransaction isolation="SERIALIZABLE"> apart from slowing things by cueing the threads. Besides, it's the default setting if you don't specify the isolation mode (as of CFMX) [Much Deleted] --- You are currently subscribed to cfaussie as: [EMAIL PROTECTED] To unsubscribe send a blank email to [EMAIL PROTECTED] Aussie Macromedia Developers: http://lists.daemon.com.au/ --- You are currently subscribed to cfaussie as: [EMAIL PROTECTED] To unsubscribe send a blank email to [EMAIL PROTECTED] Aussie Macromedia Developers: http://lists.daemon.com.au/ --- You are currently subscribed to cfaussie as: [EMAIL PROTECTED] To unsubscribe send a blank email to [EMAIL PROTECTED] Aussie Macromedia Developers: http://lists.daemon.com.au/
