Make a START TRANSACTION not reserve the ID of the tables involved, in case of failure in the insert

Question

Make a START TRANSACTION not reserve the ID of the tables involved, in case of failure in the insert

Navigation

#1 by (2 votes)

1

Hello developer friends,

I set up a transaction script to insert values into 2 different tables, I put a or die at the end of each INSERT so that the data insertion does not occur in the second table , if an error occurs when inserting the first one.

What has bothered me is that even if a data insertion error occurs and the process dies (not entering the data in the database), the table ID is reserved , however, that in a next successful INSERT, the ID is not sequential from the previous record. NOTE: The two tables have auto_increment in ID.

Is there a way to resolve this issue?

Here is an example of my code:

    $mysqli->query('START TRANSACTION') or die($mysqli->error);

    $sql = "INSERT endereco (logradouro, numero, complemento, bairro, cidade, uf) VALUES ('".$logradouro."','".$numero."','".$complemento."','".$bairro."','".$cidade."','".$uf."')"; 
    $query = $mysqli->query($sql) or die($mysqli->error);
    $idEndereco = $mysqli->insert_id;

    $sql = "INSERT contato (email, telefone, telefone_adicional) VALUES ('".$email."','".$telefone."','".$telefoneAdicional."')";
    $query = $mysqli->query($sql) or die($mysqli->error);
    $idContato = $mysqli->insert_id;

    $mysqli->query('COMMIT') or die($mysqli->error);

php mysql mysqli

asked by anonymous 30.05.2017 / 17:46

1 answer

File Transfer Socket c # Click windows form transparent C # [closed]

score 2 · Accepted Answer

This is a feature of AUTO INCREMENT, according to the documentation itself:

"Lost" auto-increment values and sequence gaps
In all lock modes (0, 1, and 2), if a transaction that generated auto-increment values rolls back, those auto-increment values are "lost". Once an auto-increment column is generated, it can not be rolled back, whether or not the "INSERT-like" statement is completed, and whether or not the transaction is rolled back. Such lost values are not reused. Thus, there may be gaps in the values stored in an AUTO_INCREMENT column of a table.

Font

In summary: In any locking mode (0, 1 and 2), if a transaction that generated a auto-increment value fails, those values will be lost. These values can not be reused. Therefore, there may be gaps in the values stored in the AUTO_INCREMENT column of the table.

One that you generate AUTO INCREMENT you can not revert the values already generated, according to the documentation itself, which reduces the chances of this actually being possible. I see absolutely no problem in having gaps, "stop being sequential."