2

Is using SELECT Max(ID) FROM table safer than using SELECT last_insert_id(), where they run as 2 separate queries?

I'm concerned that before the last_insert_id() can run, another insert will take place.

Yisroel
  • 8,164
  • 4
  • 26
  • 26

2 Answers2

13

Is using SELECT Max(ID) FROM table safer than using SELECT last_insert_id()

Definitely not, never! LAST_INSERT_ID() exists exactly for the reason you state: Other clients could have made other inserts. LAST_INSERT_ID() always gives you the last inserted ID on the current connection.

mySQL Reference

Unicron
  • 7,275
  • 1
  • 26
  • 19
  • how does connection pooling affect this? – Yisroel Jul 05 '10 at 15:31
  • 1
    Agreed, but remember that you have to be using the same connection, and it must not have been closed and re-opened. You should always run this directly after the INSERT command, before doing anything else - make it a habit, and then when you need the value, you already have it stored in a variable... :) – Dave Rix Jul 05 '10 at 15:33
  • Are you connecting to the db through the command line, or using a language such as PHP? that can make a difference... – Dave Rix Jul 05 '10 at 15:34
3

Using select max(id) is just asking for trouble. It's only a matter of time before two inserts would happen near-simultaneously and you would start to see some craziness in your data.

joeynelson
  • 405
  • 1
  • 3
  • 10
  • 1
    Because of isolation levels, typically SELECT is given higher priority - so `SELECT MAX(id) ...` can occur before an insert. Not good if I can select the value for my insert, just before your insert occurs... – OMG Ponies Jul 05 '10 at 16:04