Laravel Transactions Not working when this condition happenes

Let me start with my code

On my Controller file this is the code

namespace SomethingSomewhereController{
class Mobile extends Controller{
    public function saveMobilesIntoDb(Request $request,MediaManager $manager){
        $requestMobileData = $request->all();
        DB::beginTransaction();
        try{
            /*Do something*/
            ..
            ..
            $bigMediaArray = $manager->mobileImagesManager($media,$insertedMobile,$mediaSlug);
            ..
            ..
            DB::commit();
        }catch (Exception $exception) {
            DB::rollback();
            dump($exception);
        }
    }
}

}

Notice I am using a service there, The service is nothing but a namespace to manage the code in the service class this what happening

namespace SomethingSomewhereService{

class MediaManager{

    public function mobileImagesManager($media, $mobileId, $slug){
        //Do some stuff
        ///Create folder
        return array;
    }
}

Now the issue is when I get some error in the service and I resend the data then suppose the last id inserted into database was 5 and then the error came but didn’t rolled back so it saved the new id with 7 . and I don’t want this to happen. I know the rollback is not working when I am not in the scope but what I tried so far is

I wrapped the service function into try-catch and in the catch I used the DB::rollback() but it didn’t helped.

Please let me know how do I solve it and rollback everything when I am not in the scope.

Thank you for you time

Answer

As Alex said, due to Mysql official docs, the auto incremented ID will not rollback after transaction failure.

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.