Is last insert id method reliable

Question

The following scenario.

We have 2 scripts, both inserting into table and then using lastInsertId() to get the value from an autoincrement column.

If these 2 scripts are executed in parallel, do we know for sure that they won't mess the results?

In real time:

Time 1: Script 1 -> Insert. (created id = 1)

Time 1: Script 2 -> Insert. (created id = 2)

(database handles this using probably locks / semaphores)

Q1. Time 2: Script 2 -> lastInsertId() returns 1 or 2? Is it deterministic?

Q2. What about sequential inserts?

script.php

$statement1->insert('john'); // id = 1
$statement2->insert('mary'); // id = 2

echo $statement1->lastInsertId();
// is it 1 or 2? Is this also deterministic?

['*If a sequence name was not specified for the name parameter, PDO::lastInsertId() returns a string representing the row ID of the last row that was inserted into the database.*'](http://php.net/manual/en/pdo.lastinsertid.php) — Script47, Oct 10 '18 at 08:50
So if I specify name, I have guaranteed correct result? Also, could you please tell me, if I use transactions, does my question above not matter anymore? — DonJoe, Oct 10 '18 at 08:53
Q2: _Is it 1 or 2_ It will be 2 as the connection only remembers the key of the LAST insert. So you would have to capture the key twice, once after each insert if you need to remember it — RiggsFolly, Oct 10 '18 at 09:03
Q1: The connection remembers the last insert id. So as each script has its own connection each script would remember the INSERT that happened using its comnnection — RiggsFolly, Oct 10 '18 at 09:05
@RiggsFolly Is Q1 answer still true if i'm using persistent connection? — DonJoe, Oct 10 '18 at 09:12

Martin · Answer 1 · 2018-10-10T11:55:18.650

Yes, it is reliable.

lastInsertId() does, as the name suggests, hold the id (primary key) of the last inserted row.

So in regards to Q1: the answer will be 2, since that was the last inserted row.

When it comes to sequential inserts, and you want to "do something" that revolves around the use of lastInsertId(), then you will have to declare lastInsertId() EXACTLY after (this is important) the executed query line. This way you are sure to hold the id that you want to use.

->an insert query is executed
->lastInsertId() is stored into a variable
->variable is used for something
->another insert query is executed
->another lastInsertId is stored into a variable
->variable is used for something.
etc...

the same logic applies to a loop.

You don't necessarily have to store lastInsertId() into a variable, but it makes sense if you are working in PHP and need to use it for multiple purposes. If not, then you can simply make use of it directly in the relevant query. But remember, it must be exactly after the specified insert for the id that you wish to use.

Example of failed logic:

<?php
//I want to get id 1
$statement1->insert('john'); // id = 1
$statement2->insert('mary'); // id = 2
$lastId=$statement1->lastInsertId();
?>

This will be a failed logic, because my intent is to retrieve id 1, but since I am waiting to retrieve my lastInsertId() after statement 2, rather than statement 1, my lastInsertId() will be equal to 2 instead of 1.

Working logic:

<?php
//I want to get id 1
$statement1->insert('john'); // id = 1
$lastId=$statement1->lastInsertId();
//do something with $lastId? (value will be 1)
//get contact info from a theoretical contact info table
$sql="SELECT * FROM tbl_contacts WHERE userId='$lastId'";
$statement2->insert('mary'); // id = 2
$lastId=$statement2->lastInsertId();
//do something with $lastId? (value will be 2)
?>

This logic will work, because I am retrieving the id value(s) that I want, and make use of them before they are overwritten by another id.

You can of course make the variables containing the lastInsertId() value unique, so that they aren't overwritten, and you could then make use of them at any time you wish.

Is last insert id method reliable

1 Answers1