Will laravel database transaction lock table?

Question

I use laravel5.5's database transaction for online payment application. I have a company_account table to record each payment(type, amount, create_at, gross_income). I need to access the last record's gross_income, when a new record created. So I need to lock the table when the transaction with read and write table lock to avoid many payments at the same time.

I've refer to laravel's doc, but I don't sure if the transaction will lock the table. If the transaction will lock the table, what's the lock type(read lock, write lock or both)?

DB::transaction(function () {
    // create company_account record

    // create use_account record
}, 5);

---

Code:

DB::transaction(function ($model) use($model) {
    $model = Model::find($order->product_id);
    $user = $model->user;

    // **update** use_account record
    try {
        $user_account = User_account::find($user->id);
    } catch (Exception $e){
        $user_account = new User_account;
        $user_account->user_id  = $user->id;
        $user_account->earnings = 0;
        $user_account->balance  = 0;
    }
    $user_account->earnings += $order->fee * self::USER_COMMISION_RATIO;
    $user_account->balance += $order->fee * self::USER_COMMISION_RATIO;
    $user_account->save();

    // **create** company_account record
    $old_tiger_account = Tiger_account::latest('id')->first();

    $tiger_account = new Tiger_account;
    $tiger_account->type = 'model';
    $tiger_account->order_id = $order->id;
    $tiger_account->user_id = $user->id;
    $tiger_account->profit = $order->fee;
    $tiger_account->payment = 0;
    $tiger_account->gross_income = $old_tiger_account-> gross_income + $order->fee;
    $tiger_account->save();
}, 5);

references:
 How to pass parameter to Laravel DB::transaction()

Solution 1

Since you are updating 2 tables, you still need to use transaction to keep changes in sync. Consider the following code:

