Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Will laravel database transaction lock table?

Tags:

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

like image 554
LF00 Avatar asked Nov 01 '17 09:11

LF00


People also ask

Do database transactions lock the table?

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.

Does stored procedure lock table?

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.

How can we avoid deadlock in laravel?

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.

Does MySQL transaction lock?

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.


2 Answers

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.

like image 163
Alex Blex Avatar answered Sep 27 '22 17:09

Alex Blex


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); 
like image 22
LF00 Avatar answered Sep 27 '22 19:09

LF00