Database insertion synchronization

Question

I have a java code that generates a request number based on the data received from database, and then updates the database for newly generated

synchronized (this.getClass()) {
            counter++;
            System.out.println(counter);
            System.out.println("start " + System.identityHashCode(this));
            certRequest
                    .setRequestNbr(generateRequestNumber(certInsuranceRequestAddRq
                            .getAccountInfo().getAccountNumberId()));
            System.out.println("outside funcvtion"+certRequest.getRequestNbr());
            reqId = Utils.getUniqueId();
            certRequest.setRequestId(reqId);
            System.out.println(reqId);
            ItemIdInfo itemIdInfo = new ItemIdInfo();

            itemIdInfo.setInsurerId(certRequest.getRequestId());

            certRequest.setItemIdInfo(itemIdInfo);

            dao.insert(certRequest);
            addAccountRel();
            counter++;
            System.out.println(counter);
            System.out.println("end");

        }

the output for System.out.println() statements is `

1
start 27907101
com.csc.exceed.certificate.domain.CertRequest@a042cb
inside function request number66
outside funcvtion66
AF88172D-C8B0-4DCD-9AC6-12296EF8728D

2
end
3
start 21695531
com.csc.exceed.certificate.domain.CertRequest@f98690
inside function request number66
outside funcvtion66
F3200106-6033-4AEC-8DC3-B23FCD3CA380

4
end

In my case I get a call from two threads for this code.

If you observe both the threads run independently. However the data for request number is same in both the cases.

is it possible that before the database updation for first thread completes the second thread starts execution. ` the code for generateRequestNumber() is as follows:

public String generateRequestNumber(String accNumber) throws Exception {

String requestNumber = null;

if (accNumber != null) {

    String SQL_QUERY = "select CERTREQUEST.requestNbr from CertRequest as CERTREQUEST, "
            + "CertActObjRel  as certActObjRel where  certActObjRel.certificateObjkeyId=CERTREQUEST.requestId "
            + " and certActObjRel.certObjTypeCd=:certObjTypeCd "
            + " and certActObjRel.certAccountId=:accNumber ";

    String[] parameterNames = { "certObjTypeCd", "accNumber" };
    Object[] parameterVaues = new Object[] {
            Constants.REQUEST_RELATION_CODE, accNumber };
    List<?> resultSet = dao.executeNamedQuery(SQL_QUERY,
            parameterNames, parameterVaues);

    // List<?> resultSet = dao.retrieveTableData(SQL_QUERY);
    if (resultSet != null && resultSet.size() > 0) {
        requestNumber = (String) resultSet.get(0);
    }

    int maxRequestNumber = -1;

    if (requestNumber != null && requestNumber.length() > 0) {
        maxRequestNumber = maxValue(resultSet.toArray());
        requestNumber = Integer.toString(maxRequestNumber + 1);

    } else {
        requestNumber = Integer.toString(1);
    }
    System.out.println("inside function request number"+requestNumber);
    return requestNumber;
}
return null;

}

Kayaman · Accepted Answer · 2015-08-11 12:45:59Z

1

Databases allow multiple simultaneous connections, so unless you write your code properly you can mess up the data.

Since you only seem to require a unique growing integer, you can easily generate one safely inside the database with for example a sequence (if supported by the database). Databases not supporting sequences usually provide some other way (such as auto increment columns in MySQL).

answered Aug 11, 2015 at 12:45

Kayaman

73.8k6 gold badges91 silver badges128 bronze badges

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

5 Comments

pkn1230 Over a year ago

I got your point, however I don't just require unique growing integer, first I need to update two database tables and then based on that updation again generate the next number.

Kayaman Over a year ago

@pkn1230 In your example the number that follows 66 is 67. Doesn't sound like too much logic in generating that number.

pkn1230 Over a year ago

Please ignore it, it was a old output.

Kayaman Over a year ago

Well, you may want to modify your generateRequestNumber so it's thread-safe. The code you're displaying doesn't really tell anything. You left out all the actual implementation.

pkn1230 Over a year ago

I suppose I have a transaction synchronisation issue.

Collectives™ on Stack Overflow

Database insertion synchronization

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