Safely Selecting the Last Inserted ID

I run into this issue occasionally, and always find some kind of work-around,
but I’ve finally decided to address it head-on. The scenario is this:

  1. You insert
    a record into the database.
  2. You need the ID of the row you just inserted so you can insert additional
    rows in other tables referencing the first row you inserted.
  3. You’re worried about your code being thread-safe, so you consider
    using locks, transactions, UUIDs, etc. But what is the best way?

The easiest and most database-independent way of doing this is to generate a UUID
and use it for the primary key for the first insert, and the foreign key for any
subsequent inserts. That’s fine for certain apps, but for the app I’m working on
now, I need to use sequential, auto incremented integers as keys. I’m using the
MySQL function LAST_INSERT_ID() to get the last inserted ID, however the ID’s scope
is the current connection, so if I use two CFQUERY tags (one to insert the row,
and one to get the last inserted ID), I believe it’s possible I could get the wrong
ID if another row is inserted between the time I do my insert and the time I select
the last inserted ID. So I’m going to try:

  1. Sending two queries to MySQL at the same time. One to do the insert, and one
    to do the select. Both statements will use the same connection, and problem
    solved. I haven’t had any luck with this so far, though. I’m not sure MySQL supports
    this as I keep getting SQL syntax errors.
  2. Putting both CFQUERY tags in a transaction (they actually already are in a
    transaction), and using the isolation level of serializable. I’m potentially
    sacrificing some performance, but it should be miniscule. This is a very
    good solution if it works, but I’m not 100% positive that it will work, though
    I am hopeful.
  3. Using an exclusive lock. If all else fails, this will definitely work, although
    the disadvantage is that I will only be taking database operations that
    originate from one CF server into account. In other words, another server that
    is unaffected by the lock can slip a new row into the database at just the right
    moment and trip me up. I would much rather the lock occur at a lower level — actually
    in the database, if possible.

I’ll post my results. In the meantime, anyone have any additional insight?

9 Responses to Safely Selecting the Last Inserted ID

  1. Sjur says:

    How about this (if you really need sequential, auto incremented integer keys):Use an auto inc int key, but also add a UUID column to your table. After inserting, use the UUID to get the int key. Of course this creates some data overhead in the database…

  2. Mark Murphy says:

    This is not graceful but….you can have another table that has one row, one field that stores the next ID. If you start a transaction, increment the counter, select the counter then close the transaction, you may be able to get unique values. In SQL Server this works because once you use UPDATE you are locking that particular row for the duration of the transaction. Maybe mysql is similar.

  3. Nolan says:

    Regarding Mark Murphy’s above comment, it’s actually more graceful than it seems, especially if you do a lot of migrating data between databases. Trying to fake your way around the auto_increment features of a database’s primary key system can be next to impossible, if not impossible all together. If you do lots of moving around, or have many different Dev databases that need to be merged eventually (say, Developers at satellite offices all working independently), this might be a good idea. It’s also pretty thread safe, etc. I know of at least 2 web shops that use this method on their production servers.Hope this helps.Nolan

  4. M. Schopman says:

    Use a transaction and select the @identity 🙂

  5. I think @identiry is SQL Server specific, right? And what kind of transaction? That’s what I’m trying to pin down, and it turns out, you might not need a transaction at all (reed the next post).Christian

  6. @@identity does also work with MySQL, but what I was trying to figure out was whether transactions and/or locks were needed. Turns out if you use transactions and @@identity, you will likely be ok, at least according to my tests.

  7. Ben Forta says:

    There is another solution, and one that (where supported) is far safer. Use a trigger on the INSERT action so that every INSERT operation returns a single row with a single column containing your new id.

  8. Stacy Young says:

    In SQL Server I think you’d want to use: (if not using a trigger)SELECT order_id = SCOPE_IDENTITY()

  9. Matt Webster says:

    24.1.14.1. How to Get the Value of an AUTO_INCREMENT Column in ODBCA common problem is how to get the value of an automatically generated ID from an INSERT statement. With ODBC, you can do something like this (assuming that auto is an AUTO_INCREMENT field):INSERT INTO tbl (auto,text) VALUES(NULL,’text’);SELECT LAST_INSERT_ID();Or, if you are just going to insert the ID into another table, you can do this:INSERT INTO tbl (auto,text) VALUES(NULL,’text’);INSERT INTO tbl2 (id,text) VALUES(LAST_INSERT_ID(),’text’);See Section 23.2.13.3, “How to Get the Unique ID for the Last Inserted Rowâ€?.For the benefit of some ODBC applications (at least Delphi and Access), the following query can be used to find a newly inserted row:SELECT * FROM tbl WHERE auto IS NULL;