java - How to run sql statement in sequence

Question

I have 2 servlet that runs sql statements:

1. Runs a simple insert statement and commit.
2. Validates data and updates multiple rows. If 1 row does not satisfy the validation, a rollback statement is run.

The 2 servlet updates different table.

servlet1 code sample:

try {
        connection.setAutoCommit(false);
        int result= SQLOperations.createImport(identifier, data, connection);
        if(result>0&&SQLOperations.deleteImport(String.valueOf(result-2000), connection)){
            connection.commit();
        }else{
            connection.rollback();
        }
    } catch (SQLException e) {
        e.printStackTrace();
    }

servlet2 sample code:

            String product[] = request.getParameterValues("product");
            String quantity[] = request.getParameterValues("quantity");
            for(int i=0;i<product.length;i++){ 
                subResult=0;
                subResult=SQLOperations.createSaleItems(product[i], quantity[i], connection);       
                

                /*code here is a bit complicated basically
                   it checks if quantity is less than or 
                  equal to available stocks. if true, it 
                  deducts quantity to the available stocks 
                  and updates the stock table. if false it 
                  executes a rollback and breaks the loop*/
 
             }

problem encountered:

1. while servlet2 is running, around 2-3 create statements are executed.
2. another user triggers servlet1 which runs a create statement on a different table and then the commit statement is executed.
3. servlet2 then encounters a product with a quantity greater than the available stocks. this triggers the rollback statement.
4. the result shows 2-3 create statement from servlet2 is commited, due to the fact that servlet1 was executed while servlet2 was still running.

How do I avoid this? Any help would be appreciated. Thanks

question from:https://stackoverflow.com/questions/65873406/how-to-run-sql-statement-in-sequence

Answer 1

Waitting for answers