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?
Yes it is reliable.
If you think that a table is going to have hundreds or thousands of insert per second, consider to not use indexes or use the minimum amount of indexes. In case of MySQL favour MyISAM tables.
In your case,
Q1. Time 2: Script 2 -> lastInsertId() returns 1 or 2? Is it deterministic?
return 2nd query's id.
Q2. What about sequential inserts?
you've to make sure to ask the lastInsertId just after the insert query.
I hope it will help you.
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.