[FIXED] How to handle multiple request in Java servlet

Issue

I have a UI where there is HTML table with input fields as cells and some input field outside the HTML table, so there I am entering data and on click of save saving data into my data base using servlets

It is working fine when only one user is using it at a time but causing issue when two user clicks save at same time

What I am doing is:

• After Clicking on save I am calling my servlet class where I have written a code to insert the data into DB
• So in my servlet class firstly I am running a query to get max+1 no. of a column from db and then saving it and using that value further in my insert query

Issue I am facing is:

• When two user clicks save on same time, the query which is to get max+1 runs and the next query which is to insert data runs but the second one throws error as java.sql.BatchUpdateException: Duplicate entry '2' for key 'PRIMARY' because that one is primary key column in db and it can't be duplicate

• but why it is taking only 2 like only one no when two user has clicked save button it should take 2,3 I think Here I am not handling the multiple request in my servlet which I don't even know how to handle, I have googled a lot and read there that servlets it self manages the multiple processing at one time

• So my issue is I want to insert data into my db when multiple user clicks save button at single time, if there are two user clicking save at same time it is causing issue

My code

int grnNo;
Connection con = null;
Statement statement = null;

protected void doGet(HttpServletRequest request, HttpServletResponse response)
        throws ServletException, IOException {

}

protected void doPost(HttpServletRequest request, HttpServletResponse response)
        throws ServletException, IOException {

    String[] itemCode = request.getParameterValues("itemCodetd");
    String[] unitCode = request.getParameterValues("unittd");

    try {
        con = DBConnection.createConnection();
        statement = con.createStatement();

        String grnNoSql = "select max(GRNNUMBER)+1 as GRNNo from egoodsreceived";
        ResultSet resultSet1 = statement.executeQuery(grnNoSql);

        while (resultSet1.next()) {
            int grnNoLocal = resultSet1.getInt("GRNNo");

            if (grnNo != 0) {
                grnNo = grnNoLocal;
                System.out.println("in if  :" + grnNo);
            } else {
                grnNo = 1;
                System.out.println("in else  :" + grnNo);
            }

        }

        String query1 = "query to insert data";

        PreparedStatement ps = con.prepareStatement(query1);

        for (int i = 0; i < itemCode.length; i++) {
            if (itemCode[i] != "") {
                System.out.println("in for :" + grnNo);
                ps.setInt(1, grnNo);
                ps.setString(2, itemCode[i]);
                ps.setString(3, unitCode[i]);

                ps.addBatch();
            }
        }

        ps.executeBatch(); // here getting erro as duplicate primary key

    } catch (SQLException e) {
        System.out.println("SQL EXCPTION   91");
        e.printStackTrace();
    }

    doGet(request, response);
}

Solution

The problem is that you are using instance variables in servlets. There is only a single instance of each servlet in the JVM, so concurrent requests will attempt to use the same variable at the same time.

Refactor grnNo, con and statement to local variables, then wrap the select and insert into a single transaction.

Answered By - steven35