一、业务场景:当客户下单在指定的时间内如果没有付款,那我们需要将这笔订单取消掉,比如好的处理方法是运用延时取消,这里我们用到了swoole,运用swoole的异步毫秒定时器不会影响到当前程序的运行,具体参考:https://wiki.swoole.com/wiki/page/319.html二、说明,order_status为1时代表客户下单确定,为2时代表客户已付款,为0时代表订单已取消(正是swoole来做的),下面的代表我没有用框架,比较纯的PHP代表方便理解和应用三、举例说明,库存表csdn_product_stock产品ID为1的产品库存数量为20,产品ID为2的库存数量为40,然后客户下单一笔产品ID1减10,产品ID2减20,所以库存表只够2次下单,例子中10秒后自动还原库存,如下图:图解:1、第一次下完单产品ID1库存从20减到了10,产品ID2库存从40减到了20;2、第二次下完单产品ID的库存为0了,产品ID2的库存也为0了,3、第三次下单时,程序提示Out of stock;4、过了10秒钟(每个订单下单后往后推10秒),客户两次下单,由于没有付款(csdn_order表的order_status为1),产品1和产品2的库存被还原了(csdn_order表的order_status变为0),客户又可以继续下单了1、所需要sql数据库表
DROP TABLE IF EXISTS `csdn_order`;
CREATE TABLE `csdn_order` (
`order_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`order_amount` float(10,2) unsigned NOT NULL DEFAULT '0.00',
`user_name` varchar(64) CHARACTER SET latin1 NOT NULL DEFAULT '',
`order_status` tinyint(2) unsigned NOT NULL DEFAULT '0',
`date_created` datetime NOT NULL,
PRIMARY KEY (`order_id`)
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8;
DROP TABLE IF EXISTS `csdn_order_detail`;
CREATE TABLE `csdn_order_detail` (
`detail_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`order_id` int(10) unsigned NOT NULL,
`product_id` int(10) NOT NULL,
`product_price` float(10,2) NOT NULL,
`product_number` smallint(4) unsigned NOT NULL DEFAULT '0',
`date_created` datetime NOT NULL,
PRIMARY KEY (`detail_id`),
KEY `idx_order_id` (`order_id`)
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8;
DROP TABLE IF EXISTS `csdn_product_stock`;
CREATE TABLE `csdn_product_stock` (
`auto_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`product_id` int(10) NOT NULL,
`product_stock_number` int(10) unsigned NOT NULL,
`date_modified` datetime NOT NULL,
PRIMARY KEY (`auto_id`),
KEY `idx_product_id` (`product_id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;
INSERT INTO `csdn_product_stock` VALUES ('1', '1', '20', '2018-09-13 19:36:19');
INSERT INTO `csdn_product_stock` VALUES ('2', '2', '40', '2018-09-13 19:36:19');
<?phprequire("config.php");try{$pdo=newPDO("mysql:host=".$dbHost.";dbname=".$dbName,$dbUser,$dbPassword,array(PDO::ATTR_PERSISTENT=>true));$pdo->setAttribute(PDO::ATTR_AUTOCOMMIT,1);$pdo->setAttribute(PDO::ATTR_ERRMODE,PDO::ERRMODE_EXCEPTION);$orderInfo=array('order_amount'=>10.92,'user_name'=>'yusan','order_status'=>1,'date_created'=>'now()','product_lit'=>array(0=>array('product_id'=>1,'product_price'=>5.00,'product_number'=>10,'date_created'=>'now()'),1=>array('product_id'=>2,'product_price'=>5.92,'product_number'=>20,'date_created'=>'now()')));try{$pdo->beginTransaction();//开启事务处理
$sql='insert into csdn_order (order_amount, user_name, order_status, date_created) values (:orderAmount, :userName, :orderStatus, now())';$stmt=$pdo->prepare($sql);$affectedRows=$stmt->execute(array(':orderAmount'=>$orderInfo['order_amount'],':userName'=>$orderInfo['user_name'],':orderStatus'=>$orderInfo['order_status']));$orderId=$pdo->lastInsertId();if(!$affectedRows){thrownewPDOException("Failure to submit order!");}foreach($orderInfo['product_lit']as$productInfo){$sqlProductDetail='insert into csdn_order_detail (order_id, product_id, product_price, product_number, date_created) values (:orderId, :productId, :productPrice, :productNumber, now())';$stmtProductDetail=$pdo->prepare($sqlProductDetail);$stmtProductDetail->execute(array(':orderId'=>$orderId,':productId'=>$productInfo['product_id'],':productPrice'=>$productInfo['product_price'],':productNumber'=>$productInfo['product_number']));$sqlCheck="select product_stock_number from csdn_product_stock where product_id=:productId";$stmtCheck=$pdo->prepare($sqlCheck);$stmtCheck->execute(array(':productId'=>$productInfo['product_id']));$rowCheck=$stmtCheck->fetch(PDO::FETCH_ASSOC);if($rowCheck['product_stock_number']<$productInfo['product_number']){thrownewPDOException("Out of stock, Failure to submit order!");}$sqlProductStock='update csdn_product_stock set product_stock_number=product_stock_number-:productNumber, date_modified=now() where product_id=:productId';$stmtProductStock=$pdo->prepare($sqlProductStock);$stmtProductStock->execute(array(':productNumber'=>$productInfo['product_number'],':productId'=>$productInfo['product_id']));$affectedRowsProductStock=$stmtProductStock->rowCount();//库存没有正常扣除,失败,库存表里的product_stock_number设置了为非负数
//如果库存不足时,sql异常:SQLSTATE[22003]: Numeric value out of range: 1690 BIGINT UNSIGNED value is out of range in '(`test`.`csdn_product_stock`.`product_stock_number` - 20)'
if($affectedRowsProductStock<=0){thrownewPDOException("Out of stock, Failure to submit order!");}}echo"Successful, Order Id is:".$orderId.",Order Amount is:".$orderInfo['order_amount']."。";$pdo->commit();//提交事务
//exec("php order_cancel.php -a" . $orderId . " &");
pclose(popen('php order_cancel.php -a '.$orderId.' &','w'));//system("php order_cancel.php -a" . $orderId . " &", $phpResult);
//echo $phpResult;
}catch(PDOException$e){echo$e->getMessage();$pdo->rollback();}$pdo=null;}catch(PDOException$e){echo$e->getMessage();}?>
4、order_cancel.php,这个方法主要就是做订单自动取消,并还原库存的业务处理
<?phprequire("config.php");$queryString=getopt('a:');$userParams=array($queryString);appendLog(date("Y-m-d H:i:s")."\t".$queryString['a']."\t"."start");try{$pdo=newPDO("mysql:host=".$dbHost.";dbname=".$dbName,$dbUser,$dbPassword,array(PDO::ATTR_PERSISTENT=>true));$pdo->setAttribute(PDO::ATTR_AUTOCOMMIT,0);$pdo->setAttribute(PDO::ATTR_ERRMODE,PDO::ERRMODE_EXCEPTION);swoole_timer_after(10000,function($queryString){global$queryString,$pdo;try{$pdo->beginTransaction();//开启事务处理
$orderId=$queryString['a'];$sql="select order_status from csdn_order where order_id=:orderId";$stmt=$pdo->prepare($sql);$stmt->execute(array(':orderId'=>$orderId));$row=$stmt->fetch(PDO::FETCH_ASSOC);//$row['order_status'] === "1"代表已下单,但未付款,我们还原库存只针对未付款的订单
if(isset($row['order_status'])&&$row['order_status']==="1"){$sqlOrderDetail="select product_id, product_number from csdn_order_detail where order_id=:orderId";$stmtOrderDetail=$pdo->prepare($sqlOrderDetail);$stmtOrderDetail->execute(array(':orderId'=>$orderId));while($rowOrderDetail=$stmtOrderDetail->fetch(PDO::FETCH_ASSOC)){$sqlRestoreStock="update csdn_product_stock set product_stock_number=product_stock_number + :productNumber, date_modified=now() where product_id=:productId";$stmtRestoreStock=$pdo->prepare($sqlRestoreStock);$stmtRestoreStock->execute(array(':productNumber'=>$rowOrderDetail['product_number'],':productId'=>$rowOrderDetail['product_id']));}$sqlRestoreOrder="update csdn_order set order_status=:orderStatus where order_id=:orderId";$stmtRestoreOrder=$pdo->prepare($sqlRestoreOrder);$stmtRestoreOrder->execute(array(':orderStatus'=>0,':orderId'=>$orderId));}$pdo->commit();//提交事务
}catch(PDOException$e){echo$e->getMessage();$pdo->rollback();}$pdo=null;appendLog(date("Y-m-d H:i:s")."\t".$queryString['a']."\t"."end\t".json_encode($queryString));},$pdo);}catch(PDOException$e){echo$e->getMessage();}functionappendLog($str){$dir='log.txt';$fh=fopen($dir,"a");fwrite($fh,$str."\n");fclose($fh);}?>