将laravel数据库事务锁定表

I use laravel5.5's database transaction for online payment applicaiton. 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.

我使用laravel5.5的数据库交易进行在线支付申请。我有一个company_account表来记录每笔付款（类型，金额，create_at，gross_income）。创建新记录时，我需要访问最后一条记录的gross_income。因此我需要在具有读写表锁的事务时锁定表，以避免同时进行多次付款。

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)?

我已经参考了laravel的doc，但我不确定该事务是否会锁定表。如果事务将锁定表，那么什么是锁类型（读锁定，写锁定或两者）？

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()

参考：如何将参数传递给Laravel DB :: transaction（）

3 个解决方案

#1

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

由于您要更新2个表，因此仍需要使用事务来保持同步更改。请考虑以下代码：

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.

有2个原子查询。第一个将记录置于user_account表中，另一个将记录插入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.

如果这两个查询之间发生了可怕的事情，您需要该事务以保证不会应用任何更改。可怕的事情不是并发请求，而是php应用程序，网络分区或其他任何阻止执行第二个查询的事件的突然死亡。在这种情况下，从第一个查询的更改回滚，因此数据库保持一致状态。

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.

这两个查询都是原子的，这可以保证每个查询中的数学运算是孤立完成的，此时没有其他查询更改表。说2个并发请求可能同时处理同一用户的2次付款。第一个将在user_account表中插入或更新记录，第二个查询将更新记录，两者都将向tiger_account添加记录，并且每个事务提交时所有更改将在db中永久设置。

Few assumptions I made:

我做的几个假设：

user_id is a primary key in user_account table.
user_id是user_account表中的主键。
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.
tiger_account中至少有1条记录。在OP代码中调用了$ old_tiger_account，因为当数据库中没有任何内容时，不清楚预期的行为是什么。
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.
这是MySQL DB。我用MySQL语法来说明这种方法。其他SQL风格的语法可能略有不同。
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.

一句警告。这些是原始查询。你应该特别注意将来重构模型，并编写更多的集成测试，因为一些应用程序逻辑从命令式PHP转移到声明式SQL。我认为保证没有竞争条件是合理的价格，但我想说清楚它不是免费的。

#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.

我遇到了MySQL问题的回答：事务与锁定表，它解释了事务和锁定表。它显示了此处应使用的事务和锁定。

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

我指的是Laravel lockforupdate（Pessimistic Locking）和如何将参数传递给Laravel DB :: transaction（），然后得到下面的代码。

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);

#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'),
    ]);
});

#1