Database Transaction on Multiple Query

Question

I'm trying out something new on my end that whenever I have a multiple query that inserts / update. I use database transaction for that matter.

$this->transaction->beginTransaction();

try {

    $trucker_user->update([
        'username'       => $request->getParam('username'),
        'first_name'     => $request->getParam('first_name'),
        'middle_name'    => $request->getParam('middle_name'),
        'last_name'      => $request->getParam('last_name'),
        'contact_number' => $request->getParam('contact_number'),
        'email'          => $request->getParam('email'),
        'status'         => ($trucker_id) ? 1 : $request->getParam('status')
    ]);

    if ($trucker_id === false) {
        $trucker_user->userTrucker()->update([
            'trucker_id' => $request->getParam('trucker_id')
        ]);
    }

    $this->transaction->commit();
} catch(\Exception $e) {
    $this->transaction->rollBack();
    throw $e;
}

So the question is, is it a good practice when I have multiple query that inserts / update I shall be using database transaction for it?

I know it's a good idea to have a transaction when something bad happens it goes rollback. But whenever I use it always in multiple queries isn't a bit kind of overkill?

Stefano Zanini · Accepted Answer · 2017-04-26 13:03:23Z

3

The transaction is a tool designed for guaranteeing consistency. If the first update/insert operation should be rolled back if the second one fails, then the transaction is needed.

In your example, you update a trucker_user's fields and then, under some circumstances, you update also its id. The question you should pose to yourself is "do I want to rollback all the trucker_user updates if the id update fails?"

The answer may be yes either because you have to maintain consistency between the two actions, or because, in case the second operation fails, you have to retry it, and doing everything again is easier than finding where the process was interrupted (so that you can start back from there).

answered Apr 26, 2017 at 13:03

Stefano Zanini

5,9362 gold badges16 silver badges33 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Database Transaction on Multiple Query

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related