Laravel DB::rollback() doesn't work for transaction processes

Question

First off my engine is innoDB and I already tried the following on mySQL:

BEGIN;
INSERT INTO `tbl_users`(...) VALUES (...)
ROLLBACK();

And it works fine, meaning the problem wasn't in my mysql config.

But when I tried this on my Laravel Model:

public static function addNew($request, $department_id) {

    $result = array();

    $now = Carbon::now();

    DB::beginTransaction();

    //Checking for existing Order to set appropriate starting ID
    $result = DB::select("
        SELECT COUNT(`id`) AS 'count'
        FROM `tbl_consignmentorders`
    ")[0];

    if($result->count == 0){
        DB::update("ALTER TABLE `tbl_consignmentorders` AUTO_INCREMENT = 70000000001;");
    }

    try {
        //INSERT
        DB::insert("
            INSERT INTO `tbl_consignmentorders`
            (`from`, `to`, `status`, `created_at`, `updated_at`) 
            VALUES 
            (?, ?, ?, ?, ?)",
            [
                $department_id,
                strtoupper($request->input('supplier')),
                'PENDING',
                $now,
                $now
            ]
        );
        //GET THE LAST ID INSERTED, NEEDED FOR NEXT INSERT
        $last_id = DB::select("
            SELECT 
                LAST_INSERT_ID() AS 'id'
            FROM `tbl_consignmentorders`;"
        )[0]->id;

        //CONSTRUCTING QUERY STRING FOR VALUES
        $values = '';
        $count = 0;
        foreach($request->input('item_id') as $item) {
            $values .= ',(' . $request->input('quantity')[$count] . ', ' . $last_id . ', ' . $item . ', ' . $request->input('item_price_id')[$count] . ' )';
            $count++;
        }
        $values[0] = ' ';

        //INSERT TO DETAILS
        DB::insert("
            INSERT INTO `tbl_consignmentorderdetails`
            (`quantity`, `order_id`, `item_id`, `item_price_id`) 
            VALUES 
            $values;"
        );

        //INSERT TO TRANSACTION AUDIT
        DB::insert("
            INSERT INTO `tbl_transactions`
            (`type`, `reference_id`, `department_id`, `created_at`, `updated_at`) 
            VALUES 
            (?, ?, ?, ?, ?)",
            [
                'CONSIGNMENT ORDER',
                $last_id,
                $department_id,
                $now,
                $now
            ]
        );

        //COMMIT NOTHING FAILS
        DB::commit();
        $result = true;

    } catch (\Exception $e) {
        //ROLLBACK SOMETHING IS WRONG
        DB::rollback();
        $result = $e->getMessage();
    }

    return $result;
}

Now the above code works fine when successful, now to generate error, I will deliberately change this part of code:

    //GET THE LAST ID INSERTED, NEEDED FOR NEXT INSERT
    $last_id = DB::select("
        SELECT 
            LAST_INSERT_ID() AS 'id'
        FROM `tbl_consignmentorders`;"
    )[0]; //<--- I removed the ->id to return the whole object causing object to string error on the next query

Now as expected it goes to the catch block to pass the error message but, the queries executed before the error is still present in the database where it should not.

Answer 1

My mistake,

I use DB::rollback(); instead of DB::rollBack(); with capital B