I'm trying to implement an e-commerce website using java, MySQL database, and JDBC.
I have some problem managing the concurrency with optimistic lock. In fact if the user "A" try to buy 3 items of a product and the user "B" try to buy 2 item of the same product at the same time (but the total available item of the product is 4) i have unpredictable results. In fact I tried it and there were left 2 available items, the version of the column was incremented by 1 correctly but both users made a payment and complete a order (there wasn't a rollback for one of the two transaction).
Seems that the request of user B overwrite the request of user A because they both check the version before one of them increase it. So when they arrive to the commit point the last one will overwrite the results of the other.
Of course I'm doing wrong something and maybe i even misunderstood the concept of optimistic lock, can you please help me?
This is the controller:
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");
/* I find the pending order linked with logged user*/
OrderDAO orderDAO = daoFactory.getOrderDAO();
Order order = orderDAO.findByLoggedUserAndPendingStatus(loggedUser);
//I find the cart
CartDAO cartDAO = daoFactory.getCartDAO();
Cart cart = cartDAO.findCartByOrder(order);
//Take all the cart item inside the cart
CartItemDAO cartItemDAO = daoFactory.getCartItemDAO();
List<CartItem> cartItems = cartItemDAO.getCartItems(cart);
//I create the payment
PaymentDAO paymentDAO = daoFactory.getPaymentDAO();
paymentDAO.create(cart.getPrice_cart(), order, CardNumber, CVV, Name, Expire);
//Set complete flag to the order
orderDAO.setCompleteStatus(order);
//Decrease the quantity of the product
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) {
}
}
The productDAO.checkAndDecreaseQuantity is the only method that implements optimistic lock and can throw NotAvailableProductException and OptimisticLockException:
public int checkAndDecreaseQuantity(Long ID_product, int quantity) throws NotAvaibleProductException, 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 NotAvaibleProductException("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;
}
I know that there are several security problem such as storing the credit card information into the database and store the UserID in the cookie directly, but the website won't be published, it's just for an exam where the professor told us to don't manage the security (he just mind the software engineering).
If you see that I'm doing something wrong please let me know, thank you in advance.
Your update statement will be executed without error even when the version is not the one expected. You need to check that the statement was correctly executed:
sql = " UPDATE Product "
+ " SET AvailableQuantity=?, version=? "
+ " WHERE "
+ " ID_product=? AND Blocked=0 AND Deleted=0 AND version=?";
try (PreparedStatement 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());
if (ps.executeUpdate() == 0) {
throw new OptimisticLockException("Exception lock optimistic quantity update");
}
}