DB::transaction(function () {
    $model = Model::find($order->product_id);
    $user = $model->user();

    DB::insert("
        insert into user_account (user_id, earnings, balance) values (?, ?, ?)
        on duplicate key update
        earnings = earnings + values(earnings),
        balance = balance + values(balance)
    ", [$user->id, $order->fee * self::USER_COMMISION_RATIO, $order->fee * self::USER_COMMISION_RATIO]);

    DB::insert(sprintf("
        insert into tiger_account (`type`, order_id, user_id, profit, payment, gross_income)
            select '%s' as `type`, %d as order_id, %d as user_id, %d as profit, %d as payment, gross_income + %d as gross_income
            from tiger_account
            order by id desc
            limit 1
    ", "model", $order->id, $user->id, $order->fee, 0, $order->fee));

}, 5);

There are 2 atomic queries. First one upsert a record into user_account table, another one insert a record into tiger_account.

You need the transaction to guarantee that no changes are applied if something terrible happened between these 2 queries. The terrible thing is not a concurrent request, but a sudden death of the php application, network partition, or anything else that prevents second query to be executed. In this case changes from the first query rolled back, so the database remain in consistent state.

Both queries are atomic, which guarantee the math in each query is done in isolation, and no other queries change the table at this time. Saying that it is possible that 2 concurrent requests process 2 payments for the same user at the same time. The first one will insert or update a record in the user_account table and the second query will update the record, both will add a record to the tiger_account, and all changes will permanently set in the db when each transaction is committed.

Few assumptions I made:

• user_id is a primary key in user_account table.
• There is at least 1 record in tiger_account. The one called $old_tiger_account in the OP code, as it is not clear what's expected behaviour when there is nothing in the db.
• All money fields are integers, not floats.
• It is MySQL DB. I use MySQL syntax to illustrate the approach. Other SQL flavours may have slightly different syntax.
• All table names and column names in the raw queries. Don't remember illuminate naming conventions.

A word of warning. These are raw queries. You should take extra care on refactoring models in the future, and write few more integration tests, as some application logic shifted from imperative PHP to declarative SQL. I believe it is a fair price to guarantee no race conditions, yet I want to make it crystal clear it does not come for free.

Solution 2

I came across this answer of the question MySQL: Transactions vs Locking Tables, which explain transaction and locking table. It shows both the transaction and locking should used here.

I refer to Laravel lockforupdate (Pessimistic Locking) and How to pass parameter to Laravel DB::transaction(), then get below code.

I don't know if it's a well implementation, at least it works now.

DB::transaction(function ($order) use($order) {
    if($order->product_name == 'model')
    {
        $model = Model::find($order->product_id);
        $user = $model->user;

        $user_account = User_account::where('user_id', $user->id)->lockForUpdate()->first();

        if(!$user_account)
        {
            $user_account = new User_account;
            $user_account->user_id  = $user->id;
            $user_account->earnings = 0;
            $user_account->balance  = 0;
        }

        $user_account->earnings += $order->fee * self::USER_COMMISION_RATIO;
        $user_account->balance += $order->fee * self::USER_COMMISION_RATIO;
        $user_account->save();

        $old_tiger_account = Tiger_account::latest('id')->lockForUpdate()->first();
        $tiger_account = new Tiger_account;
        $tiger_account->type = 'model';
        $tiger_account->order_id = $order->id;
        $tiger_account->user_id = $user->id;
        $tiger_account->profit = $order->fee;              
        $tiger_account->payment = 0;

        if($old_tiger_account)
        {
            $tiger_account->gross_income = $old_tiger_account->gross_income + $order->fee;
        } else{
            $tiger_account->gross_income = $order->fee;
        }

        $tiger_account->save();
    }
}, 3);

Solution 3

In my opinion, if you calculate the gross income on-the-fly for each record, separately, you don't even need to lock the table, you know locking a table will directly slow down your website.

DB::transaction(function () use($order) {
    $model = Model::find($order->product_id);
    $user = $model->user;

    // **update** use_account record
    try {
        $user_account = User_account::find($user->id);
    } catch (Exception $e){
        $user_account = new User_account;
        $user_account->user_id  = $user->id;
        $user_account->earnings = 0;
        $user_account->balance  = 0;
    }
    $user_account->earnings += $order->fee * self::USER_COMMISION_RATIO;
    $user_account->balance += $order->fee * self::USER_COMMISION_RATIO;
    $user_account->save();

    // **create** company_account record
    $tiger_account = Tiger_account::create([
        'type' => 'model',
        'order_id' => $order->id,
        'user_id' => $user->id,
        'profit' => $order->fee,
        'payment' => 0,
    ]);

    $tiger_account->update([
        'gross_income' => Tiger_account::where('id', '<=', $tiger_account->id)->sum('fee'),
    ]);
});

Comments

[Alex Blex]

Transactions and locks are 2 different things built for different purposes. You don't need locking, nor a restriction of a single payment at a time. Please read stackoverflow.com/questions/4226766/… and alike for examples of how transactions and rollbacks work.

[LF00]

@AlexBlex Your link is helpful. But I think I need to use lock(read and write) and transaction both, For in the transaction I will access the last record's gross_income then calculate the current gross_income for the new record. Am I here get wrong way.

[Dharma Saputra]

I found an answer that might be good to discuss, stackoverflow.com/questions/3106737/…. It's explain that there are guarante of no another user changes while we in the middle of transaction.

[Alex Blex]

If it is within the same table, it is very likely you don't need neither locks nor transactions. A single atomic update can be sufficient. Am happy to give a full answer with examples if you update the question with table schema and the queries you are trying to do within the transaction.

[LF00]

@AlexBlex I've updated my question with code sample.

[LF00]

I should access last record's gross_income column to calculate for the new record. If I don't lock the table, there may be multi payment at the sametime. This will cause wrong gross_income calculation.

[Alex Blex]

Yes, this should work. Locking whole tables is not particularly performant, but it does exactly what you asked in the question. The only benefit I see is you stay away from raw queries, which can justify loss in performance.

[spirit]

@KrisRoofe as i found in your code, the gross_income of each record is it's fee + previous record's gross_income. so it means each record's gross_income = sum of all fee's up to the current record. If it's correct then look at my answer again, you don't really need to lock table for read. Furthermore, locking the table for read is really a performance issue. If you're planning to start a medium or large service, you should never lock tables (specially read lock) for a repeating action. There is always an alternative solution.

[LF00]

@ I agree with you here.

[reza]

thanks for the answer..., but why does LF00 need refactoring models in the future?

[Alex Blex]

@reza because things change and software need to catch up. Even such basic program as cat reached version 8 after almost 50 years in service. SQL models tend to decay much faster.