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

Question

Hello fellow developers,

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

What has bothered me is that even if an error occurs in the insertion of the data and the process dies (not entering the data in the database), still table ID is reserved, making a successful next INSERT, the ID is not sequential compared to the previous record. NOTE: Both tables have auto_increment in ID.

Is there any way to solve this problem?

Below 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);

Answer 1

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 a value is generated for an auto-increment column, it cannot be Rolled back, whether or not the "INSERT-like" statement is completed, and whether or not the containing 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.

Source

In short: in any lock mode (0, 1 and 2), if a transaction that generated a value of the auto-increment fail, these values will be lost. These values cannot be reused. Thus, there may be gaps in the values stored in the AUTO_INCREMENT column of the table.

---

Once you generate AUTO INCREMENT you cannot reverse the values already generated, according to the documentation itself, which reduces the chances of this actually being possible. I see absolutely no problem with gaps, "no longer sequential".