Is MySQL_Insert_Id Safe to Use

Is mysql_insert_id safe to use?

From the MySQL manual:

The ID that was generated is maintained in the server on a per-connection basis. This means that the value returned by the function to a given client is the first AUTO_INCREMENT value generated for most recent statement affecting an AUTO_INCREMENT column by that client. This value cannot be affected by other clients, even if they generate AUTO_INCREMENT values of their own. This behavior ensures that each client can retrieve its own ID without concern for the activity of other clients, and without the need for locks or transactions.

Short version: it is safe to use.

is there a better way to use mysql_insert_id()

Using the native auto_increment - there is no other way. You need to do the 3 steps you described.

As Dan Bracuk mentioned, you can create a stored proc to do the 3 queries (you can still get the insert id after executing it).

Other possible options are:

• not storing the id in the filename - you can concatenate it later if you want (when selecting)
• using an ad-hoc auto increment instead of the native one - I would not recommend it in this case, but it's possible
• using some sort of UUID instead of auto increment
• generating unique file names using the file system (Marcell Fülöp's answer)

How bad is using SELECT MAX(id) in MYSQL instead of mysql_insert_id() in PHP?

The point is not if potential bad situations are likely. The point is if they are possible. As long as there's a non-trivial probability of the issue occurring, if it's known it should be avoided.

It's not like we're talking about changing a one line function call into a 5000 line monster to deal with a remotely possible edge case. We're talking about actually shortening the call to a more readable, and more correct usage.

I kind of agree with @Mark Baker that there is some performance consideration, but since id is a primary key, the MAX query will be very quick. Sure, the LAST_INSERT_ID() will be faster (since it's just reading from a session variable), but only by a trivial amount.

And you don't need a lot of users for this to occur. All you need is a lot of concurrent requests (not even that many). If the time between the start of the insert and the start of the select is 50 milliseconds (assuming a transaction safe DB engine), then you only need 20 requests per second to start hitting an issue with this consistently. The point is that the window for error is non-trivial. If you say 20 requests per second (which in reality is not a lot), and assuming that the average person visits one page per minute, you're only talking 1200 users. And that's for it to happen regularly. It could happen once with only 2 users.

And right from the MySQL documentation on the subject:

You can generate sequences without calling LAST_INSERT_ID(), but the utility of 
using the function this way is that the ID value is maintained in the server as 
the last automatically generated value. It is multi-user safe because multiple 
clients can issue the UPDATE statement and get their own sequence value with the
SELECT statement (or mysql_insert_id()), without affecting or being affected by 
other clients that generate their own sequence values.

---

Related Topics