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()
LOCK IN SHARE MODE inside a transaction, as you said, since normally SELECTs, no matter whether they are in a transaction or not, will not lock a table. Which one you choose would depend on whether you want other transactions to be able to read that row while your transaction is in progress.
In contrast, stored procedures do not acquire table-level locks. All statements executed within stored procedures are written to the binary log, even for statement-based binary logging.
runQueryCallback runs all the db executions(selects, inserts, updates). So,if it catches QueryException,as it does,and check if it is error4001 it repeat the command that would theoretically prevent deadlock.
The MySQL locks are taken at the same time as those acquired explicitly with the LOCK TABLES statement. If a table is locked explicitly for reading with LOCK TABLES but needs to be locked for writing since it might be modified within a trigger, a write lock might be taken instead of a read lock.
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.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.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.
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);
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With