This is perhaps the most-often-asked question in the history of ColdFusion (its either that, or "Golly... what does a CFLOCK do?").  It is also noteworthy because there are so very many wrong answers out there on the web. 

The problem: 
You just added a database record, and you want to be able to immediately refer to it as part of some additional processing.  Since it is typical to use an auto-incrementing numeric field as the primary key for a db record (lets name this field "ID"), this presents a problem, since the database itself generates the value, and you must somehow ask it for the value before you can move on to your next task.  On the surface the solution seems simple, but when you factor in the multi-user nature of your database the situation begins complicating itself immediately.

Part 1:  The Typical Solution (and whats wrong with it)

Lets look at an often-seen (and terribly wrong) way to address this issue:

<cfquery     datasource="#application.myDSN#">    INSERT INTO myFile    (       myName,       myQuest,       myColor    )    VALUES    (       #form.myName#,       #form.myQuest#,       #form.myColor#    ) </cfquery> <cfquery    name="getMyID"    datasource="#application.myDSN#">    SELECT       MAX(ID) as lastID    FROM        myFile </cfquery> <p> The last record's ID was <cfoutput>#getMyID.lastID#</cfoutput> </p>

Figure 1
(always use cfqueryparam, which is omitted here for the sake of simplicity)

Whats so bad about it?  Well, what happens if someone shows up and inserts a record at the same time you do?  How do you know you'll get the ID back to YOUR record, as opposed to the one belonging to that other user?  As unlikely as that may seem, it can and does happen.  So here's the typical solution to that possibility:

<cftransaction> <cfquery     datasource="#application.myDSN#">    INSERT INTO myFile    (       myName,       myQuest,       myColor    )    VALUES    (       #form.myName#,       #form.myQuest#,       #form.myColor#    ) </cfquery> <cfquery    name="getMyID"    datasource="#application.myDSN#">    SELECT       MAX(ID) as lastID    FROM        myFile </cfquery> </cftransaction> <p> The last record's ID was <cfoutput>#getMyID.lastID#</cfoutput> </p>

Figure 2

The idea here is that cftransaction will somehow protect the code above so it cannot be intruded upon by another user.  Unfortunately, this is dead wrong.  CFTRANSACTION does a number of things; one of which is to provide rollback functionality.  That is what developers are providing themselves with the code above.  If the insert succeeds, but the getMyID query fails, then the insert will be rolled back and undone (which would hopefully be dealt with by your try/catch block, that you wrapped aroung the code above).  Thats not exactly the result we were looking for.  There are two ways to address this:

1. Change <cftransaction> to <cftransaction isolation="SERIALIZABLE">
   This will give you what you are looking for with respect to a protected transaction, but there are dangers and penalties.  First of all, not all database platforms (particularly older installations of mySQL) support this option.  Next, this will introduce choke points into your application's performance.  Furthermore, when collisions occur errors will be thrown by your database, which means you will have to write additional retry and re-run code within try/catch blocks that will have to surround this code.  Load test this solution, and use it at your own risk.
    
2. Replace <cftransaction> with <cflock name="ChokePoint" type="EXCLUSIVE" timeout="10">
   I mention this option because it exists, not because its a good idea.  Wrapping your code in a lock like this chokes that block of code down so that only one user can execute it at one time.  With severe consequences to your site's throughput capacity.  If you have just one user at a time performing your task, or a limited number, this method may be sufficient.  However it is by no means a scaleable solution.

If there were such a thing as a bottom line to this topic, it would be that the method above is inherently broken, and the things you do to attempt to fix it will only cause a different set of problems.  So how do we fix it?

Part 2: Platform-Specific Solutions

I'll say straight up that I personally do not like to use any sort of platform-specific answer to this problem.  I will build it up as I gather examples of use on the various online lists and fora.  No matter what, the information in this part will very likely be incomplete.

• Oracle
  Set up and use a sequence.
   
• Microsoft SQL Server
  Use @@identity either within your insert query or a stored procedure
   
• Microsoft Access
  Access also supports @@identity
   
• mySQL
  mySQL has a function called LAST_INSERTED_ID

Part 3: A Platform Independent Solution

I would argue that this is where you should look for a solution to your problem.

• Use a UUID as a 'temporary primary key'.  The field is indexed but not the true primary key of your database.
• Use a standard numeric primary key, and an indexed UUID field.
• Insert the UUID into your database
• Query back for the UUID, take the new ID number and use it as you see fit for further processing

Lets look at the code:

<cfset variables.newUUID=createUUID()> <cfquery     datasource="#application.myDSN#">    INSERT INTO myFile    (       myName,       myQuest,       myColor,       myUUID    )    VALUES    (       '#form.myName#',       '#form.myQuest#',       '#form.myColor#',       '#variables.newUUID#'    ) </cfquery> <cfquery    name="getMyID"    datasource="#application.myDSN#">    SELECT        myfile.ID    FROM        myFile    WHERE       myfile.myUUID='#variables.newUUID#' </cfquery> <p> The last record's ID was <cfoutput>#getMyID.lastID#</cfoutput> </p>

Figure 3

See any choke points?  A zillion users can access this all at once with no detriment to its ability to handle simultaneous access.  Further, if you move from one database platform to another you will need to do nothing to your application to accomodate this move.  I have found in practice over several years that I typically have a use for a UUID on general principles sooner or later in a table record (as salt to season a hash, for example) so while the field may have been added solely for this purpose, it often winds up being used again for other things.