laravel 请问laravel 数据库事务锁表吗？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/47051555/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Will laravel database transaction lock table?
提问by Kris Roofe
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.
我使用 laravel5.5 的数据库交易进行在线支付应用。我有一个company_account表来记录每次付款（type，amount，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 的文档，但我不确定事务是否会锁定表。如果事务将锁定表，那么锁定类型是什么（读锁、写锁或两者）？
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()
采纳答案by Alex Blex
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_accounttable, 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_accounttable 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_idis a primary key inuser_accounttable.- There is at least 1 record in
tiger_account. The one called$old_tiger_accountin 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.
user_id是user_account表中的主键。- 中至少有 1 条记录
tiger_account。$old_tiger_account在 OP 代码中调用的那个，因为当数据库中没有任何内容时，不清楚预期的行为是什么。 - 所有货币字段都是整数，而不是浮点数。
- 它是 MySQL 数据库。我使用 MySQL 语法来说明该方法。其他 SQL 风格的语法可能略有不同。
- 原始查询中的所有表名和列名。不记得照亮命名约定。
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。我相信保证没有竞争条件是一个公平的价格，但我想明确表示它不是免费的。
回答by Kris Roofe
I came across this answerof the question MySQL: Transactions vs Locking Tables, which explain transaction and locking table. It shows both the transaction and locking should used here.
我遇到了问题MySQL: Transactions vs Locking Tables 的这个答案，它解释了事务和锁定表。它显示了此处应使用的事务和锁定。
I refer to Laravel lockforupdate (Pessimistic Locking)and How to pass parameter to Laravel DB::transaction(), then get below code.
我参考了Laravel lockforupdate (Pessimistic Locking)和How to pass parameter to 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);
回答by spirit
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'),
]);
});
