Can I perform tasks inside DB transaction and rollback in the middle?

I need to perform 3 inserts in a transaction.

The 3rd transaction is optional, and when it is present, I need to do extra "tasks" before I do that last insert. The tasks are searching in another table for some data and doing some queries to get the data needed for the 3rd insert:

try {
    DB::beginTransaction();    

    $insertId = DB::table('table1')->insertGetId([
       // ...
    ]);

    DB::table('table2')->insert([
       // ...
    ]);

    // Conditional insert:
    if ($request->flag) {
        
        $extra_data = DB::table('some_table')->select(...)->where(...)->get();
       // filter and validate extra data
       // if some data is not found for example, roll back everything like
		
        if ($extra_data->column !== 'x') {
            DB::rollback();
        }

        DB::table('table3')->insert([
            // insert some parts of $extra_data 
        ]);
    }    

    DB::commit();

} catch (\Exception $e) {
    DB::rollback();   
}

So is it possible to do the above - to get optional data for the optional insert, and do some checks before that 3rd insert and do rollback if these conditions aren't met?

LaryAI

3 years ago

Level 58

Yes, it is possible to perform tasks inside a DB transaction and rollback in the middle. Your code example is a good start, but you need to make a few changes to ensure that the transaction is rolled back if the conditions for the optional insert are not met.

Here's an updated version of your code:

try {
    DB::beginTransaction();    

    $insertId = DB::table('table1')->insertGetId([
       // ...
    ]);

    DB::table('table2')->insert([
       // ...
    ]);

    // Conditional insert:
    if ($request->flag) {
        
        $extra_data = DB::table('some_table')->select(...)->where(...)->get();
        // filter and validate extra data
        // if some data is not found for example, roll back everything like
        
        if ($extra_data->column !== 'x') {
            DB::rollback();
            throw new \Exception('Extra data not found');
        }

        DB::table('table3')->insert([
            // insert some parts of $extra_data 
        ]);
    }    

    DB::commit();

} catch (\Exception $e) {
    DB::rollback();   
}

The main change is that we're now throwing an exception if the conditions for the optional insert are not met. This ensures that the transaction is rolled back and an error message is displayed.

Note that you can customize the error message to suit your needs.

Ligonsker

3 years ago

Level 8

This is amazing, I really really think I should start finding a new profession just to be safe, lol

Please or to participate in this conversation.