Could PDO::lastInsertId() be inaccurate?

Question

I've got a page calling a script via AJAX which could be asynchronously called multiple times simultaneously from the same parent page. This script uses require_once to access a configuration script with the database handler. Will the script call a unique instance of this handler each time, or if multiple copies are open will they all be using the same instance and causing undesirable results when I attempt to get the ID of the last insert from that instance of the script?

Just to be clear, this is the command I'm referencing:

$DBH->lastInsertId()

Thanks!

this site suggests answers for the trivial questions while you're typing it. Please pay attention to these suggestions — Your Common Sense, Oct 10 '13 at 20:54
Huh. Sorry about that, I was confident I'd checked it as I am typically good about it. — J S, Oct 10 '13 at 21:37

score 3 · Answer 1 · answered Oct 10 '13 at 20:54

3

The documentation says

This method may not return a meaningful or consistent result across different PDO drivers, because the underlying database may not even support the notion of auto-increment fields or sequences.

That being said, I've never had a problem using lastInsertId with MySQL. It's always worked.

This ostensibly wraps a call to MySQL's 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.

i.e. it should be safe to use even if you are facing frequent requests.

answered Oct 10 '13 at 20:54

Explosion Pills

188,624
52
326
405

Reputation points are so sweet, aren't they? – Your Common Sense Oct 10 '13 at 20:55
@YourCommonSense you seem to lack a substantial amount of common sense in spite of your username – Explosion Pills Oct 10 '13 at 20:56
So then it's on a per-connection basis and, more relevantly, each instance of the script will be opening a unique connection. Is that unquestioningly correct? (I'm implementing something on an existing server and if this goes wrong my clients will be deleting each other's content, so I need to be extremely careful.) – J S Oct 10 '13 at 20:57
@JS yes it is; each time you call `new PDO` for example. I know exactly what you are trying to implement and I have used `lastInsertId` in these circumstances before with no problem. If you like, you can easily test it. Update your ajax script to `sleep` for some period of time and hit it twice quickly. You should confirm you get the right IDs – Explosion Pills Oct 10 '13 at 20:58
I'll trust you as you seem rather knowledgeable. I could have tested it of course, but if I'm going to rely on testing I'd want it to be rather more extensive than that and despite working with a rather sensitive/critical infrastructure here I'm also on a major time crunch. Thanks for the help! – J S Oct 10 '13 at 21:01
And I must ask, what exactly do you think I'm implementing? I'm curious how close you are. – J S Oct 10 '13 at 21:01
@JS Not something I'm working on now, but I have worked on it in the past. I still suggest that you test just for your own peace of mind, but I'm quite confident in my answer. – Explosion Pills Oct 10 '13 at 21:03
let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/39023/discussion-between-explosion-pills-and-j-s) – Explosion Pills Oct 10 '13 at 21:03

score 2 · Answer 2 · edited May 23 '17 at 10:33

$DBH->lastInsertId() retrieves, in case of MySQL, the result of last_insert_id() function.

The last_insert_id() is session based, which means that if you have 2 PHP scripts running, you will only get the ones last_insert_id that were generated by each script.

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.

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

score -1 · Answer 3 · answered Oct 10 '13 at 20:54

-1

As noted in the documentation:

Note: This method may not return a meaningful or consistent result across different PDO drivers, because the underlying database may not even support the notion of auto-increment fields or sequences.

answered Oct 10 '13 at 20:54

Matt R. Wilson

7,268
5
32
48

I don't see how that's directly relevant to my question. – J S Oct 10 '13 at 20:54

Could PDO::lastInsertId() be inaccurate?

3 Answers3