> > > Beware of doing it this way... You will need cftransaction or some
> other > way of ensuring that the ID is unique. Consider the situation
> where 2 > inserts from 2 different people are happening nearly
> simultaneously. The > second request for the max(ID) could occur
> before the first is > inserted....
ARGH! Why can't anyone design a *good* email client? They all suck!
> Is that definately true with the select insert as I exampled? I
> imagine (but that is all I do, quite willing to be wrong) that the
> single SQL statement must complete before another is allowed to run..?
> I.e. I imagine that this SQL will not run into the problem you
> describe:
>
> INSERT INTO myTable (id, title)
> SELECT Max(id) + 1, 'Some title'
> FROM myTable
As far as I know, at least with SQL Server that's true, the server
snapshots the environment, executes the query and then moves forward, so
in theory, this single-query approach wouldn't have the race condition.
I would expect that also to be true of Oracle, although I don't know for
certain. MySQL I honestly would say "it's up in the air" -- I know MySQL
does a lot of things rather differently than other databases, like
allowing you to specify a different engine for each table, which I
believe was a large part of the reason why it took them so gosh darned
LOOOOOOOOOOOONNNNNNNNNNNNNNNNNNNGGGG to add support for views. Similarly,
their table names were at one time case-sensitive or could be (which was
non-standard behavior). That being the case, I don't like to make any
claims about what I "expect" MySQL to do. :P And similar with any other
databases, most of which I've had no exposure to.
--
s. isaac dealey ^ new epoch
isn't it time for a change?
ph: 503.236.3691
http://onTap.riaforge.org
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
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:295226
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
