We hope that when someone purchases, check whether the quantity of the product is sufficient. If there is a surplus in the inventory, then the user will be able to successfully purchase it, and then change the inventory. If the user queues up to buy one by one, of course there is no problem.
But in reality, it may be that multiple users come to purchase at the same time and check the inventory at the same time. This may be that the inventory is only enough for one of them to purchase, but since the inventory has not been reduced, several people will successfully purchase it, and then the inventory will be reduced to negative and oversold. This is very likely to occur when a large number of users purchase at the same time at the same time.
So we adjust the order. When some users purchase, we first reduce the inventory. Then you must ask, how to reduce it? Will it also reduce when the inventory is not enough for one person?
We assume that each product has a unique purchase code (pre-made before starting the purchase), and the number of purchase codes the user grabs is the number of copies he bought. Then when a user purchases, our first step is to change the status of the lucky code from valid to invalid, and mark it with its buyer ID.
The code copy is as follows: "UPDATE `lottery_number` SET `status` = invalid status,`user_id` = buyer user ID,`current_time`= timestamp WHERE `goods_id` = snatched product ID AND `status`=valid status LIMIT Purchased copies";
In this way, mysql will give us a return result, called affecting the number of rows, which means that this statement updates the data of how many rows it affects. This affecting the number of rows is the number of copies of the product it actually purchased. If the number of rows is 0, it means that no copy was successfully purchased, which means that the product has been rushed to purchase.
Java implementation:
/** * Generate the purchase code of the product <Insert a large amount of data> * * @param goodsIssue * @author Nifury */public void insertLotteryNumbers(GoodsIssue goodsIssue) { String prefix = "INSERT INTO `lottery_number` (`goods_id`, `periods`,`luck_number`, `create_time`, `status`, `issue_id` ) VALUES /n"; Timestamp now = new Timestamp(System.currentTimeMillis()); Connection con = null; try { con = jdbcTemplate.getDataSource().getConnection(); con.setAutoCommit(false); PreparedStatement pst = con.prepareStatement(""); Long total = goodsIssue.getTotalShare();// Total number of visitors for (int i = 0; i < total; i += 10000) {// 10,000 submissions once StringBuffer suffix = new StringBuffer(); List<Integer> numbers = new ArrayList<Integer>(); for (int j = 0; j < 10000 && i+j < total; j++) { numbers.add(10000001 + i + j); } Collections.shuffle(numbers);//Scramble lucky code for (int n = 0,length = numbers.size(); n < length; n++) { suffix.append("(" + goodsIssue.getGoodsId() + "," + goodsIssue.getPeriods() + "," + numbers.get(n) + ",'" + now.toString() + "'," + 1 + "," + goodsIssue.getIssueId() + ")/n,"); } // Build the complete sql String sql = prefix + suffix.substring(0, suffix.length() - 2); pst.addBatch(sql); pst.executeBatch(); con.commit(); } con.setAutoCommit(true);// Restore pst.close(); con.close(); } catch (Exception e) { e.printStackTrace(); try {// Transaction rollback con.rollback(); con.setAutoCommit(true); con.close(); } catch (SQLException e1) { e1.printStackTrace(); }// Restore }}Assign a purchase code (our business needs to show the purchase code to the purchaser, so there is a return)
/** * Randomly get the purchase code through the product issue_id (each product has a unique issue_id per issue) (the purchase code used will be set to invalid state) * @param issueId * @param amount The number of purchase codes to be obtained* @param userId * @return LotteryNumber object list* @author Nifury 2016-7-22 */public List<LotteryNumber> queryByNewIssueId2(Long issueId, Long amount,Long userId) { List<LotteryNumber> numberList = new ArrayList<LotteryNumber>(); try { long currentTime=System.currentTimeMillis(); String updateUserId = "UPDATE `lottery_number` SET `status` = 0,`user_id` = ?,`current_time`= ? WHERE `issue_id` = ? AND `status`=1 LIMIT ? "; int rownum=jdbcTemplate.update(updateUserId, userId, currentTime, issueId, amount ); if(rownum>0){//There are still valid purchase codes left and right?Object[] buyargs={issueId, userId ,currentTime}; numberList = jdbcTemplate.query(QUERY + " WHERE `issue_id` = ? AND `status` = 0 AND `user_id` = ? AND `current_time`= ?", buyargs, LotteryNumberMapper); } } catch (DeadlockLoserDataAccessException e) { System.out.println("------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------The above is all the content of this article. I hope it will be helpful to everyone's learning and I hope everyone will support Wulin.com more.