Please stop using SELECT MAX(id)

I know there are a lot of tutorials that use this technique. I also know there’s a lot of inherited code (in any language) that contains this SQL. I also also know that - years ago - I too was a slave to SELECT MAX(id). But this is the 21st century and you need to stop using it.

The SQL examples shown here comes from part 7.3 of my OOP with ColdFusion Primer.

The Scenario

1. Insert record into database table.

<cfquery name="qCreateContact" datasource="#variables.DSN#">
INSERT INTO CONTACTS (
  FIRST_NAME,
  LAST_NAME
)
VALUES (
  'Adrian',
  'Moreno'
)
</cfquery>

2. Retrieve the ID of the record we just inserted

<cfquery name="qNewContact" datasource="#variables.DSN#">
  SELECT MAX( CONTACT_ID ) AS NEW_ID
</cfquery>
<cfset newContactID = qNewContact.NEW_ID />

3. Insert record into a related table using the ID we just retrieved

to relate it

<cfloop index="x" from="1" to="#arrayLen( categoryIDArray )#">
  <cfquery name="qCreateContactCategory" datasource="#variables.DSN#">
    INSERT INTO CONTACT_CATEGORIES (
      CONTACT_ID,
      CATEGORY_ID
    )
    VALUES (
      #newContactID#,
      #categoryIDArray[x]#
    )
  </cfquery>
</cfloop>

The Problem

Odds are, if you’re using SELECT MAX(id), you’re also NOT running these related queries within a transaction. It’s also very possible you’re not even using a database that is capable of using transactions.

When you’re running this code on your local development box, you won’t ever see a problem with this code. If your application is only ever used by a few users, most likely you’ll never have an issue.

When your user base increases and the number of concurrent requests to the database start to stack up, the MAX(id) you expect will not be the MAX(id) you get back.

Multiple threads and concurrent requests

Picture the gaps between the cfquery tags in each step. Now picture that the queries are cars driving in the same lane down the highway. That lane is your request for a new record in CONTACTS, the new Contact’s ID and the association of that Contact with its related Categories in CONTACT_CATEGORIES.

Now picture there’s a lane next to you with another set of cars. That’s someone else’s Contact creation request. Each lane can only handle so many cars and each individual car runs at a different speed. What happens when all the lanes are full and there are cars ahead and behind you?

You know some jerk is going to swerve in between the cars in the next lane just to try and get where he's going just a little faster.

At some point, a car from another lane is going to jump ahead of or behind the car containing the “SELECT MAX(id)” that should be in your request. The car could be the other lane’s INSERT (car1) or “SELECT MAX(id)” (Car2). It’s even possible that a car on your left and a car on your right jump into your lane at the same time. Now you’re really in trouble.

You have to write your queries so that not only do your cars follow each other in a certain order, you also have to make sure that you use a process that retrieves your new CONTACT_ID and blocks cars in another lane from jumping into yours.

The Solutions

Most modern databases have built-in functionality to allow you to retrieve the most recently created primary key in any table. Depending on your needs, you can even safely create your own primary key.

Here are three common approaches to getting the most recently created Primary Key of a table:

1. Auto-incrementing Primary Key functions

Tables in these databases can be defined to automatically create a new Primary Key as each record is created.

MS-Access

First off, here’s Nine Reasons NOT To Use MS Access To Power A DB-Driven Website. But if you absolutely MUST use it, Bruce Johnson has some code here that will safely get the last inserted ID.

SQL Server through version 7 *

Pretty much the only thing you can use here is @@IDENTITY

Step 2 for SQL Server through version 7

<cfquery name="qNewContact" datasource="#variables.DSN#">
  SELECT @@IDENTITY AS NEW_ID
</cfquery>
<cfset newContactID = qNewContact.NEW_ID />

SQL Server 2000 through current *

You have three choices here.

1. @@IDENTITY: This is an intrinsic variable in SQL Server that contains the Id of the record that was last created on this connection, regardless of the table. (See code above.)

The problem with @@IDENTITY is that if you do an INSERT to TableA and you also have a Trigger that then causes an INSERT to TableB, then the value of @@IDENTITY shifts from the newest ID in TableA to the newest ID in TableB.

2. IDENT_CURRENT('table'): This function returns the ID of the record that was last created in the specified table.</li>

Step 2 for SQL Server 2000 or greater

<cfquery name="qNewContact" datasource="#variables.DSN#">
  SELECT IDENT_CURRENT('CONTACTS') AS NEW_ID
</cfquery>
<cfset newContactID = qNewContact.NEW_ID />

The issue here is that it’s possible by the time you call IDENT_CURRENT(‘CONTACTS’) from your request, another request has inserted a record in that table.

3. SCOPE_IDENTITY(): This variable contains the Id of the last record that was created within the current scope, regardless of the table.

