本文目录导读：
在PHP项目中实现出库单扣减库存,建议采用事务处理 + 锁机制（乐观锁/悲观锁） 来保证数据一致性，以下是完整的实现逻辑和代码示例：
核心逻辑流程
public function processOutboundOrder($orderData) {
// 开启数据库事务
$this->db->beginTransaction();
try {
// 1. 校验出库单商品数据
$this->validateOutboundItems($orderData['items']);
// 2. 锁定库存（防止并发）
$this->lockInventory($orderData['items']);
// 3. 逐项扣减库存
foreach ($orderData['items'] as $item) {
$this->deductStock($item['product_id'], $item['quantity']);
}
// 4. 记录出库单
$this->saveOutboundOrder($orderData);
// 提交事务
$this->db->commit();
} catch (Exception $e) {
$this->db->rollback();
throw $e;
}
}
扣减库存方法实现
方案1：悲观锁（推荐高并发场景）
// 使用 SELECT ... FOR UPDATE 锁定行
public function deductStock($productId, $quantity) {
// 锁定并查询库存
$sql = "SELECT id, stock_quantity, version
FROM products
WHERE id = ?
FOR UPDATE";
$product = $this->db->fetchOne($sql, [$productId]);
// 检查库存是否充足
if ($product['stock_quantity'] < $quantity) {
throw new Exception("商品{$productId}库存不足");
}
// 执行扣减
$updateSql = "UPDATE products
SET stock_quantity = stock_quantity - ?,
updated_at = NOW()
WHERE id = ?
AND stock_quantity >= ?"; // 二次校验
$affected = $this->db->execute($updateSql, [$quantity, $productId, $quantity]);
if ($affected === 0) {
throw new Exception("库存扣减失败，可能已被其他请求更新");
}
}
方案2：乐观锁（适用于冲突较少的场景）
public function deductStock($productId, $quantity, $maxRetries = 3) {
$retries = 0;
while ($retries < $maxRetries) {
// 查询当前库存和版本号
$product = $this->db->fetchOne(
"SELECT stock_quantity, version FROM products WHERE id = ?",
[$productId]
);
if ($product['stock_quantity'] < $quantity) {
throw new Exception("库存不足");
}
$newVersion = $product['version'] + 1;
// 带版本号的更新
$sql = "UPDATE products
SET stock_quantity = stock_quantity - ?,
version = ?,
updated_at = NOW()
WHERE id = ?
AND version = ?
AND stock_quantity >= ?";
$affected = $this->db->execute($sql, [
$quantity,
$newVersion,
$productId,
$product['version'],
$quantity
]);
if ($affected > 0) {
return true; // 成功
}
// 更新失败，重试
$retries++;
usleep(100000); // 100ms 延迟
}
throw new Exception("库存扣减失败，请重试");
}
完整示例代码
<?php
class InventoryService {
private $db;
public function processOutboundOrder($orderId, $items) {
try {
$this->db->beginTransaction();
// 1. 创建出库单记录
$outboundId = $this->createOutbound($orderId);
// 2. 处理每个商品
foreach ($items as $item) {
// 锁定商品行
$product = $this->db->fetchOne(
"SELECT * FROM products WHERE id = ? FOR UPDATE",
[$item['product_id']]
);
if (!$product) {
throw new Exception("商品不存在: " . $item['product_id']);
}
// 校验库存
$requestedQty = $item['quantity'];
$availableQty = $product['stock_quantity'];
if ($availableQty < $requestedQty) {
throw new Exception(
"商品 {$product['name']} 库存不足：需求{$requestedQty}，剩余{$availableQty}"
);
}
// 扣减库存
$this->db->execute(
"UPDATE products
SET stock_quantity = stock_quantity - ?,
updated_at = NOW()
WHERE id = ?
AND stock_quantity >= ?",
[$requestedQty, $item['product_id'], $requestedQty]
);
// 记录出库明细
$this->createOutboundDetail($outboundId, $item);
}
$this->db->commit();
return $outboundId;
} catch (Exception $e) {
$this->db->rollback();
// 日志记录错误
error_log("出库失败: " . $e->getMessage());
throw $e;
}
}
private function createOutbound($orderId) {
$sql = "INSERT INTO outbound_orders (order_id, status, created_at)
VALUES (?, 'pending', NOW())";
$this->db->execute($sql, [$orderId]);
return $this->db->lastInsertId();
}
private function createOutboundDetail($outboundId, $item) {
$sql = "INSERT INTO outbound_details
(outbound_id, product_id, quantity, unit_price)
VALUES (?, ?, ?, ?)";
$this->db->execute($sql, [
$outboundId,
$item['product_id'],
$item['quantity'],
$item['unit_price'] ?? 0
]);
}
}
数据库表结构建议
-- 商品表
CREATE TABLE products (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(255) NOT NULL,
stock_quantity INT NOT NULL DEFAULT 0,
version INT NOT NULL DEFAULT 1, -- 乐观锁版本号
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);
-- 出库单主表
CREATE TABLE outbound_orders (
id INT PRIMARY KEY AUTO_INCREMENT,
order_id VARCHAR(50) NOT NULL,
status ENUM('pending', 'completed', 'cancelled') DEFAULT 'pending',
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
-- 出库明细表
CREATE TABLE outbound_details (
id INT PRIMARY KEY AUTO_INCREMENT,
outbound_id INT NOT NULL,
product_id INT NOT NULL,
quantity INT NOT NULL,
unit_price DECIMAL(10,2) DEFAULT 0.00,
FOREIGN KEY (outbound_id) REFERENCES outbound_orders(id),
FOREIGN KEY (product_id) REFERENCES products(id)
);
最佳实践建议
- 数据库引擎：使用 InnoDB（支持事务和行级锁）
- 库存充足校验：在 UPDATE 语句中加入
AND stock_quantity >= ?二次校验 - 并发控制：高并发时优先使用悲观锁（FOR UPDATE）
- 性能优化：对
product_id建立索引 - 监控报警：库存低于阈值时触发告警
- 日志记录：每次操作记录详细的日志
// 库存充足监控示例
public function checkStockThreshold($productId, $threshold = 10) {
$sql = "SELECT stock_quantity FROM products WHERE id = ?";
$stock = $this->db->fetchOne($sql, [$productId]);
if ($stock['stock_quantity'] <= $threshold) {
// 发送告警通知
$this->notifyLowStock($productId, $stock['stock_quantity']);
}
}
通过以上方案,可以有效地保证在并发场景下库存数据的准确性和一致性，建议根据实际业务量选择合适的锁机制，并做好异常处理和日志记录。
