Is it conceptually right to do a SELECT MAX(id) etc. for finding the last inserted row?

Question

I was reviewing a Drupal module when I found this pattern for getting the id of the row last inserted:

SELECT MAX(id) FROM ... WHERE ...

where the id is a field working in a usual autoincrement way.

Is this conceptually right to do? Is there any situation when this pattern will fail in a MySQL/PostgreSQL environment?

Edit:

Thanks for the excellent comments and answers!

I should clarify my question: I meant a situation where someone would like to find out the id of the last inserted row regardless of the session.

Answer 1

This seems subjective, but I would say no, it's not conceptually right, because:

• you want the most recently inserted row
• but your query looks at the maximum id value

Yes, there is some relationship between max id and most recent insert, but consider the following:

• what if the most recently inserted row was deleted?

Answer on MySQL: you get different results. Note that there doesn't even have to be multithreading or multiple processes for this to fail. That's because they're two different things (which admittedly can often produce the same results).

select max(id) from <tablename>

vs

select last_insert_id()

(Guess which one is right.)

---

@Dems pointed out that the OP is ambiguous. I'll clarify my main point:

We're talking about three different pieces of information:

• maximum id value
• id of row most recently inserted, specific to a session
• id of row most recently inserted into table (regardless of session)

The dangerous thing is that sometimes, querying for one will give the right answer for another -- but not always.

Answer 2

select max(id) would only be guaranteed to retrieve the record with the highest ID. it may not necessarily be the LAST record inserted into the table. Remember that DB operations can be run in parallel. You might insert a record and get ID #5. But by the time you get around to doing the select MAX(id), someone else might have inserted another record and now the max is really #6 instead.

As well, you can't use it to predict what the next assigned ID might be. Consider the case where some inserts are done in a transaction. The last committed record in the DB is #5 again, then 3 inserts are performed (#6, #7, #8), and then they're rolled back. The next insert to be performed will actually be #9, not #6 again - mysql does not recycle ID numbers from failed transactions. And there's no guarantee that YOU would get #9 either - some other session could do the insert before you get around to it, so your fourth insert might be #10, or #10,000,000 instead.