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

I want to avoid having to jump in auto increment of id , in case there is an error and I make rollback in my transaction. And at the same time I want to make sure that there is no error if I change the auto-increment in case of simultaneous access and etc.

Exp:

Thebestoptionwouldbesomethinglikethis:

ALTERTABLEsales_orderAUTO_INCREMENT=(SELECTMAX(id_sales_order)+1FROMsales_order)

Iwantedtotakeadvantageofthecueandfindoutiftherearebetterpracticesforsavingdatatoadatabase

Iknowthatfromthemomentyoutalkaboutbestpractices,thechanceofmyquestionbeingdeniedandclosedisgreat,butIwantedasuggestionfrommoreexperiencedusersifpossible.

Mycommentedoutstructure:

$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