> 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
