乐观锁实现异常：并发请求下库存扣减与事务问题求助

架构老林

2026-7-13

电商系统乐观锁并发问题排查

问题场景

使用Java、MySQL和JDBC开发电商网站时，遇到乐观锁并发控制失效的问题：

商品初始可用库存为4
用户A同时购买3件，用户B同时购买2件
最终剩余库存为2，版本列仅自增1，两个用户的支付和订单均完成，未触发任何事务回滚

推测是两个请求在一方更新版本前都读取到相同版本，导致后提交的请求覆盖前一个的结果，现附上相关代码请求排查。

控制器代码

String applicationMessage = null;
DAOFactory sessionDAOFactory = null;
DAOFactory daoFactory = null;
User loggedUser;

Logger logger = LogService.getApplicationLogger();

try {
    Map sessionFactoryParameters = new HashMap<String, Object>();
    sessionFactoryParameters.put("request", request);
    sessionFactoryParameters.put("response", response);
    sessionDAOFactory = DAOFactory.getDAOFactory(Configuration.COOKIE_IMPL, sessionFactoryParameters);
    sessionDAOFactory.beginTransaction();

    UserDAO sessionUserDAO = sessionDAOFactory.getUserDAO();
    loggedUser = sessionUserDAO.findLoggedUser();

    daoFactory = DAOFactory.getDAOFactory(Configuration.DAO_IMPL, null);
    daoFactory.beginTransaction();

    Double CardNumber = Double.parseDouble(request.getParameter("CardNumber"));
    int CVV = Integer.parseInt(request.getParameter("cvv"));
    String Name = request.getParameter("name");
    String ExpireDate = request.getParameter("expireDate");

    /* 查找当前登录用户的待处理订单 */
    OrderDAO orderDAO = daoFactory.getOrderDAO();
    Order order = orderDAO.findByLoggedUserAndPendingStatus(loggedUser);

    // 查找购物车
    CartDAO cartDAO = daoFactory.getCartDAO();
    Cart cart = cartDAO.findCartByOrder(order);

    // 获取购物车内所有商品项
    CartItemDAO cartItemDAO = daoFactory.getCartItemDAO();
    List<CartItem> cartItems = cartItemDAO.getCartItems(cart);

    // 创建支付记录
    PaymentDAO paymentDAO = daoFactory.getPaymentDAO();
    paymentDAO.create(cart.getPrice_cart(), order, CardNumber, CVV, Name, ExpireDate);

    // 将订单标记为已完成
    orderDAO.setCompleteStatus(order);

    // 扣减商品库存
    for (CartItem cartItem : cartItems) {
        ProductDAO productDAO = daoFactory.getProductDAO();
        productDAO.checkAndDecreaseQuantity(cartItem.getProduct().getId_product(), cartItem.getQuantity());
    }

    daoFactory.commitTransaction();
    sessionDAOFactory.commitTransaction();

    applicationMessage = "Payment received, order executed successfully";

    request.setAttribute("loggedOn", loggedUser != null);
    request.setAttribute("loggedUser", loggedUser);
    request.setAttribute("applicationMessage", applicationMessage);
    request.setAttribute("viewUrl", "jsp/homeManagement/Home");

} catch (OptimisticLockException e) {
    logger.log(Level.SEVERE, "Controller Error", e);
    try {
        if (daoFactory != null) daoFactory.rollbackTransaction();
        if (sessionDAOFactory != null) sessionDAOFactory.rollbackTransaction();
    } catch (Throwable t) {
    }
    throw new RuntimeException(e);

} catch (NotAvailableProductException e) {
    logger.log(Level.INFO, "Controller Error", e);
    try {
        if (daoFactory != null) daoFactory.rollbackTransaction();
        if (sessionDAOFactory != null) sessionDAOFactory.rollbackTransaction();
    } catch (Throwable t) {
    }
    throw new RuntimeException(e);

} catch (Exception e) {
    logger.log(Level.SEVERE, "Controller Error", e);
    try {
        if (daoFactory != null) daoFactory.rollbackTransaction();
        if (sessionDAOFactory != null) sessionDAOFactory.rollbackTransaction();
    } catch (Throwable t) {
    }
    throw new RuntimeException(e);

} finally {
    try {
        if (daoFactory != null) daoFactory.closeTransaction();
        if (sessionDAOFactory != null) sessionDAOFactory.closeTransaction();
    } catch (Throwable t) {
    }
}

ProductDAO.checkAndDecreaseQuantity方法代码

public int checkAndDecreaseQuantity(Long ID_product, int quantity) throws NotAvailableProductException, OptimisticLockException {
    PreparedStatement ps;
    Product product=null;

    try {
        String sql
                = " SELECT * FROM PRODUCT "
                + " WHERE "
                + "   Deleted=0 AND Blocked=0 AND ID_product=? AND AvailableQuantity>=?";

        ps = conn.prepareStatement(sql);
        ps.setLong(1, ID_product);
        ps.setInt(2,quantity);

        ResultSet resultSet = ps.executeQuery();

        boolean exist=false;
        while (resultSet.next()) {
            product = read(resultSet);
            exist=true;
        }

        resultSet.close();

        if (!exist) {
            throw new NotAvailableProductException("ProductDAOJDBCImpl.decreaseQuantity: The desired quantity of the selected product is less than the available quantity, or the product has been deleted or blocked.");
        }

        sql = " UPDATE Product "
                + " SET AvailableQuantity=?, version=? "
                + " WHERE "
                + " ID_product=? AND Blocked=0 AND Deleted=0 AND version=?";

        ps = conn.prepareStatement(sql);
        int i=1;
        ps.setInt(i++, product.getQuantity()-quantity);
        ps.setLong(i++, product.getVersion()+1);
        ps.setLong(i++, ID_product);
        ps.setLong(i++, product.getVersion());

        ps.executeUpdate();
        ps.close();

    } catch (SQLException e) {
        String errorMessage = e.getMessage();
        int errorCode = e.getErrorCode();

        if (errorMessage.contains("Unknown column") || errorCode == 1054) {
            throw new OptimisticLockException("Exception lock optimistic quantity update");
        } else {
            throw new RuntimeException(e);
        }
    }
    return product.getQuantity()-quantity;
}

