When giving transition rollback, reset id to the last id actually inserted

Question

I want to avoid having jumps in the auto increment of id, in case there’s a mistake and I make it rollback in my transaction. And at the same time I want to make sure that no mistake happens if I change the auto-increment, in case of simultaneous accesses and etc.

Exp:

The best option would be something like this?:

ALTER TABLE sales_order AUTO_INCREMENT =(SELECT MAX(id_sales_order)+1 FROM sales_order)

I wanted to take advantage of the tip and see if there are better practices to save data in a database

I know that from the moment we talk about best practices, the chance of my question being negative and closed is great, but I wanted a suggestion from more experienced users, if possible.

My commented structure:

$id = $_POST["id"];
//Abro a conexão
$db = new PDO('ok');
//Inicio da transação
$db->beginTransaction();
//Seto para pegar qualquer erro de sql
$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$db->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);

try {
    $sql = "INSERT INTO sales_order (total_price) VALUES ($id,0)";
    $stmt = $db->prepare($sql);
    $stmt->execute();
    //Pego o último id inserido
    $lastId = $db->lastInsertId();
    //Gero um hash com esse id utilizando a biblioteca
    $hash_id = generate_hash_id($lastId);
    $sql = "UPDATE sales_order SET hashed_id ='".$hash_id."' WHERE id_sales_order = ".$lastId;
    $stmt = $db->prepare($sql);
    $stmt->execute();
} catch (PDOException $e) {
    if ($e != ""){
        //Seto erro 500 no header da resposta
        header($_SERVER["SERVER_PROTOCOL"]." 500 Internal Server Error");
        //Salvo em um arquivo .txt o log do erro com a data e hora
        date_default_timezone_set( 'America/Sao_Paulo' );
        $filename = 'log-errors.log';
        file_put_contents( $filename, '[START]'.PHP_EOL.date( 'r' ).PHP_EOL.$e.PHP_EOL.'[END]'.PHP_EOL, FILE_APPEND );
        //Realizo rollBack de todas alterações feitas na transação
        $db->rollBack();
        die();
    }
}
//Dou um commit em todas alterações feitas na transação
$db->commit();
echo '{"status":200}';

Library source: Hashids

All