仅当两表计数与总和差值>0时插入行：库存预留校验方案

方案1：数据库触发器（推荐，强保证数据一致性） #

直接在表B上创建BEFORE INSERT触发器，插入前自动计算可用库存并校验，不满足条件就终止插入并抛出错误。

以MySQL为例，触发器代码如下：

DELIMITER //
CREATE TRIGGER check_reserved_stock BEFORE INSERT ON B
FOR EACH ROW
BEGIN
    DECLARE available INT;
    -- 计算目标商品的可用库存
    SELECT (
        (SELECT COUNT(*) FROM A WHERE productId = NEW.productId AND owner IS NULL)
        - (SELECT IFNULL(SUM(reserved), 0) FROM B WHERE productId = NEW.productId)
    ) INTO available;
    
    -- 库存不足则抛出错误
    IF available < NEW.reserved THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '可用库存不足，无法完成预留';
    END IF;
END //
DELIMITER ;

测试插入语句：

-- 这条会触发错误，因为可用库存仅为2，请求预留3
INSERT INTO B (productId, owner, reserved) VALUES (1000, 'bill', 3);

注：不同数据库的错误抛出语法有差异，比如PostgreSQL用RAISE EXCEPTION，SQL Server用THROW，需对应调整。

方案2：存储过程封装插入逻辑 #

把库存校验和插入操作封装到存储过程中，所有预留操作统一通过调用存储过程执行，避免直接操作表B。

MySQL示例代码：

DELIMITER //
CREATE PROCEDURE add_reserved_record(
    IN p_productId INT,
    IN p_owner VARCHAR(50),
    IN p_reserved INT
)
BEGIN
    DECLARE available INT;
    -- 计算可用库存
    SELECT (
        (SELECT COUNT(*) FROM A WHERE productId = p_productId AND owner IS NULL)
        - (SELECT IFNULL(SUM(reserved), 0) FROM B WHERE productId = p_productId)
    ) INTO available;
    
    IF available >= p_reserved THEN
        INSERT INTO B (productId, owner, reserved) VALUES (p_productId, p_owner, p_reserved);
        SELECT '预留成功' AS result;
    ELSE
        SELECT CONCAT('预留失败，当前可用库存：', available) AS result;
    END IF;
END //
DELIMITER ;

调用方式：

CALL add_reserved_record(1000, 'bill', 3); -- 返回预留失败
CALL add_reserved_record(1000, 'bill', 2); -- 执行插入并返回成功

方案3：应用层事务+锁控制 #

如果不想在数据库层做逻辑，可在应用代码中通过事务加锁实现，避免并发场景下的库存超卖：

1. 开启数据库事务
2. 对目标商品的A、表B数据加排他锁，防止并发修改
3. 计算可用库存
4. 校验通过则插入表B，否则回滚事务
5. 提交事务

Java+MySQL伪代码示例：

public boolean addReserved(int productId, String owner, int reserved) {
    Connection conn = null;
    try {
        conn = getDbConnection();
        conn.setAutoCommit(false);
        
        // 加行锁，锁定目标商品的库存数据
        String lockSql = "SELECT * FROM A WHERE productId = ? FOR UPDATE";
        PreparedStatement lockStmt = conn.prepareStatement(lockSql);
        lockStmt.setInt(1, productId);
        lockStmt.executeQuery();
        
        // 计算可用库存
        String calcSql = "SELECT (SELECT COUNT(*) FROM A WHERE productId = ? AND owner IS NULL) - (SELECT IFNULL(SUM(reserved), 0) FROM B WHERE productId = ?) AS available";
        PreparedStatement calcStmt = conn.prepareStatement(calcSql);
        calcStmt.setInt(1, productId);
        calcStmt.setInt(2, productId);
        ResultSet rs = calcStmt.executeQuery();
        rs.next();
        int available = rs.getInt("available");
        
        if (available >= reserved) {
            String insertSql = "INSERT INTO B (productId, owner, reserved) VALUES (?, ?, ?)";
            PreparedStatement insertStmt = conn.prepareStatement(insertSql);
            insertStmt.setInt(1, productId);
            insertStmt.setString(2, owner);
            insertStmt.setInt(3, reserved);
            insertStmt.executeUpdate();
            conn.commit();
            return true;
        } else {
            conn.rollback();
            return false;
        }
    } catch (SQLException e) {
        if (conn != null) {
            try { conn.rollback(); } catch (SQLException ex) { ex.printStackTrace(); }
        }
        e.printStackTrace();
        return false;
    } finally {
        if (conn != null) {
            try { conn.close(); } catch (SQLException e) { e.printStackTrace(); }
        }
    }
}

注：必须加锁，否则高并发场景下会出现多个请求同时计算库存，导致超卖。

内容的提问来源于stack exchange，提问作者Lion.k