Step 2 for SQL Server 2000 or greater

<cfquery name="qNewContact" datasource="#variables.DSN#">
  SELECT SCOPE_IDENTITY() AS NEW_ID
</cfquery>
<cfset newContactID = qNewContact.NEW_ID />

This seems to be the safest as it disregards IDs created by Triggers or some other process and sticks to the ID created by your connection’s INSERT.

Some info on MS-Access and SQL Server quoted or paraphrased from this post by Bruce Johnson.

MySQL

LAST_INSERT_ID(): From the documentation - “For LAST_INSERT_ID(), the most recently generated ID is maintained in the server on a per-connection basis. It is not changed by another client. It is not even changed if you update another AUTO_INCREMENT column with a non-magic value (that is, a value that is not NULL and not 0). Using LAST_INSERT_ID() and AUTO_INCREMENT columns simultaneously from multiple clients is perfectly valid. Each client will receive the last inserted ID for the last statement that client executed.”

Step 2 for MySQL

<cfquery name="qNewContact" datasource="#variables.DSN#">
  SELECT LAST_INSERT_ID() AS NEW_ID
</cfquery>
<cfset newContactID = qNewContact.NEW_ID />

As a matter of fact, you could even eliminate Step 2 and just use Steps 1 and 3 in this manner:

Removing Step 2 for MySQL

<cfquery name="qCreateContact" datasource="#variables.DSN#">
  INSERT INTO CONTACTS (
    FIRST_NAME,
    LAST_NAME
  )
  VALUES (
    'Adrian',
    'Moreno'
  )
</cfquery>

<cfloop index="x" from="1" to="#arrayLen( categoryIDArray )#">
  <cfquery name="qCreateContactCategory" datasource="#variables.DSN#">
    INSERT INTO CONTACT_CATEGORIES (
      CONTACT_ID,
      CATEGORY_ID
    )
    VALUES (
      LAST_INSERT_ID(),
      #categoryIDArray[x]#
    )
  </cfquery>
</cfloop>

You could do that same for SQL Server by replacing LAST_INSERT_ID() with SCOPE_IDENTITY().

DB2

IDENTITY_VAL_LOCAL(): If your table has been created using an IDENTITY column for its Primary Key, then you can retrieve a new ID using this function.

Step 2 for DB2 when using IDENTITY column

<cfquery name="qNewContact" datasource="#variables.DSN#">
  SELECT IDENTITY_VAL_LOCAL() AS NEW_ID
</cfquery>
<cfset newContactID = qNewContact.NEW_ID />

2. Sequences

Tables in these databases use associated objects called SEQUENCES to manage ID values for their Primary Keys. Sequences don’t necessarily increment by 1 like auto-generated keys. You can also manipulate a sequence on the fly, but that’s another topic. The point is that there are functions which allow you to access the value of a new record’s ID when that ID is based on an associated sequence.

Generic Sequence CREATE statement

CREATE SEQUENCE CONTACT_ID_SEQ
START WITH 1
INCREMENT BY 1

DB2

Some versions of DB2 can use IDENTITY, some can’t. Those that can’t, use SEQUENCES. Using the generic Sequence, here’s how we’d change the INSERT Statement in Step 1:

Step 1 for DB2 using a Sequence

<cfquery name="qCreateContact" datasource="#variables.DSN#">
INSERT INTO CONTACTS (
  CONTACT_ID,
  FIRST_NAME,
  LAST_NAME
)
VALUES (
  NEXT VALUE FOR CONTACT_ID_SEQ,
  'Adrian',
  'Moreno'
)
</cfquery>

This would create a record with CONTACT_ID = 1 since this would be the first record in the table and we started the sequence at 1. So now we need to get the current value of the sequence to associate this new Contact to its Categories.

Step 2 for DB2 using a Sequence

<cfquery name="qNewContact" datasource="#variables.DSN#">
  SELECT PREVIOUS VALUE FOR CONTACT_ID_SEQ AS NEW_ID
</cfquery>
<cfset newContactID = qNewContact.NEW_ID />

The PREVIOUS VALUE expression returns the most recently generated value for the specified sequence for a previous statement within the current application process.

Documentation for Sequences on DB2.

Oracle

Oracle uses the same generic SQL to create a Sequence, but it has a few differences on its attributes. Check the documentation for more info. Sequences work the same with Oracle as they do with DB2, the difference is that Oracle uses a dot syntax to perform operations on them.

Step 1 for Oracle using a Sequence

<cfquery name="qCreateContact" datasource="#variables.DSN#">
  INSERT INTO CONTACTS (
    CONTACT_ID,
    FIRST_NAME,
    LAST_NAME
  )
  VALUES (
    CONTACT_ID_SEQ.NEXTVAL,
    'Adrian',
    'Moreno'
  )
</cfquery>

