The transaction won't actually isolate you from a race condition. Other connections will be able to insert at the same time. A full table lock, on the other hand, will protect you. A unique key constraint would also do it.
cheers, barneyb On Feb 11, 2008 2:54 PM, s. isaac dealey <[EMAIL PROTECTED]> wrote: > > Unique and random are somewhat orthagonal concepts. In order to > > enforce the uniqueness, you're placing a constraint on the random > > values, so they're not really totally random. For the random part, > > #randRange(0, 999999)# will take care of it. For the uniqueness, > > you'll have to check the generated value against all others in the > > database, and if the value is already in there, generate another > > random value. Repeat until you find one that is unique. > > It's also important that you place a lock around the uniqueness test for > each individual number and that you numberformat(number,000000) if you > want to make sure the values are all 6 characters in length. So once > you've generated your random number with > > rn = NumberFormat(RandRange(0,999999),000000); > > Then you'll want to make sure you use a cftransaction around the test & > insert, i.e. > > <cftransaction isolation="serializable"> > <cfquery name="checkunique" ...>select x from mytable...</cfquery> > <cfif checkunique.recordcount eq 0> > <cfquery ...>insert into mytable ...</cfquery> > <cfelse> > <!--- here it's got to loop out of the transaction > to create another random number ---> > </cfif> > </cftransaction> > > Without that transaction then you have the associated risks of a race > condition. > > -- > s. isaac dealey ^ new epoch > isn't it time for a change? > ph: 503.236.3691 > > http://onTap.riaforge.org/blog > > > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;160198600;22374440;w Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:298755 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
