Using Laravel as my backend, I want to add data to my database via a controller. To do this I need to do two things:

1. Verify the data and insert the data into tableA using function A. Function A returns the id received

2. Update that the user submitted the data in tableB using function B. tableB is related to tableA via tableA->id.

I would prefer the two steps above to run inside a DB transaction. For example if there is error in updating tableB, tableA should ideally be rolled back. I think that makes the most sense?

However, if functionA was unneccessarily long because it needs to verify the data, for example here:

public function A($data) {
    // get data constraints from tableC (a get query)
    // verify data against constraints stated in tableC (assume this step is long)
    // insert data into tableA
    // return row Id.
}

public function B($userId, $rowId) {
    // update tableB where userId = $userId with $rowId.
}

public function controller(Request $request) {
    DB::transaction(function() use ($request) {
        $newId = A($request->data);
        B($request->userId, $newId);
    });
}

From what I understand, this is not ideal. Because function A would take up transaction time doing other things such as querying table and doing loops to verify the data.

This results in unnecessary amount of work done being enclosed in the DB::transaction.

I am wondering is this really a bad thing? Is DB::transaction implemented in a way that it can optimize any table locking?

I won't be able to change the structure of the logic easily because function A and function B belongs to two different classes.

Edit: I am actually able to change the logic if I really want to, however for now I am more interested in if this is actually an issue using DB::transaction.

Because right now I am not even sure if DB::transaction locks the table for the entire function.

CodePudding user response：

DB::transaction does not lock the table. It does only when there is an exception throwed inside the block, it rolls back the changes.

For more information: https://laravel.com/docs/9.x/database#database-transactions

If you want to lock the table you can use the answer from this question: How to lock table with Laravel?

DB::raw('LOCK TABLES important_table WRITE');