Both accomplish the same thing: they generate the next value of the sequence and pass it to some SQL operation. Getting the current value of the sequence makes more sense in Oracle than it does in DB2.

Step 2 for Oracle using a Sequence

<cfquery name="qNewContact" datasource="#variables.DSN#">
  SELECT CONTACT_ID_SEQ.CURRVAL AS NEW_ID
</cfquery>
<cfset newContactID = qNewContact.NEW_ID />

Again, we could remove Step 2 and jump straight to Step 3

Bypassing Step 2 in Oracle using a Sequence

<cfloop index="x" from="1" to="#arrayLen( categoryIDArray )#">
  <cfquery name="qCreateContactCategory" datasource="#variables.DSN#">
    INSERT INTO CONTACT_CATEGORIES (
      CONTACT_ID,
      CATEGORY_ID
    )
    VALUES (
      CONTACT_ID_SEQ.CURRVAL,
      #categoryIDArray[x]#
    )
  </cfquery>
</cfloop>

Documentation for Sequences on Oracle.

PostgreSQL

PostgreSQL uses the same generic SQL to create a Sequence, differences in attributes are in the documentation. We can use Sequences in PostgreSQL just as we do with DB2 or Oracle, but PostgreSQL opted to use a function-based syntax. You can also run into case sensitivity issues, so check the documentation for those.

Step 1 for PostgreSQL using a Sequence

<cfquery name="qCreateContact" datasource="#variables.DSN#">
  INSERT INTO CONTACTS (
    CONTACT_ID,
    FIRST_NAME,
    LAST_NAME
  )
  VALUES (
    NEXTVAL('CONTACT_ID_SEQ'),
    'Adrian',
    'Moreno'
  )
</cfquery>

But at least they go with Oracle’s more sensible approach to getting a sequence’s current value:

Step 2 for PostgreSQL using Sequences

<cfquery name="qNewContact" datasource="#variables.DSN#">
  SELECT CURRVAL('CONTACT_ID_SEQ') AS NEW_ID
</cfquery>
<cfset newContactID = qNewContact.NEW_ID />

Documentation for Sequences on PostgreSQL.

Summary

Get the next (new) value of a sequence (Primary Key):

• DB2: NEXT VALUE FOR CONTACT_ID_SEQ
• Oracle: CONTACT_ID_SEQ.NEXTVAL
• PostgreSQL: NEXTVAL('CONTACT_ID_SEQ')

Get the current value of a sequence (Primary Key):

• DB2: PREVIOUS VALUE FOR CONTACT_ID_SEQ
• Oracle: CONTACT_ID_SEQ.CURRVAL
• PostgreSQL: CURRVAL('CONTACT_ID_SEQ')

3. User Defined Value

The final option can be used with any database. The downside is that these keys are usually going to be of a string data type and that could have some impact on indexing and data look ups as your data set grows.

Rather than create some arbitrary string of characters on your own. You would most often opt to use what’s called a UUID, a universally Unique Identifier.

ColdFusion, for example, has a function called CreateUUID()as of version 4.01 that creates a unique 35 character ID. Many other programming languages have similar functions.

So here are our SQL queries using our own User Defined Value for a CONTACT_ID:

Step 1 and 3 using a UUID

<cfset newContactID = createUUID() />

<cfquery name="qCreateContact" datasource="#variables.DSN#">
  INSERT INTO CONTACTS (
    CONTACT_ID,
    FIRST_NAME,
    LAST_NAME
  )
  VALUES (
    #newContactID#,
    'Adrian',
    'Moreno'
  )
</cfquery>

<cfloop index="x" from="1" to="#arrayLen( categoryIDArray )#">
  <cfquery name="qCreateContactCategory" datasource="#variables.DSN#">
    INSERT INTO CONTACT_CATEGORIES (
      CONTACT_ID,
      CATEGORY_ID
    )
    VALUES (
      #newContactID#,
      #categoryIDArray[x]#
    )
  </cfquery>
</cfloop>

Since we already know the value of the new Contact ID, there’s no need for Step 2.

Summary

The examples shown here use separate queries for each step to walk you through the required process for each database. Certainly you can combine these queries under a single connection (single CFQUERY) by separating them with a semi-colon ( ; ).

Even with separate connections for each step, you’ll want to wrap the entire process in a single database transaction to ensure you’re getting the correct new ID and to further insulate this series of related queries from those of another request. You’ll also want to add in a try/catch block to roll back the transaction in case anything goes wrong. If you’re unfamiliar with CFTRANSACTION, CFTRY and CFCATCH, you can read about them here.

Hopefully, this has given you insight on parts of a database that may have gone unseen until now. Trust me, if you poke around a bit, you’ll find more and more reasons to code directly in your database instead of in your application. Getting the newest Primary Key value is only the tip of the iceberg.