0

I have one method which has merge query inside it. If entry already not present this method inserts new row and if already present updates the existing record. The problem in this method is when 2 threads executes this method at same time. First thread enters in method and doesn't commit transaction and at same time second thread enters. Now one of them inserts row, second one is also trying to insert instead of updating and causing unique constraint error. I am wondering why this is happening, second thread should update instead of inserting new row. I am new to hibernate. So is it hibernate transaction problem or something else.

Please help me to understand this. Thanks. Below is the mergeFunction() code added for clarity. when simulated log line printed in below sequence

  • updating MY_TABLE ... thread-1

  • updating MY_TABLE ... thread-2

  • updated MY_TABLE ... thread-1

  • Unique Key constraint error ... thread-2

This problem is simulating only few times. When luckily 2 threads tries to execute same method.

    public void mergeFunction(long customerId, long assetId, String module, Status status, String errorMsg) {

    log.debug("Updating MY_TABLE for  asset {}  module {} status ={} customer ={}", assetId, module, status.name(),customerId);
    StatelessSession statelessSession = session.getSessionFactory().openStatelessSession();
    try {
        Transaction tx = statelessSession.beginTransaction();
        try {

            String sql = "merge into MY_TABLE x " +
                    "using (select " + customerId + " customer_id, '" + module + "' module, " + assetId + " asset_id from dual) y " +
                    "on (x.asset_id = y.asset_id and x.module = y.module) " +
                    "when matched then " +
                    "  update set x.status = :status, x.ERROR = :errMsg where x.asset_id = :aid and x.module = :module " +
                    "when not matched then " +
                    "  insert (id, uuid, customer_id, module, asset_id, status, activated_on) " +
                    "  values (id_MY_TABLE.nextval, portal_pck.get_uuid(), " + customerId + ", '" + module + "', " + assetId + ", '" + status + "', sysdate)";

            statelessSession.createSQLQuery(sql).setParameter("aid", assetId)
                    .setParameter("module", module)
                    .setParameter("status", status.name())
                    .setParameter("errMsg", StringUtils.isEmpty(errorMsg) ? " " : errorMsg)
                    .executeUpdate();
            tx.commit();
            log.debug("Updated MY_TABLE for {} asset for {} status ={} customer={}",assetId,module, status.name(),customerId);

        } catch (Exception exe) {
            log.debug("error while updating MY_TABLE table for asset {} module{} status {}  for customer {} exception ={}",
                    assetId, module, status.name(), customerId, exe);
            tx.rollback();
            throw exe;
        }

    } finally {
        statelessSession.close();
    }
}
Datta
  • 169
  • 1
  • 1
  • 10
  • Could you please also add the log trace for when the error occurs. – sidmishraw May 20 '19 at 17:41
  • @sidmishraw added. Thanks – Datta May 20 '19 at 18:00
  • one more question, does the log message inside the catch block execute for thread 2 when it throws the constraint violation exception? – sidmishraw May 20 '19 at 21:29
  • Yes log inside catch executed. – Datta May 21 '19 at 02:35
  • I've been thinking, hibernate depends on the underlying DB for concurrency control –– in this case Oracle. How does the merge query behave natively in Oracle when you try execute it concurrently? [https://stackoverflow.com/questions/1938671/concurrency-in-hibernate] and [https://docs.jboss.org/hibernate/orm/5.2/userguide/html_single/Hibernate_User_Guide.html#transactions] are good reads. Also, could you please add your application details too like if it is using JTA, hibernate version, etc. – sidmishraw May 21 '19 at 21:21
  • It's a web application running in tomcat. and using hibernate-3.6.0 oracle 12c and java 8 version. – Datta May 23 '19 at 04:42

0 Answers0