Concurrency handling with DB and URL interaction

Question

I have a billing application that takes a request check in the db if the customer has already paid, if not paid it hits a billing API and then update the response in the database.

I am facing issues when the customer send request multiple time by clicking the "Bill me" button 3-4 times. All request are hitting the server with a difference in ms. As a result, the customer is getting charged multiple times because the result didn't get updated as the first request takes some time and in the meantime the second request read the status from the db and go again for charging. Below is the code. Please let me know what best practices I should follow to prevent the same. I have tried making the servlet SingleThreadModel but it didn't help

 protected void processRequest(HttpServletRequest request, HttpServletResponse response)
         throws ServletException, IOException {
    response.setContentType("text/html;charset=UTF-8");
    try (PrintWriter out = response.getWriter()) {
        /* TODO output your page here. You may use following sample code. */
        String mobilenumber = request.getParameter("mobilenumber");
        String param2 = request.getParameter("param2");
        String checkifthecustomerischarged = checkifcharge(mobilenumber);
        if (checkifthecustomerischarged.equals("NOTOK")) {
            String status = hittocharge(mobilenumber, param2);
            int i = updatestatus(mobilenumber, status);
            if (i > 0) {
                out.println("Status Updated ");
            } else {
                out.println("Status Not update Updated ");
            }
        } else {
            out.println("Already Charged");
        }
      }
  }

here I am checking in the table

 private String checkifcharge(String mobilenumber) {
    String st = "NOTOK" ; 
    try {
        Connection conn = null ; 
        ResultSet rs  = conn.createStatement.executeQuery("select sno from table where mobilenumber = "+mobilenumber);
       if(rs.next()){
       st = "OK";
       }

    } catch (Exception e) {
        e.printStackTrace();
     }
  return st ;
  }

here i am updating the status

   private int updatestatus(String mobilenumber, String status) {
    int st = 0 ; 
    try {
        Connection conn = getConnection() ; 
       st  = conn.createStatement.executeQuery("update table set status =1    where  mobilenumber ="+mobilenumber);

       releaseConnection(conn);
    } catch (Exception e) {
        e.printStackTrace();
    }
 return st ;
}

I sincerely hope this is code is not being used for actual billing. — Kayaman
– Kayaman, Commented Aug 18, 2015 at 6:53
actually this is not live , i am trying to make one.. let me know your thought on this. — Mohit H
– Mohit H, Commented Aug 19, 2015 at 10:22
I think you're too inexperienced for this. Billing involves money, and nobody will trust money to a software that doesn't handle transactions properly. — Kayaman
– Kayaman, Commented Aug 19, 2015 at 10:24

Pelit Mamani · Accepted Answer · 2015-08-18 05:21:31Z

2

Normally, people don't just say "bill me" but "bill me for something specific" (e.g. "august 2015 payment for mobile number 123"). I would decide on an identifier for each billing (e.g. "08_2015_123") and include it in the billing request as well as the status table. Then you can use optimistic update, which is something in the lines of

st=statement.executeQuery("update table set status=1 where identifier='08_2015_123' and status<>1")
// if st==0 it probably means bill was previously payed, don't bill again

BTW even if you're absolutely sure you can't generate such an identifier, you could always generate some dummy random field in your form, so that at least you can identify multiple quick clicks on the same button (though it won't help for more complex situations, e.g. user that forgot that he payed a week ago). However with billing I would make an effort to find a proper identifier.

answered Aug 18, 2015 at 5:21

Pelit Mamani

2,3912 gold badges15 silver badges11 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Mohit H Over a year ago

hi pelit , thanks for reply but here i was expecting putting sync , threadsafe , singlethreadmodel life features, still there are billing models where one can be charged directly by just clicking from his mobile account.

Pelit Mamani Over a year ago

Hi, I'm afraid pessimistic locking (sync/threadsafe etc) won't help if the problem comes from multiple clicks on the mobile. Consider it... what would you lock and when? it's like the trigger for locking should be "the minute I render the button on mobile", which would be a performance nightmare.

Pelit Mamani Over a year ago

BTW if you're looking for a quick solution,just for multiple clicks, - you could just disable the button after the 1st click (and before sending our the request). But I felt that for the large picture, it's better to be able to identify what you're billing for.

Mohit H Over a year ago

no we cannot control the clicking as sometimes it is a button sometimes banner , we just provide the url to the third party , they hit it badly keeping the block syn didnt help , and neither keep the servlet after implementing singlethreadmodel ..

Pelit Mamani Over a year ago

Yes, as you probably realize it's because you still get several requests, so even if you put a pessimistic lock on everything - it would just make them run sequentially... that's why I think you need optimistic locking. Maybe in your case it would be enough to do "update table set status=1 where mobileNumber=123 AND status<>1 " and if this returns 0 it means a duplicate that shouldn't be billed.

Collectives™ on Stack Overflow

Concurrency handling with DB and URL interaction

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related