Re: Generating unique random numbers
That is a documentation bug that has been fixed in CF 8. Thanks Jochem! Well damn... that was repeated in Simon's article too... (which, doesn't let us add blog-style comments) ... but I went ahead and submitted a comment on the CF7 docs with links to both the corrected CF8 documentation and your article, since I didn't see the correction in the comments that were already there. -- 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:298817 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: Generating unique random numbers
Firstly, the column in the table absoutley *should* have a unique index or constraint on it. Doing that will avoid any racing as the db will make it impossible to insert dupes. I actually wasn't saying it shouldn't have a unique constraint, just that if you're using cflock to handle the looping and checking, then the system will never actually reach a point at which the unique index becomes relevant, because the lock will prevent it from even attempting to insert any dup's. -- 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:298814 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Generating unique random numbers
I actually wasn't saying it shouldn't have a unique constraint, just that if you're using cflock to handle the looping and checking, then the system will never actually reach a point at which the unique index becomes relevant, because the lock will prevent it from even attempting to insert any dup's. I wasn't saying you weren't - just pointing out that it should ;) What I am saying though is that the check and the insert can be the same thing - why use two uniqueness checkers when one is already written for you and is water tight? Loop until the db doesn't throw back a unique constraint error when you try and insert. Seems to me the most logical approach (and it doesn't require a lock). Dominic -- Blog it up: http://fusion.dominicwatson.co.uk ~| 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:298828 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Generating unique random numbers
Ask Tom describes this well with regards to Oracle: http://www.oracle.com/technology/oramag/oracle/05-nov/o65asktom.html The drawback (with Oracle at least) is that if one tries to update the same row from two different serializable transactions, you'll get an error. Of course this can be trapped and appropriate action taken. On Feb 13, 2008 12:28 AM, Jochem van Dieten [EMAIL PROTECTED] wrote: s. isaac dealey wrote: 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. Umm... I was under the impression that a serializable transaction lock would lock the table. It doesn't: http://jochem.vandieten.net/2008/02/12/serializable-transactions-are-not-single-threading-a-database/ this is from the CF7 docs for cftransaction: * serializable: places an exclusive lock on every data table in use for the duration of the transaction. Causes access to the database to be single-threaded and is therefore not recommended for normal database access. http://livedocs.adobe.com/coldfusion/7/htmldocs/0346.htm That is a documentation bug that has been fixed in CF 8. Jochem ~| 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:298841 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: Generating unique random numbers
I'm with you on it feeling ugly - I barely ever use try and catch. I do however feel that it is the most effective solution to this problem - the best checker of duplication being the one created by the db server that happens at the very moment of the insert. I don't have a problem with semantics here, it reads well to me though I would certainly comment the code. d -- Blog it up: http://fusion.dominicwatson.co.uk ~| 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:298839 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
RE: Generating unique random numbers
I try not to rely too much on systems that expect errors to be thrown if I can avoid it, partly for semantic reasons, i.e. hopefully errors shouldn't be thrown during standard operation, but also because the trapping of errors is mechanically expensive. The trapping of errors is only expensive when there's an error to be handled. Otherwise, it's cheap - often cheaper than the alternative of whatever conditional testing you'd otherwise do. And, there's no reason why you can't think of exceptions as just one more part of an API, just another sort of message passed between objects. Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ Fig Leaf Training: Adobe/Google/Paperthin Certified Partners http://training.figleaf.com/ WebManiacs 2008: the ultimate conference for CF/Flex/AIR developers! http://www.webmaniacsconference.com/ ~| 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:298840 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: Generating unique random numbers
What I am saying though is that the check and the insert can be the same thing - why use two uniqueness checkers when one is already written for you and is water tight? Loop until the db doesn't throw back a unique constraint error when you try and insert. Seems to me the most logical approach (and it doesn't require a lock). I try not to rely too much on systems that expect errors to be thrown if I can avoid it, partly for semantic reasons, i.e. hopefully errors shouldn't be thrown during standard operation, but also because the trapping of errors is mechanically expensive. That being said, before I had created the fairly detailed form validation tools I use in my own work now, (or when they're not available to me), I tend to use try-catch and manually thrown exceptions as a quick and convenient method of form validation because it feels semantically appropriate (an error is thrown because there's something wrong) and form validation is a lower-frequency activity where mechanical performance isn't necessarily as important. I guess ultimately my reasons for avoiding it here aren't entirely rational, it just feels bad to me. Though as Jochem pointed out, I was mistaken about cftransaction in spite of having checked the official documentation (for CF7). So that being the case then I'd probably revert back to the other solution I mentioned before using the result attribute in cfquery and using query syntax to ensure it doesn't attempt to insert the duplicate. -- 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:298834 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Generating unique random numbers
s. isaac dealey wrote: 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. Umm... I was under the impression that a serializable transaction lock would lock the table. It doesn't: http://jochem.vandieten.net/2008/02/12/serializable-transactions-are-not-single-threading-a-database/ this is from the CF7 docs for cftransaction: * serializable: places an exclusive lock on every data table in use for the duration of the transaction. Causes access to the database to be single-threaded and is therefore not recommended for normal database access. http://livedocs.adobe.com/coldfusion/7/htmldocs/0346.htm That is a documentation bug that has been fixed in CF 8. Jochem ~| 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:298787 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: Generating unique random numbers
do you actually need the numbers to be random or just unique? auto-incremented zero-filed integers of set length are available as datatype in some DBs (i.e. MySQL)... not random (they will be sequential), but unique to each record... --- Azadi Saryev Sabai-dee.com http://www.sabai-dee.com Les Irvin wrote: Is there a slick way to do this in CF? Let's say I have a set of records - one field of which is for a unique and random 6 digit number. When adding a new record, what the best way to assign its random number that's not already assigned to another record? Thanks in advance, Les ~| 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:298786 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: Generating unique random numbers
and yeah bobby I USE IT ALL THE DAMN TIME to generate random numbers? Wow, what a waste of processing... RandRange() works just fine for numbers... as for uniqueness, you can only make sure the number isn't already in use by checking your database. If it is in use, generate another and check it. Wash, rinse, repeat until the number isn't being used. !--- generate a random number --- cfset thisRandNum = Randrange(0, 99) / !--- check to see if the number is in the database --- cfquery name=numCheck datasource=myDSN Select recordID from table where randomnumberfield = #val(thisRandnum)# /cfquery !--- if the number is in the database, repeat all of the above until it is NOT used in the db --- cfloop condition=numCheck.recordcount GT 0 cfset thisRandNum = Randrange(0, 99) / cfquery name=numCheck datasource=myDSN Select recordID from table where randomnumberfield = #val(thisRandnum)# /cfquery /cfloop #thisRandNum# At best, this is just taking a set amount of numbers and scrambling them... it will become less and less efficient (if you can call it efficient) with each new record added to your database. If you explain a little more about what it is you are doing and why, I'm sure someone can offer a better solution. ..:.:.:.:.:.:.:.:.:.:.:. Bobby Hartsfield http://acoderslife.com ..:.:.:.:.:.:.:.:.:.:.:. Bobby Hartsfield http://acoderslife.com -Original Message- From: Dave l [mailto:[EMAIL PROTECTED] Sent: Monday, February 11, 2008 5:48 PM To: CF-Talk Subject: Re: Generating unique random numbers Bobby has a nice lil udf for that and yeah bobby I USE IT ALL THE DAMN TIME!! haha Is there a slick way to do this in CF? Let's say I have a set of records - one field of which is for a unique and random 6 digit number. When adding a new record, what the best way to assign its random number that's not already assigned to another record? Thanks in advance, Les ~| 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:298785 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: Generating unique random numbers
The transaction won't actually isolate you from a race condition. Not if all applications use the same template for the transaction. ~| 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:298784 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Generating unique random numbers
Woops, made a few blunders in there - but hey, you get the idea I'm sure ;) Dom On 12/02/2008, Dominic Watson [EMAIL PROTECTED] wrote: Firstly, the column in the table absoutley *should* have a unique index or constraint on it. Doing that will avoid any racing as the db will make it impossible to insert dupes. With that in mind, your code could look something like: cfset recordInserted = false cfset maxTries = 10 cfset counter = 0 cfloop condition=not recordInserted and counter LT maxTries cfset recordInserted = true cfset counter = counter + 1 cfset randomNumber = GetRandomNumber() cftry cfquery datasource=#ds# SQL STATEMENT /cfquery cfcatch cfif errorIdUniqueConstraintError !--- I don't know how to check that off-hand --- cfset recordInserted = false cfelse cfthrow /cfif /cfcatch /cftry /cfloop -- Blog it up: http://fusion.dominicwatson.co.uk ~| 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:298781 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: Generating unique random numbers
Firstly, the column in the table absoutley *should* have a unique index or constraint on it. Doing that will avoid any racing as the db will make it impossible to insert dupes. With that in mind, your code could look something like: cfset recordInserted = false cfset maxTries = 10 cfset counter = 0 cfloop condition=not recordInserted and counter LT maxTries cfset recordInserted = true cfset counter = counter + 1 cfset randomNumber = GetRandomNumber() cftry cfquery datasource=#ds# SQL STATEMENT /cfquery cfcatch cfif errorIdUniqueConstraintError !--- I don't know how to check that off-hand --- cfset recordInserted = false cfelse cfthrow /cfif /cfcatch /cftry /cfloop ~| 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:298780 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Generating unique random numbers
Bobby has a nice lil udf for that and yeah bobby I USE IT ALL THE DAMN TIME!! haha Is there a slick way to do this in CF? Let's say I have a set of records - one field of which is for a unique and random 6 digit number. When adding a new record, what the best way to assign its random number that's not already assigned to another record? Thanks in advance, Les ~| 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:298754 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Generating unique random numbers
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, 99)# 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,00) 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,99),00); 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
Re: Generating unique random numbers
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, 99)# 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. Keep in mind that your gamut is only a million values, which isn't particularly large. I don't know how many records you expect to store, but if it's more than a couple thousand, I'd consider upping the max value if randomness is at all important. cheers, barneyb On Feb 11, 2008 1:39 PM, Les Irvin [EMAIL PROTECTED] wrote: Is there a slick way to do this in CF? Let's say I have a set of records - one field of which is for a unique and random 6 digit number. When adding a new record, what the best way to assign its random number that's not already assigned to another record? Thanks in advance, Les ~| 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:298749 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: Generating unique random numbers
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, 99)# 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,00) 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,99),00); 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:298753 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Generating unique random numbers
I've done something similar with hex values. I coded a loop that created a hex value, queried the database for a match, exited if one didn't exist and repeated the loop if one did. I then saved the hex value after the loop. On Feb 11, 2008 4:39 PM, Les Irvin [EMAIL PROTECTED] wrote: Is there a slick way to do this in CF? Let's say I have a set of records - one field of which is for a unique and random 6 digit number. When adding a new record, what the best way to assign its random number that's not already assigned to another record? Thanks in advance, Les ~| 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:298748 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: Generating unique random numbers
Umm... I was under the impression that a serializable transaction lock would lock the table. If you omit the isolation, the database may not I've always referred to this article by simon when using transactions. http://www.adobe.com/devnet/coldfusion/articles/cftransaction.html Pretty consistent with my thinking... too bad those devnet articles don't offer the opportunity to post blog-style comments. This was written before the result attribute was added to cfquery, so he mentions the long-time issue of people wanting to get an identity or autonumber value from an inserted record, which used to be done primarily with cftransaction (although scope_identity() was better if you were using a more recent version of SQL Server since @@identity wasn't thread safe)... but it'd be nice to be able to tag that article with a comment that points to the cf8 docs for cfquery to point out that you don't have to futz with all that anymore now that there's a result attribute. -- 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:298765 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Generating unique random numbers
I believe you're correct that a serializable transaction would make it safe, but the loss of concurrency is a steep price to pay when a unique index will assure it for no performance penalty (and probably actually make queries faster, since uniqueness is a good indicator of a field to be used in a WHERE clause). The unique index causes an error if it fails -- you still have to trap the error, which has its own increasingly steep performance price as you approach 1-mil, since the number of errors generated and trapped would climb proportional to the number of records in the table. So the more performant alternative really would be a cflock using the random number as part of the name attribute -- which would work either way, irrespective of whether or not you have a unique index on the table. I dunno... I'm not apt to leap to the conclusion that serializing access to the one table would be so abhorently non-performant. It might be -- might not be -- depends a lot on the frequency of the transaction. In a signup form for example, the frequency of new members on a site is nowhere near say the frequency of new content like for example, photos being uploaded to Flickr. So where they probably wouldn't want to serialize access to the photo table, serializing access to the user/member table during signup to ensure uniqueness of the username would probably not pose a considerable problem. And with a domain of at most a million records, if the records are being created with the kind of speed that say photos get uploaded to Flickr, then he'd run out of available numbers probably in a matter of months. However, in retrospect there's another alternative in CF8 thanks to the result attribute added to cfquery. Syntax may vary or not be available dependent on platform, but SQL Server would allow this: cfloop condition=true cfset z = getRandomNumber() / cfquery result=inserted ... insert into mytable (x,y,z) select #x#, #y#, #z# where not exists (select x from mytable where z = #z#) /cfquery cfif inserted.recordcount !--- exit the loop --- cfbreak / /cfif /cfloop Actually it's been a while since I've even done this in SQL Server, so I'm not certain the syntax is correct. But I have conditionally inserted records in roughly this way before -- the addition of result just lets you determine if it succeeded to make the decision about what to do with the loop. No errors would be thrown and it only executes one query per iteration of the loop (which is likely to be almost twice as fast). Though I can think of a few places I'd worked where I would probably have got in trouble for doing something too advanced if I'd written this particular code, and not because of the infinite loop potential (which in this case would be unavoidable since you'd get closer and closer to an infinite loop as you approached the 1-mil mark). But unless I were working on something that needed maximum performance, I'd probably stick with the serializable lock on that one table because I think it's generally more straightforward. Then rewrite it later if it becomes a bottleneck. -- 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:298763 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Generating unique random numbers
Umm... I was under the impression that a serializable transaction lock would lock the table. If you omit the isolation, the database may not I've always referred to this article by simon when using transactions. http://www.adobe.com/devnet/coldfusion/articles/cftransaction.html Will ~| 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:298762 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Generating unique random numbers
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. Umm... I was under the impression that a serializable transaction lock would lock the table. If you omit the isolation, the database may not necessarily serialize it -- but the whole point of the serializeable isolation *is* to single-thread access to those resources for the purpose of eliminating race conditions. Docs seem to confirm that notion - this is from the CF7 docs for cftransaction: * serializable: places an exclusive lock on every data table in use for the duration of the transaction. Causes access to the database to be single-threaded and is therefore not recommended for normal database access. http://livedocs.adobe.com/coldfusion/7/htmldocs/0346.htm Thing is, I believe according to standard serializable is supposed to be the default (I'm actually not certain of that, but remember Jochem saying something about it)... however, because databases are notorious for being iffy on standards support, etc. it's generally better to explicitly declare the isolation in the cftransaction tag, since the docs say it'll use whatever the default is for the associated database if you omit it. May be okay to omit if you know for example that your company always uses a specific db platform configured a particular way and therefore always means a default isolation of x, but for me, that's still an area where I'd rather just specify it in the tag. This from a guy who likes to use a tool to generate his forms that eliminates the need to specify value attributes. :P -- 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:298760 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: Generating unique random numbers
If you use serializable transactions then you effectively single thread access to everything, which is HORRIBLE for performance because your concurrency goes basically to one. So using serializable transactions as the default is almost never a good idea. I believe you're correct that a serializable transaction would make it safe, but the loss of concurrency is a steep price to pay when a unique index will assure it for no performance penalty (and probably actually make queries faster, since uniqueness is a good indicator of a field to be used in a WHERE clause). cheers, barneyb On Feb 11, 2008 4:43 PM, s. isaac dealey [EMAIL PROTECTED] wrote: 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. Umm... I was under the impression that a serializable transaction lock would lock the table. If you omit the isolation, the database may not necessarily serialize it -- but the whole point of the serializeable isolation *is* to single-thread access to those resources for the purpose of eliminating race conditions. Docs seem to confirm that notion - this is from the CF7 docs for cftransaction: * serializable: places an exclusive lock on every data table in use for the duration of the transaction. Causes access to the database to be single-threaded and is therefore not recommended for normal database access. http://livedocs.adobe.com/coldfusion/7/htmldocs/0346.htm Thing is, I believe according to standard serializable is supposed to be the default (I'm actually not certain of that, but remember Jochem saying something about it)... however, because databases are notorious for being iffy on standards support, etc. it's generally better to explicitly declare the isolation in the cftransaction tag, since the docs say it'll use whatever the default is for the associated database if you omit it. May be okay to omit if you know for example that your company always uses a specific db platform configured a particular way and therefore always means a default isolation of x, but for me, that's still an area where I'd rather just specify it in the tag. This from a guy who likes to use a tool to generate his forms that eliminates the need to specify value attributes. :P -- 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:298761 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
