I am using MySQL's LAST_INSERT_ID() to retrieve the auto_increment ID of the last
record I inserted.
I am concerned since I am using 4 persistent connections to the database in the CF
Admin.
If two pages were processed at nearly the same time for two different users, isn't it
possible that one user would execute the insert query, then the other user would
execute the insert query, then the first user would execute the 'select
LAST_INSERT_ID()' and if CF used the same database connection for both of them
(possible under heavy load) the first user would get back THE WRONG ID?!
I avoid this potential problem with SQL Server by issuing the two SQL statements in
the same query:
cfquery name=insert
insert 'a', 'b', 'c' into mytable;
select @@IDENTITY as keyvalue;
/cfquery
But I cannot do this in MySQL since it does not accept two SQL statements in one query:
cfquery name=insert
insert 'a', 'b', 'c' into mytable;
/cfquery
cfquery name=getkey
select LAST_INSERT_ID() as keyvalue;
/cfquery
(Note: if it matters, I am not using InnoDB tables for these)
-Peter Theobald
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Archives: http://www.houseoffusion.com/lists.cfm?link=t:4
Subscription: http://www.houseoffusion.com/lists.cfm?link=s:4
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
This list and all House of Fusion resources hosted by CFHosting.com. The place for
dependable ColdFusion Hosting.
http://www.cfhosting.com
