MYSQL transaction tutorial Yii2.0 merchant withdrawal function

Preface

I am a PHP programmer who started out as a programmer. So far, not counting the time I spent studying in training courses, I have been writing code for two years. Probably due to work reasons, I have not used MySQL transactions in the past two years. Just yesterday, there was a business about Alipay transfer that had to be completed using MySQL transactions. After a lot of explanations from others, I still didn’t understand what MySQL transactions were. So I started a new round of make-up lessons. If you want to make it in this world, you must pay back the knowledge you owe.

Let me first briefly talk about the business I encountered yesterday. I want to initiate an Alipay withdrawal business on the mobile terminal. I wrote this business in three steps:

first step:

First, for withdrawal, I need to write a withdrawal record in the withdrawal table, and then update the user balance table. These two data records are both pre-withdrawal records, which means that their status is in process.

Step 2:

Then request Alipay's third-party interface;

Step 3:

If the interface request is successful and the user has received the money paid by Alipay, we will update the status of the withdrawal record table and the data of the user balance table.

If the interface request fails and the user does not receive the money transferred to the user by Alipay, the status of the withdrawal record table will be withdrawal failure.

However, if the user initiates a withdrawal, the user's pre-withdrawal business has been carried out, but the third-party Alipay interface request fails, and there is a sudden power outage at this time, the database status cannot be changed, and the result is not what we expected. At this time, MySQL transactions show their value in this kind of business.

MySQL transaction: A series of operations to be performed is called a transaction, and transaction management is to manage these operations to either be fully executed or not executed at all. That is to say, the withdrawal business above must either be executed successfully or unsuccessfully, and there will be no half-executed situation. The purpose of MySQL transactions is to ensure data integrity. However, not all data engines in MySQL support transaction management, only InnoDB supports transaction management.

Transaction management features:

1. Atomicity: The entire operation of a transaction is a whole and cannot be divided. Either all of it succeeds or all of it fails.

2. Consistency: The data in the data table does not change before and after the transaction operation.

3. Isolation: Transaction operations are isolated from each other and are not affected.

4. Persistence: Once the data is submitted, it cannot be changed, and the data table data is permanently changed.

Transaction management operations:

Enable transaction management: After enabling it, the following SQL statements will not be executed immediately and the results will not be written to the table, but will be written to the transaction log.

start transaction;

Rollback operation: Rollback will clear the content written to the transaction log after the transaction management is started, that is, restore to the state before the transaction management is started.

Syntax: rollback;

Note: The rollback operation only rolls back the "written" content, and cannot roll back the ordinary table read select statement.

Transaction commit: write the results of the SQL statement to the data table.

Syntax: commit;

Sample code:

/***
 * Notes: Merchants can withdraw cash from Alipay* @Author: Dang Mengmeng
 * @Date : 2019/7/9 0009 10:01
 * @param $params
 * @return mixed
 * @throws BadRequestHttpException
 */
public function storeWith($params){
 try{
 $payee_account = $params['pay_account'];
 $amount = $params['amount_of_acc'];
 $request = new AlipayFundTransToaccountTransferRequest();
 $connection = Yii::$app->db;
 $transaction = $connection->beginTransaction(); //Start transaction //Write withdrawal record $RES = StoreWith::updateWithData($x_id);
 $financeId = StoreWith::insertLog($params);
 $params['acc_id'] = $financeId;
 $paymentId = StoreWith::insertPaymentLog($params);
 $request->setBizContent("{" .
  "\"out_biz_no\":$orderId," .
  "\"payee_type\":\"ALIPAY_LOGONID\"," .
  "\"payee_account\":$payee_account," .
  "\"amount\":$amount," .
  "\"remark\":\"Profit withdrawal\"" .
  " }");
 $result = $aop->execute($request);
 $responseNode = str_replace(".", "_", $request->getApiMethodName()) . "_response";
 $resultCode = $result->$responseNode->code;
 if(!empty($resultCode) && $resultCode == 10000){
  //Withdrawal successful $status = 1;
  //1. Update the status of the deposited funds data StoreWith::updateWithDataTwo($x_id,$status);
 } else {
  //Withdrawal failed $status = 2;
  StoreWith::updateWithDataTwo($x_id,$status);
 }
 if($resultCode == 10000){
  $transaction->commit(); //Transaction commit}else{
  $transaction->rollBack(); //Execution failed, transaction rollback}
 return $resultCode;
 }catch (\Exception $exception){
 throw new BadRequestHttpException($exception->getMessage());
 }
}

The knowledge debt you owe must always be repaid, it is better to know it early than late! ! !

It’s better to learn early than late, so I’ll give you a song as soon as possible. Hahaha! ! !

Summarize

The above is the full content of this article. I hope that the content of this article will have certain reference learning value for your study or work. Thank you for your support of 123WORDPRESS.COM.

Original link: https://www.cnblogs.com/honely/p/11156929.html