php库存减一代码,php操作mysql，并发减库存，悲观锁实现

1、建表

CREATE TABLE `goods` (

`id` int(11) unsigned NOT NULL AUTO_INCREMENT,

`goods` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,

`num` int(11) unsigned NOT NULL,

PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `test`.`goods` (`id`, `goods`, `num`) VALUES ('1', '手机', 50);

CREATE TABLE `order` (

`id` int(11) unsigned NOT NULL AUTO_INCREMENT,

`goods` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,

`created_at` int(11) DEFAULT NULL,

PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

两个表，一个商品表，存放了库存为50的手机，一个订单表，用来存放即将入库的数据。

2、代码使用php实现，思路就是使用事务加FOR UPDATE实现

"set names utf8", //设置编码

PDO::ATTR_EMULATE_PREPARES => false, //使用预处理

];

$db = new PDO($dsn, $user, $password, $params);

} catch (PDOException $e) {

echo $e->getMessage();

exit;

}

//悲观锁

try {

$db->beginTransaction(); //开启事务

$sth = $db->prepare("SELECT goods,num FROM goods WHERE `id`=? FOR UPDATE");

$sth->execute([1]);

$info = $sth->fetch(PDO::FETCH_ASSOC);

if (empty($info) || $info['num'] <= 0) {

throw new Exception("已卖完了", 1);

}

//减库存

$sth = $db->prepare("UPDATE `goods` SET `num`=num-1 WHERE `id`=?");

$sth->execute([1]);

if (!$sth->rowCount()) {

throw new Exception("减库存失败!", 2);

}

//入库

$sth = $db->prepare("INSERT INTO `order` (`goods`, `created_at`) VALUES (?,?)");

$sth->execute([$info['goods'], time()]);

if (!$sth->rowCount()) {

throw new Exception("入库失败!", 3);

}

$db->commit();

} catch (Exception $e) {

$db->rollBack();

file_put_contents('D:/1.txt', $e->getMessage() . "\n", FILE_APPEND);

}

$db = null; //关闭数据库连接

3、使用ab压测，

ab -c 200 -n 2000 http://127.0.0.1:20081/test/pdo.php