MySQL pessimistic lock processing Giveaway inventory oversold situation

Deal with inventory oversold situation, first understand what is optimistic lock and pessimistic lock, the following several blog has been introduced more detailed, I will not repeat its principle

"MySQL" pessimistic Lock & optimistic lock

understanding of MySQL optimistic lock, pessimistic lock, shared lock, exclusive lock, row lock, table lock concept

The application of pessimistic lock in practice is introduced below.

Place Order

..........

Try {
 M ()-starttrans();     
//Judging whether a product has a giveaway$give _gift= $obgetgivegoods ($sku _nos); if(!empty ($give _gift)) {                            $ This-Dealskugift($give _gift, $ob, $data ['Order_no'], $SKUNUMARR, $packageSku); }
}Catch(flexception$ex) {
M ()Rollback();
 $curCode =  $ex ->geterrorcode  () ; 
   $curmsg  =  $ex ->getmessage  () ; 
   E ( $curCode   $curmsg ) ; 
 } 
  m ()->  commit    ()  ;           
..........

// Get gift Stock

 Publicfunction getgivegoodsstock($sku _no) {$sku _no=implode (",", Array_unique ($sku _no)); $sku _no="'". Str_replace (",","', '", $sku _no)."'"; $Model=New\think\model (); $sku _gift_info= $Model->query (" Select Sku_no,stock from ". C ('db_prefix'). " Sku_gift_stock where is_deleted=0 and Is_online=1 and Sku_no in ($sku _no) for update "); return$sku _gift_info;}

// Handling Giveaway

Privatefunction dealskugift($give _gift, $ob, $orderNo, $SKUNUMARR, $packageSku) {if(Empty ($give _gift) or empty ($SKUNUMARR)) {return false; } $skuGiftStockM=m ('Sku_gift_stock'); $sku _gifts=array_column ($give _gift,'Sku_no'); //Get gift Stock$sku _give_gift= $obGetgivegoodsstock($sku _gifts,1); $skuGiftStock=Array (); $giftCount=0; foreach($sku _give_gift as$k =$v) {$skuGiftStock [$v ['Sku_no']]= $v ['Stock']; }    $ Public=NewPmodel (); foreach($give _gift as$v) {$goodsNums=0; $delStockWhere=Array (); if($v ['Ishavestock']=='1'&& $skuGiftStock [$v ['Sku_no']] >0){//on behalf of the goods--these are to be shipped              if($v ['type']=='1') {$lastGiftNum= $SKUNUMARR [$v ['Parent_sku_no']]* $v ['goods_nums']; }Else{//by number of fixed premiums$lastGiftNum = $v ['goods_nums']; }              if($lastGiftNum >= $v ['Stock_num']) {$lastGiftNum= $v ['Stock_num']; }              if($v ['Stock_num']==0) {$lastGiftNum=0; } $delStockWhere ['Sku_no']= $v ['Sku_no']; $delStockWhere ['is_deleted']=0; $delStockWhere ['Is_online']=1;$delStockWhere [' _string ']= sprintf (' stock>=%d ', $lastGiftNum);$skuGiftStockMwhere($delStockWhere)Lock(true)->setdec ('Stock', $lastGiftNum);//Deduct Merchandise Gift stock$re _v=$ Public->updatasigndata ($v ['Sku_no'], $lastGiftNum,'virtual_inventory','-','Goods_sku','Sku_no');//Deduction of Gift Merchandise inventory              if(false==$re _v) {E ('300110'); } $giftCount+=$lastGiftNum; }Else{$lastGiftNum=0; } $DD []=Array ('Parent_sku_no'= = $v ['Parent_sku_no'],              'Sku_no'= = $v ['Sku_no'],              'Num'= $lastGiftNum,//number of final shipments, 0 in stock              'Create_time'=>date ('y-m-d h:i:s', Time ()),'Order_no'=$orderNo,'package_id'= = $packageSku [$v ['Parent_sku_no']],              'Stock_num'=>empty ($skuGiftStock [$v ['Sku_no']])?0: $skuGiftStock [$v ['Sku_no']]         ); } $order _goods_gift=m ('Order_goods_gift'); $order _goods_gift-AddAll ($DD); $upOrdeData=Array ('Gift_count'= $giftCount +1    ); M ('Order'),where('order_no= "'. $orderNo.'"')->data ($upOrdeData)Save (); return true;
}

Interface access mode

Because of the interface to test the oversold processing situation, so before the interface, the body can be written in the parameters of the interface, so that convenient debugging

Access the interface again

The next single interface can be tested with the Apache AB tool.

View merchandise in the database p002026-01 associated with 2 giveaways, each associated with 10

View the number of gifts in stock

The principle of Apache concurrency test and how to use it See blog: https://www.cnblogs.com/lishuyi/p/5808661.html

How to use:

Ab-n 105  http://(-N makes 10 requests,- http:// 

First forecast, request 10 times, concurrent 5, the final inventory will remain 0

The following begins the concurrency test

The remaining stock of the final giveaway P002872, P002962 is as follows

Results can be seen, the pessimistic lock up the effect, the following look at the situation is not pessimistic lock what will be, the following will be 2 gifts of inventory restored to 10, and will get the gift inventory code for the transformation, this time to get the gift inventory is not added pessimistic lock

//Get gift Stock Publicfunction Getgivegoodsstock ($sku _no, $ori) {$sku _gift=m ('Sku_gift_stock'); $sgg _where['Sku_no']=array ('inch', $sku _no); $sgg _where['is_deleted']=0; $sgg _where['Is_online']=1; $sku _gift_info= $sku _gift->field ('Sku_no,stock')-where($sgg _where)-Select(); return$sku _gift_info;}

Test the concurrency again.

found that the gift inventory is turned negative, which is oversold situation appeared

OK, the above test can be, the following look directly with the operation of the data table to see the effect, in the following order to execute, found that when the first 4 steps, User B query not to the gift inventory information, mainly because the gift table has been locked

Below executes user A 5th commit, User B inquires the gift stock immediately inquires out

The above is visible, MySQL pessimistic lock on the giveaway inventory oversold processing process of the effective process

MySQL pessimistic lock processing Giveaway inventory oversold situation