问题根源分析

竞态条件：查询与更新分离
当前实现先执行SELECT读取库存和版本，再执行UPDATE修改数据。并发场景下，两个请求会同时读取到相同的版本号（如v1）和库存（4）：
- 用户A计算库存为4-3=1，版本为v1+1=v2，执行UPDATE WHERE version=v1成功
- 用户B计算库存为4-2=2，版本为v1+1=v2，执行UPDATE WHERE version=v1也成功
  最终库存被后执行的请求覆盖为2，版本仅自增到v2，完全未触发乐观锁机制。
未校验UPDATE影响行数
执行ps.executeUpdate()后未判断返回的影响行数。如果更新行数为0，说明版本已被其他请求修改，应该抛出OptimisticLockException，但当前代码仅在SQL语法错误时抛出该异常，完全忽略了乐观锁失效的核心场景。
库存校验存在间隙
SELECT时校验AvailableQuantity>=quantity，但从SELECT到UPDATE的时间窗口内，库存可能已被其他请求修改，导致最终更新后的库存不符合实际情况（如案例中用户B的更新导致库存异常）。

解决方案

1. 合并查询与更新为原子SQL操作

利用数据库的原子性，用单条SQL完成库存校验、扣减和版本更新，彻底消除竞态条件：

UPDATE Product 
SET AvailableQuantity = AvailableQuantity - ?, version = version + 1
WHERE ID_product = ? AND Blocked = 0 AND Deleted = 0 AND AvailableQuantity >= ? AND version = ?

2. 强制校验UPDATE影响行数

执行executeUpdate()后必须检查返回的影响行数：

若行数为0，分情况抛出异常：
- 库存不足：NotAvailableProductException
- 版本不匹配：OptimisticLockException

3. 重构checkAndDecreaseQuantity方法

以下是优化后的方法实现：

public int checkAndDecreaseQuantity(Long ID_product, int quantity) throws NotAvailableProductException, OptimisticLockException {
    PreparedStatement ps = null;
    PreparedStatement checkPs = null;
    ResultSet rs = null;

    try {
        // 1. 获取当前商品的版本和库存（用于乐观锁校验）
        String checkSql = "SELECT version, AvailableQuantity FROM Product WHERE ID_product=? AND Blocked=0 AND Deleted=0";
        checkPs = conn.prepareStatement(checkSql);
        checkPs.setLong(1, ID_product);
        rs = checkPs.executeQuery();

        if (!rs.next()) {
            throw new NotAvailableProductException("Product not found, blocked or deleted");
        }

        long currentVersion = rs.getLong("version");
        int currentStock = rs.getInt("AvailableQuantity");
        rs.close();
        checkPs.close();

        if (currentStock < quantity) {
            throw new NotAvailableProductException("Insufficient stock for product");
        }

        // 2. 执行原子更新操作
        String updateSql = " UPDATE Product "
                + " SET AvailableQuantity = AvailableQuantity - ?, version = version + 1 "
                + " WHERE "
                + " ID_product = ? AND Blocked = 0 AND Deleted = 0 AND version = ?";

        ps = conn.prepareStatement(updateSql);
        ps.setInt(1, quantity);
        ps.setLong(2, ID_product);
        ps.setLong(3, currentVersion);

        int affectedRows = ps.executeUpdate();
        if (affectedRows == 0) {
            throw new OptimisticLockException("Optimistic lock failed: product version has been modified by another request");
        }

        // 3. 返回更新后的库存
        return currentStock - quantity;

    } catch (SQLException e) {
        throw new RuntimeException("Database error during stock update", e);
    } finally {
        // 关闭资源
        try { if (rs != null) rs.close(); } catch (SQLException e) {}
        try { if (checkPs != null) checkPs.close(); } catch (SQLException e) {}
        try { if (ps != null) ps.close(); } catch (SQLException e) {}
    }
}

4. 事务隔离级别确认

确保事务隔离级别至少为READ_COMMITTED（MySQL默认是REPEATABLE READ，也可满足需求），避免脏读和不可重复读问题。

内容的提问来源于stack exchange，提问作者Michael

乐观锁实现异常：并发请求下库存扣减与事务问题求助

问题场景 #

控制器代码 #

ProductDAO.checkAndDecreaseQuantity方法代码 #

问题根源分析 #

解决方案 #

1. 合并查询与更新为原子SQL操作 #

2. 强制校验UPDATE影响行数 #

3. 重构checkAndDecreaseQuantity方法 #

4. 事务隔离级别确认 #