Safely Selecting the Last Inserted ID, Part II

Well, as it turns out, this isn’t
an issue at all. I ran two tests. The first followed these steps:

  1. Using a CFQUERY tag, I executed an insert statement.
  2. Used Thread.sleep() to make the page hang for 10 seconds.
  3. Inserted ten additional rows from the command line (using the MySQL command
    line tool).
  4. After the Thread.sleep(), used LAST_INSERT_ID() to get the last inserted ID,
    and displaed it.

I expected the result to be 11, but it was actually 1.  The 10 rows I inserted
between the initial insert and when I selected the last inserted ID had no effect.  I
figured this was because ColdFusion was considered one client, and the command
line administrator was considered a different client, and since the scope of
the last inserted ID is the client/connection, I was protected.  So I ran this
test, instead:

  1. Executed an insert statement in ColdFusion.
  2. Slept for 10 seconds.
  3. Ran a second CFM page that inserted 10 rows while the first page was hung.
  4. After the thread in the original page woke up, selected the last inserted id
    and displayed it.

Keep in mind I was using no transactions or locks since I was trying to get a
wrong result before trying to figure out how to get the correct one, however
the result was once again 1.  As it turns out, no transactions or locks are
needed.  Why?  Apparently because each request gets its own database connection,
and that one connection gets reused for the duration for the request, regardless
of how many database operations you perform.  Other
requests can happen simultanously, of course, but they all get their own connections,
and hense do not affect the first connection’s last inserted ID value.  In
other words, it seems to work exactly how you would want it to!  ColdFusion
does it again!

Disclaimer: This test was run using JRun, CFMX 6.1 (with the
latest updater) and MySQL
version 4.0.18.  Before you rely on this data, you might want to run some tests
yourself, though I will try to get confirmation from the ColdFusion team that
this technique should work across all databases and future versions of ColdFusion.

5 Responses to Safely Selecting the Last Inserted ID, Part II

  1. Hans Omli says:

    Now, how would you do the same using JRun and a POJO?

  2. Pat says:

    I ran into a problem with getting the ID from an inserted record yesterday. our normal process is (SQL 2000)insert ….select scope_identity()we dont use @@identity since if the table has a trigger that does another insert you wont get right id.However, for some reason if that first insert uses this whole process breaks down. somehow using cfqueryparam breaks the scope on the transaction or something.maybe someone could shed some light on why this is ?

  3. Ottawa says:

    I think SQL looks at the process, not at who’s running the query. So if you ran 2 separate CF processes, then it would return the correct IDs.But what happens when you have one connection in multithreaded environment? Like persistent connections in PHP or mod_perl?I am looking for answers myself, I am just giving food for thought.

  4. Joel Foote says:

    Pat, I had the same problem with scope_identity() when executing multiple queries in a single transaction. My code executed in query analyzer returned exactly as you would expect. My code, executed in a CF page, would return the identity for the first query regardless of how many other queries I executed on the page. The best I can deduct is that multiple tags in a tag don’t necessarily execute in serial as you would expect.The solution is to place your “select scope_identity() as XXXXX”. Then, reference “queryName.XXXXX” for the ID of the record just inserted.If anyone can offer some explanation why (or confirm my suspicion), I’d love to know more. In the mean time, hopefully this saves some of you the hours of pulling your hair out that I experienced.

  5. Joel Foote says:

    Let’s try this again (this time, without GT or LT signs anywhere in my response)….I had the same problem with scope_identity() when executing multiple queries nested in a single transaction. My code, executed in query analyzer, returned exactly as you would expect. My code, executed in a CF page, would return the identity for the first query regardless of how many other queries I executed on the page. The best I can deduct is that multiple CFQUERY tags nested in a CFTRANSACTION tag don’t necessarily execute in serial as you would expect.The solution is to place your “select scope_identity() as XXXXX” in the same CFQUERY tags as your insert statement (of course, with a semicolon between the INSERT and SELECT queries). Then, reference “queryName.XXXXX” for the ID of the record just inserted.If anyone can offer some explanation why (or confirm my suspicion), I’d love to know more. In the mean time, hopefully this saves some of you the hours of pulling your hair out that I experienced.