> In referring to this article:
> http://www.defusion.com/articles/index.cfm?ArticleID=72
>
> The method below of inserting a record is not a
> reliable way to get the corresponding record id
> in Cold Fusion:
>
> <cftransaction>
>
> <cfquery name="my_insert" datasource="whatever">
> INSERT INTO myTable
> ...
> </cfquery>
>
> <cfquery name="get_id" datasource="whatever">
> SELECT MAX(myTable.ID) as new_id
> FROM myTable
> </cfquery>
>
> </cftransaction>
>
> Is this article correct in stating this method does
> not work, even if the code is wrapped in a
> CFTRANSACTION??
Yes, this method doesn't guarantee that you'll retrieve the correct identity
column value. Here's why.
Transactions use locking to prevent problems caused by concurrent requests.
The database locks all the resources used by the transaction until that
transaction has completed; if it fails, the database can then roll back the
changes because it has locked those resources.
Different databases implement locking differently. They have different
scopes, so to speak. For example, some databases lock individual rows.
Others lock not only the row being used, but other rows immediately before
and/or after that row. Some lock entire tables!
In a database that locks individual rows, the above transaction can't
guarantee that you'll get the desired result. The first query inserts a
record, and locks that row. No other rows are locked, so someone else
inserts a row right after that. The second query then retrieves that row's
identity, instead of the one you've inserted. The transaction still
completes, but you get the wrong identity value.
This could even happen in situations where you don't have row-level locking;
that just makes it more likely.
> What is the best, scalable method for retrieving
> the identity field from the database, in the
> anticipation of heavy traffic?
Use stored procedures, or failing that, triggers, to retrieve the identity
column. Within a stored procedure, you can reference the @@IDENTITY variable
(in SQL Server). Within a trigger, you can reference the specific record
inserted using INSERTED.
Dave Watts, CTO, Fig Leaf Software
http://www.figleaf.com/
voice: (202) 797-5496
fax: (202) 797-5444
------------------------------------------------------------------------------
Archives: http://www.eGroups.com/list/cf-talk
To Unsubscribe visit
http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk or send a
message to [EMAIL PROTECTED] with 'unsubscribe' in the body.
