mysql - Spring Boot JDBC 和 MySQL 中的原子性

Question

我敢肯定有很多类似的问题。但是我仍然不确定 Spring boot JDBC 中的原子性。

所以我的项目是一个基本的在线服装店。用户将一些产品添加到购物车然后结帐的地方。每个用户都有一些积分（虚拟货币）。所以要实现结帐，我需要对数据库执行原子操作。基本上，结帐时我必须检查两件事。

1. 用户在结帐时有足够的积分。
2. 有足够的库存。

因此，我没有执行正常操作，而是JDBCtemplate.update()通过获取 Connection 然后使用 Prepared 语句来执行查询和更新。下面是我的结帐功能代码。

  public boolean validCart(String username) throws SQLException {
        Connection conn = DataSourceUtils.getConnection(template.getDataSource());
        try {
            conn.setAutoCommit(false);
            PreparedStatement ps = conn.prepareStatement("SELECT credits from user where username = ?");
            ps.setString(1, username);
            ResultSet rs = ps.executeQuery();
            rs.next();
            int credits = rs.getInt(1);
            ps = conn.prepareStatement("SELECT cart.quantity*stock.price from cart,stock where cart.username = ? and stock.cloth_id = cart.cloth_id and stock.size = cart.size");
            ps.setString(1, username);
            rs = ps.executeQuery();
            rs.next();
            int cost = rs.getInt(1);
            Boolean flag = false;
            if(credits >= cost){
                String sql = "SELECT COUNT(*) from cart,stock where cart.username = ? and cart.size = stock.size and cart.cloth_id = stock.cloth_id and cart.quantity > stock.quantity";
                ps = conn.prepareStatement(sql);
                ps.setString(1, username);
                rs = ps.executeQuery();
                rs.next();
                int bad = rs.getInt(1);
                sql = "SELECT COUNT(*) FROM cart WHERE username = ? and  (cloth_id,size) NOT IN (SELECT cloth_id,size from stock)";
                ps = conn.prepareStatement(sql);
                ps.setString(1, username);
                rs = ps.executeQuery();
                rs.next();
                bad += rs.getInt(1);
                if(bad == 0){
                    sql = "UPDATE cart INNER JOIN stock on cart.cloth_id = stock.cloth_id and stock.size = cart.size SET stock.quantity = stock.quantity - cart.quantity where cart.username = ?";
                    ps = conn.prepareStatement(sql);
                    ps.setString(1, username);
                    ps.executeUpdate();
                    sql = "UPDATE user SET credits = credits - ? where username = ?";
                    ps = conn.prepareStatement(sql);
                    ps.setInt(1, cost);
                    ps.setString(2, username);
                    ps.executeUpdate();
                    sql = "INSERT INTO order_details(username,cloth_id,size,quantity,price) SELECT cart.username,cart.cloth_id,cart.size,cart.quantity,stock.price from cart,stock where cart.username = ? and cart.cloth_id = stock.cloth_id and cart.size = stock.size";
                    ps = conn.prepareStatement(sql);
                    ps.setString(1, username);
                    ps.executeUpdate();
                    sql = "DELETE FROM cart WHERE username = ?";
                    ps = conn.prepareStatement(sql);
                    ps.setString(1, username);
                    ps.executeUpdate();
                    flag = true;
                }
            }
            conn.commit();
            conn.setAutoCommit(true);
            return flag;
        } catch (SQLException e) {
            conn.rollback();
            e.printStackTrace();
        }
        return false;
    }

但是我仍然不确定如果操作是原子的，那么这里不会发生竞争条件的问题。

一般来说，执行此类操作的最佳方法应该是什么？

标签： mysqlspring-bootspring-jdbcrace-conditionacid