MySQL and PDO: Could PDO::lastInsertId theoretically fail?

Question

I have been pondering on this for a while.

Consider a web application of huge proportions, where, let's say, millions of SQL queries are performed every second.

I run my code:

$q = $db->prepare('INSERT INTO Table
                 (First,Second,Third,Fourth)
          VALUES (?,?,?,?)');
$q->execute(array($first,$second,$third,$fourth));

Then immediately after, I want to fetch the auto incremented ID of this last query:

$id = $db->lastInsertId();

Is it possible for lastInsertId to fail, i.e. fetch the ID of some SQL insert query that was executed between my two code blocks?

Secondary:

If it can fail, what would be the best way to plug this possible leak?

Would it be safer to create another SQL query to fetch the proper ID from the database, just to be sure?

Answer 1

It will always be safe provided that the PDO implementation is not doing something really bone-headed. The following is from the MySQL information on last_insert_id:

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.

Answer 2

No. lastInsertId is per-connection, and doesn't require a request to the server - mysql always sends it back in its response packet.

So if the execute method doesn't throw an exception, then you are guaranteed to have the right value in lastInsertId.

It won't ever give you the insert ID of anything else, unless your query failed for some reason (e.g. invalid syntax) in which case it might give you the insert ID from the previous one on the same connection. But not anybody else's.