James, CFTransaction doesn't take care of that? Baz
-----Original Message----- From: James Holmes [mailto:[EMAIL PROTECTED] Sent: Sunday, January 08, 2006 8:39 PM To: CF-Talk Subject: Re: Get last inserted ID (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:228804 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
