Is there a risk in using mysqli_insert_id?

Question

Suppose a website with a relatively high flow. An X user inserts a record into the database at the same time as another Y user inserts another record.

Whereas the procedure of this insertion of user X and user Y data is:

mysqli_query(conexao(),"insert into tabela (coluna) values ($valor)");
//INTERVALO ENTRE AS FUNÇÕES
$id_inserido = mysqli_insert_id(conexao());

What are the possibilities between the mysqli_query and the mysqli_insert_id there is an insertion of another user Y and the mysqli_insert_id user X actually take the id of user Y?

Answer 1

There is no risk of "mixing" ID, by the following

• How it is returned from a column auto increment, he does not repeat.

• Since the function works with the current connection, and each access to a PHP script creates its individual connection (persistent connections should not be used in scripts), connections made by another script will not change this value (as long as you do not mix tasks in parallel on the same page with threads or something like, what is no longer normal to do in PHP).

• All this from your conexao() don’t do anything weird, see considerations at the end.

It’s just important to pay attention to a few things.

• If you enter multiple lines, contrary to what can be imagined, the id returned is the first of them. This was so decided so that in replication environments it is possible to leave the same sequence on different machines with the use of the Insert id.

• Because of mysqli_insert_id() act on the last insertion performed, be sure to call mysqli_insert_id() immediately after the query that generated the value not to be confused.

• The value of the Mysql SQL function LAST_INSERT_ID() always contains the value AUTO_INCREMENT most recently generated, and is not restarted between queries.

In addition, these last two notes deserve a special explanation: If you have more than one query on the same page, be careful to check if the one that needs to return the id gave error or not, and even modified some record of fact, to avoid by mistake catch the id of a query previous.

An example of extra care in more critical operations:

if( mysqli_affected_rows( $con ) > 0 ) { // -1 é erro, 0 é sem inserção
   $last_id = mysqli_insert_id( $con );
} else {
   $last_id = 0;
}

Or more lean:

$last_id = mysqli_affected_rows($con) > 0 ? mysqli_insert_id($con) : 0;

In this way, a mistake was made in the insert, or simply if there was no insert, has no danger of catching the id of a previous operation. But you don’t have to do it all the time, this only makes sense if in the same script has more than one query insertion after the other.

Additional considerations:

mysqli_query(conexao(),"insert into tabela (coluna) values ($valor)");

That one conexao() in your code should probably be a variable. If you have a separate function for this, you have the risk of having several headaches, if the code is not a simple getter.

Probably be better that way:

$con = conexao();
mysqli_query($con,"insert into tabela (coluna) values ($valor)");
$id_inserido = mysqli_insert_id($con);

Another thing: if you need several ids in the same script, better pick up right away to avoid confusion. Not for the danger mentioned in the question, but for the danger of catching the ids in the wrong order in an extended code:

$con = conexao();

mysqli_query($con,"insert into tabela (coluna) values ($valor)");
$id_inserido1 = mysqli_insert_id($con);
// intervalo
mysqli_query($con,"insert into tabela (coluna) values ($valor)");
$id_inserido2 = mysqli_insert_id($con);
// intervalo
mysqli_query($con,"insert into tabela (coluna) values ($valor)");
$id_inserido3 = mysqli_insert_id($con);
// intervalo
//
// Agora usa $id_inserido1, $id_inserido2 e $id_inserido3 como quiser

Manual for PHP:

http://php.net/manual/en/mysqli.insert-id.php

If you prefer to use directly in Mysql;

https://dev.mysql.com/doc/refman/5.5/en/information-functions.html#function_last-Insert-id