Ali, Wow... ummm... that's a shocker.
As far as isolation levels... umm... which is worse: A performance Drop Perpetually Corrupted Data I have to say that to have the DB set up so that this could happen at all is a matter of concern for me. It doens't sound like a sound design. I hope that wasn't too blunt, but this should have happened to begin with. You've got a limited number of choices, and CFTransaction won't help unless you set the isolation level high enough that the next query can't write until the first query's data has been comitted. Read_comitted should be close enough, and give you less of a performance hit than serializable... but without reworking some things I'm afraid there's not a lot that can be done otherwise. You could set up an On Insert trigger to generate a UUID You could add an auto-incremented field You could add a date-time field with a default value of getdate() you could add an inserted-by field that carries the application name and cftoken values There's many different ways you could structure this to keep the data unique even IF the records end up with a confict such as you've encountered. I would HIGHLY recommend you add a UNIQUE constraint to the column in question as well... to prevent such duplicates in the future. You could code your create routine, then to try to insert, and in CFCATCH type="database" check for whatever you need to see if a duplicate entry was attempted to be inserted in a column with a unique constraint and re-fire your create routine. These are just some thoughts... the eventual implementation is entirely up to you. Laterz! J On 4/20/05, Ali Awan <[EMAIL PROTECTED]> wrote: > Jared, > > LOL, yeah it sounds like an Access issue. > Actually we're using SQL 2k. > > Another question though, by using a high isolation level will that slow the > app down significantly? I read in the docs that there's a lot of overhead > associated with it? > > Thanks, > Ali > > >Ali, > > > >You'd be best off to use a CFTRANSACTION with a high isolation level, > >like read_committed or even serializable. > > > >I'm gonna take a stab at this and guess you're using Access? > > > >Keep us posted! > > > >J > > > >On 4/19/05, Ali Awan <[EMAIL PROTECTED]> wrote: > > > > > >-- > >--------------- > >------------------------------------- > >Buy SQLSurveyor! > >http://www.web-relevant.com/sqlsurveyor > >Never make your developers open Enterprise Manager again. > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Find out how CFTicket can increase your company's customer support efficiency by 100% http://www.houseoffusion.com/banners/view.cfm?bannerid=49 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:203741 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
