yes, this topic has been beaten to death. All posts here have links at the bottom, one of which is to the archives http://www.houseoffusion.com/cf_lists/threads.cfm/4
With Oracle all you need is a sequence defined and use it. No trans is needed, the sequence always gives a unique value. other RDBSs should be talked about in the archives. DK On 1/8/06, James Holmes <[EMAIL PROTECTED]> wrote: > (This is based on Oracle defaults; ymmv with MySql). It's essentially > what Mike said, but only if the other insert is committed first. It's > like this: > > 1) My transaction is started > 2) My INSERT is done > 3) Someone else's transaction starts > 4) Their INSERT is done > 5) Their SELECT is done > 6) Their transaction ends and is committed > 7) My SELECT is done (and I get the wrong ID). > 8) My transaction ends and is committed. > > It's unlikely, since both transactions are doing the same thing and if > I start first I should finish first, but it can happen. Make this > serializable and it can't happen (but then you risk "transaction can't > be serialized" errors and performance decreases). > > The best way is to select the ID first, from a sequence or some other > thing that guarantees a unique value (this is why a UUID is so good > for this) and use that in the insert. It can be done in a Stored Proc > to keep it all within the DB if you like. > > You can google the CF-Talk archives from the site. > > On 1/9/06, Mike Soultanian <[EMAIL PROTECTED]> wrote: > > What if someone were to insert another city just before the SELECT > > statement executed? > > > > Mike > > > > Baz wrote: > > > James, in which cases is it not 100% reliable? Are the CF-Talk archives > > > searchable? > > > > > > > > > >> On 1/9/06, Baz <[EMAIL PROTECTED]> wrote: > > >>> Is this the best way to do this: > > >>> > > >>> <cftransaction> > > >>> <cfquery name="InsertAddress" datasource="#DSN#"> > > >>> INSERT INTO Address (City) > > >>> VALUES (<cfqueryparam value="#City#" cfsqltype="cf_sql_integer" />) > > >>> </cfquery> > > >>> > > >>> <cfquery name="getInsertedID" datasource="#DSN#"> > > >>> SELECT MAX(AddressID) as MaxID > > >>> FROM Address > > >>> </cfquery> > > >>> </cftransaction> > > >>> > > >>> I'm using MySQL. > > >> -- > > >> CFAJAX docs and other useful articles: > > >> http://jr-holmes.coldfusionjournal.com/ > > >> > > >> > > > > > > > > > > > > > > > > > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:228796 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=11502.10531.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
