MySQL版 秒杀练习

1. [代码][PHP]代码    

<?php

/**
*
*
*  CREATE TABLE `UserList` (
*    `ID` int(10) unsigned NOT NULL AUTO_INCREMENT,
*    `UserID` int(10) unsigned DEFAULT NULL,
*    `ActivityID` int(10) unsigned DEFAULT '1',
*    `Milliseconds` bigint(20) unsigned DEFAULT NULL,
*    PRIMARY KEY (`ID`),
*    UNIQUE KEY `uniqueKey` (`UserID`,`ActivityID`)
*  ) ENGINE=InnoDB DEFAULT CHARSET=utf8
*
*
*  CREATE TABLE `DateRange` (
*    `DateID` int(10) unsigned NOT NULL AUTO_INCREMENT,
*    `Total` int(10) unsigned DEFAULT '0',
*    `Amount` int(10) DEFAULT '0',
*    `Date` int(10) unsigned DEFAULT NULL,
*    `Status` tinyint(3) unsigned DEFAULT '0',
*    `Timestamp` int(10) unsigned DEFAULT NULL,
*    PRIMARY KEY (`DateID`)
*  ) ENGINE=InnoDB DEFAULT CHARSET=utf8
*
*
*/


$host = '127.0.0.1';
$user = 'root';
$password = '123456';
$charset = 'utf8';
$dbname = 'test';
 
$pdo = new PDO('mysql:host='.$host.';dbname='.$dbname, $user, $password);
$pdo->setAttribute(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, true);
$pdo->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC);
$pdo->query('set names '. $charset);

$UserID = (int) $_GET['UserID'];
$ActivityID = (int) $_GET['ActivityID'];


// 库存检查
try {
    $sql = 'update `DateRange` set `Amount` = `Amount` - 1 , `Timestamp` = UNIX_TIMESTAMP() where `Date` = ? and `Amount` > 0';
    $sth = $pdo->prepare($sql);
    $sth->BindValue(1, date('Ymd'), PDO::PARAM_INT);
    $sth->execute();
 
    if (!$sth->rowCount()) {
        $message = 'Amount Invalid';
        throw new ErrorException($message);
    }
     
    if ($sth->errorCode() != '00000') {
        $errorInfo = $sth->errorInfo();
        list($pdoerr, $err, $message) = $errorInfo;
        throw new ErrorException($message);
    }
     
}catch(Exception $e) {
	
	echo sprintf('{ "exception" : "InvalidNumberException", "message": "%s" }', $e->getMessage());
	exit;
     
}

// 准备入库
try {
    $sql = 'insert into UserList set UserID = ?, ActivityID = ?, Milliseconds = ?';
    $sth = $pdo->prepare($sql);
    $sth->BindValue(1, $UserID, PDO::PARAM_INT);
    $sth->BindValue(2, $ActivityID, PDO::PARAM_INT);
    $sth->BindValue(3, round(microtime(true) * 1000), PDO::PARAM_STR);
    $sth->execute();
	
	if (!$sth->rowCount()) {
        $message = 'Save Invalid';
        throw new ErrorException($message);
    }
 
    if ($sth->errorCode() != '00000') {
        $errorInfo = $sth->errorInfo();
        list($pdoerr, $error, $message) = $errorInfo;
        throw new ErrorException($message);
    }
     
}catch(Exception $e) {
     
    // 还原库存
    $sql = 'update `DateRange` set `Amount` = `Amount` + 1 , `Timestamp` = UNIX_TIMESTAMP() where Date = ?';
    $sth = $pdo->prepare($sql);
    $sth->BindValue(1, date('Ymd'), PDO::PARAM_INT);
    $sth->execute();
     
    echo sprintf('{ "exception" : "SaveInvalidException", "message": "%s" }', $e->getMessage());
	exit;
     
}


echo sprintf('{ "errno" : 0 , "data" : { "UserID" : %d, "Milliseconds": %.0f } }', $UserID, round(microtime(true) * 1000));
exit;