Hugh's Blog

PHP 数据库并发处理

 

下面是一些 PHP 数据库并发的简单处理方案,作个记录。

数据库信息

create table goods(
   id INT NOT NULL AUTO_INCREMENT,
   stock INT NOT NULL DEFAULT 0,
   PRIMARY KEY (id)
);
insert into goods(stock) values(100);

正常流程

检查库存,更新库存。

try {
    $pdo = new PDO('mysql:host=127.0.0.1;dbname=test;port=3306;charset=utf8', 'root', 'root');
} catch (PDOException $e) {
    die($e->getMessage());
}

$stmt = $pdo->prepare('select stock from goods where id = 1');
$stmt->execute();

$row = $stmt->fetch(PDO::FETCH_ASSOC);
if ($row['stock'] > 0) {
    $stmt = $pdo->prepare('update goods set stock = stock - 1 where id = 1');
    $stmt->execute();
}

$pdo = null;

使用排他锁

开启事务,检查库存(锁定数据行),更新库存,提交事务。

// PDO
$pdo->setAttribute(PDO::ATTR_AUTOCOMMIT, false);
$pdo->beginTransaction();

try {
    $stmt = $pdo->prepare('select stock from goods where id = 1 for update');
    $stmt->execute();

    $row = $stmt->fetch(PDO::FETCH_ASSOC);
    if ($row['stock'] > 0) {
        $stmt = $pdo->prepare('update goods set stock = stock - 1 where id = 1');
        $stmt->execute();
    }

    $pdo->commit();
} catch (PDOException $e) {
    $pdo->rollback();
    $pdo = null;
    die('Error');
}

$pdo = null;

使用文件锁

检查文件是否锁定,检查库存,更新库存。

// PDO
$fp = fopen('lock.txt', 'w+');
if (!flock($fp, LOCK_EX)) {
    fclose($fp);
    $pdo = null;
    die('Error');
}

$stmt = $pdo->prepare('select stock from goods where id = 1');
$stmt->execute();

$row = $stmt->fetch(PDO::FETCH_ASSOC);
if ($row['stock'] > 0) {
    $stmt = $pdo->prepare('update goods set stock = stock - 1 where id = 1');
    if ($stmt->execute()) {
        flock($fp, LOCK_UN);
    }
}

fclose($fp);
$pdo = null;

使用 Redis 队列

检查队列库存,更新数据库库存。

// PDO
$redis = new Redis();
$redis->connect('127.0.0.1', 6379);

// 把库存信息放进队列
if (isset($_GET['init'])) {
    $redis->del('goods_stock');
    $maxStock = 100;
    for ($i = 1; $i <= $maxStock; $i++) {
        $redis->lpush('goods_stock', $i);
    }
    echo $redis->llen('goods_stock');

    $pdo = null;
    exit();
}

$exists = $redis->lpop('goods_stock');
if ($exists) {
    $stmt = $pdo->prepare('update goods set stock = stock - 1 where id = 1');
    $stmt->execute();
} else {
    $pdo = null;
    die('Error');
}

$pdo = null;

接下来可以使用 AB 测试:

ab -n 1000 -c 1000 http://localhost/test1.php

这几个都是简单的方案思路,实际情况要复杂很多,需要具体分析。


参考

PHP 高并发秒杀解决方案