I. Business scenario: When the customer places an order and fails to pay within the specified time, we need to cancel the order. For example, a good way to deal with this is to use delayed cancellation. Swoole is used here. Wiki.swoole.com/wiki/page/3…


When order_status is 1, it means that the customer has ordered and confirmed; when order_status is 2, it means that the customer has paid; and when order_status is 0, it means that the order has been cancelled (swoole does this). I do not use the framework to represent the following


3. For example, the inventory table CSDN_product_stock of product ID1 is 20, and that of product ID2 is 40. Then the customer places a single order of product ID1 minus 10, and product ID2 minus 20, so the inventory table is only enough for two orders.


Diagram: 1. After the first order is placed, the ID1 inventory is reduced from 20 to 10, and the ID2 inventory is reduced from 40 to 20; 2. After placing the second order, the inventory of product ID is 0, and so is the inventory of product ID2. 3. 4. After 10 seconds (10 seconds later after each order is placed), the customer places two orders. Since there is no payment (ORDER_status of CSDN_ORDER table is 1), the inventory of products 1 and 2 is restored (order_status of CSDN_ORDER table is 0), the customer can continue to place orders

1, the required SQL database table

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'.'the 2018-09-13 19:36:19');
INSERT INTO `csdn_product_stock` VALUES ('2'.'2'.'40'.'the 2018-09-13 19:36:19');
Copy the code

2. Database configuration information: config.php

<? php$dbHost = "192.168.0.110";
$dbUser = "root";
$dbPassword = "123456";
$dbName = "test123"; ? >Copy the code

3, order_submit. PHP, generate order

<? php require("config.php");
try {
    $pdo = new PDO("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(); // Start transaction processing$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) {
            throw new PDOException("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']) {
                throw new PDOException("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(); SQLSTATE[22003]: Numeric value out of range: 1690 BIGINT UNSIGNED value is out of rangein '(`test`.`csdn_product_stock`.`product_stock_number` - 20)'
            if($affectedRowsProductStock <= 0) {
                throw new PDOException("Out of stock, Failure to submit order!"); }}echo "Successful, Order Id is：" . $orderId .", Order Amount is:" . $orderInfo['order_amount']."。";
        $pdo->commit(); // Commit transaction //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();
}
?>
Copy the code


Order_cancel.php: order_cancel.php: order_cancel.php: order_cancel.php: order_cancel.php: order_cancel.php: order_cancel.php

<? php require("config.php");
$queryString = getopt('a:');
$userParams = array($queryString);
appendLog(date("Y-m-d H:i:s")."\t" . $queryString['a']."\t" . "start");

try {
    $pdo = new PDO("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(); // Start transaction processing$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"The representative has placed an order, but has not paid, we restore inventory only for the unpaid orderif(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(); // Commit transaction}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();
}
function appendLog($str) {
    $dir = 'log.txt';
    $fh = fopen($dir."a");
    fwrite($fh.$str . "\n");
    fclose($fh);
}
?>Copy the code

More Swoole knowledge sharing If you like my article and want to learn with a group of experienced developers, for more learning materials please join my learning group here to learn and grow