Hibernate with sqlserver deadlock issue

Question

Following are the two tables with its entity classes.

tbl_rules

| rule_id | rule_name |

    @Entity
    @Table(name = "db_user_name.tbl_rules")
    public class Rule implements Serializable {
        private static final long serialVersionUID = 1L;

        @Id
        @Basic(optional = false)
        @GenericGenerator(name = "incre", strategy = "increment")
        @GeneratedValue(generator = "incre")
        @Column(name = "rule_id", unique = true, nullable = false)
        private long ruleId;

        @Column(name = "rule_name", length = 250)
        private String ruleName;

        @OneToMany(fetch = FetchType.LAZY, mappedBy = "rules")
        private Set<Benchmark> benchmarks = new HashSet<Benchmark>(0);
        ... getters and setters
    }

tbl_benchmark

| benchmark_id | rule_id |

@Entity
@Table(name = "tbl_benchmark", catalog = "db_user_name")
@DynamicUpdate(value = true)
public class Benchmark implements Serializable {
    private static final long serialVersionUID = 1L;

    @Id
    @Basic(optional = false)
    @Column(name = "benchmark_id", unique = true, nullable = false)
    private Long benchmarkId;

    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "rule_id", nullable = false)
    private Rule rules;
    .. getter and setters
}

Facing deadlock issue with sql server db in the following case

• HibernateSessionManager.beginTransaction();
• call saveRule(rule) // in the backend rule and benchmark both tables are locked (using sql server locked tables query)
• call saveBenchmark(benchmark) // deadlock at this method
• HibernateSessionManager.commit();

Code where deadlock is happened:

HibernateSessionManager.beginTransaction();
            UserIdManager.setCurrentGroupId(2);
            if (savingObjects.get(AmlConstants.USERCREDENTAILS_STRING) != null){
                    userCredentials = (UserCredentials) savingObjects.get(AmlConstants.USERCREDENTAILS_STRING);
                    Util.setAuditLogField(AmlConstants.USERIDSTRING);
                    this.getAmlDAOFactory().getUserCredentialsDAO().updateUserDetails(userCredentials);
                if (savingObjects.get(AmlConstants.USERBRANCHMAPPING_STRING) != null){
                    userBranchMapping = (UserBranchMapping) savingObjects.get(AmlConstants.USERBRANCHMAPPING_STRING);
                      Util.setAuditLogField(AmlConstants.BRANCH_STRING);
                      this.getAmlDAOFactory().getUserBranchMappingDAO().saveUserBranchMapping(userBranchMapping);
                }
            }
            HibernateSessionManager.commit();

saveRule:

@Override
    public Rule saveRule(Rule rule) throws Exception {
        try {
            getSession().saveOrUpdate(rule);
            getSession().flush();
        } catch (RuntimeException e) {
            e.printStackTrace();
            throw e;
        }
        return rule;
    }

saveBenchmark:

@Override
    public Benchmark saveBenchMark(Benchmark benchmark) throws Exception {
        try {
            if (benchmark.getBenchmarkId() == null)
                benchmark.setBenchmarkId(getBenchmarkCount() + 1);
            getSession().clear();
            getSession().saveOrUpdate(benchmark);
            getSession().flush();
        } catch (RuntimeException e) {
            // logger.error("Runtime error while saving benchmark", e);
            e.printStackTrace();
        } catch (Exception e) {
            logger.error("Exception while saving benchmark " + e.getMessage(), e);
        }
        return benchmark;
    }

Spring-Hib confg file:

    <bean id="dataSource" class="org.apache.commons.dbcp2.BasicDataSource" 
destroy-method="close">
            <property name="driverClassName" value="${jdbc.driverClassName}" />
    ..
     <property name="hibernateProperties">
                <props>
                   <prop key="hibernate.dialect">com.aml.hibernate.SQLServerCustomeDialect</prop>
                   <prop key="hibernate.character_encoding">UTF-8</prop>
                   <prop key="hibernate.connection.useUnicode">true</prop>
                    <prop key="hibernate.show_sql">true</prop>
                    <prop key="hibernate.generate_statistics">false</prop>
                </props>
            </property>
    ..

HibernateSessionManager.java

public class HibernateSessionManager {

    public  static Logger logger = Logger.getLogger(HibernateSessionManager.class);
    public static final ThreadLocal<Session> currentSession = new ThreadLocal<Session>();
    public static final ThreadLocal<java.util.List<Session>> sessionList = new ThreadLocal<java.util.List<Session>>();

    /** Store transaction object on thread local
     * this helps to make a request processing transactional */
    public static final ThreadLocal<Transaction> transaction = new ThreadLocal<Transaction>();
    public static final ThreadLocal<Map<String, Transaction>> transactionMap = new ThreadLocal<Map<String, Transaction>>();

    /** keep the beginner method path which helps to commit on the same method only 
     * we are not supposed to make nested commits under a single request */
    public static final ThreadLocal<String> callerXPath = new ThreadLocal<String>();



    /**
     * Returns existing hibernate session binded with current request thread, 
     * if no session already bind with current thread then it will open a new session, bind to current thread 
     * and returns the session object
     * 
     * @param sessionFactory
     * @return
     * @throws HibernateException
     */
    public static Session currentSession(SessionFactory sessionFactory) throws HibernateException {

        Session s = (Session) currentSession.get();
        // Open a new Session, if this Thread has none yet
        if (s == null || !s.isOpen())
        {
            s = sessionFactory.openSession();
            currentSession.set(s);
            if(sessionList.get()==null)
                sessionList.set(new LinkedList<Session>());
            sessionList.get().add(s);
            logger.debug("Opened new session:"+currentSession.get().hashCode());
        }else{
            logger.debug("returning existing session:"+currentSession.get().hashCode());
        }
        return s;
    }


    /**
     * Closes all the sessions binded with current request thread
     * @throws HibernateException
     */
    public static void closeSession() throws HibernateException {
        currentSession.set(null);
        transaction.set(null);
        callerXPath.set(null);

        try{
            if(sessionList.get()!=null)
                for (int i = 0; i < sessionList.get().size(); i++) {
                    Session s = sessionList.get().get(i);
                    try{
                        if (s != null && s.isOpen())
                            s.close();
                        logger.debug("Closed session - session local:"+ (s!=null?s.hashCode(): ""));
                    }catch (Exception e) { logger.debug("Error while closing session: ", e); }
                }
            transactionMap.get().clear();
        }catch (Exception e) { logger.debug("Error while closing session: ", e); }
        sessionList.set(null);
        transactionMap.set(null);
    }




    // ------------------- Transaction management ------------------
    /**
     * Starts a new hibernate transaction on the session binded to current request thread
     * if there is already a transaction started on this thread, ignores creation of another transaction
     * all the db calls on a single request thread has to come under a single transaction
     * @return
     */
    public static boolean beginTransaction(){
        try{
            logger.debug("beginTransaction............... ");

            Transaction t = transaction.get();
            if(t == null && callerXPath.get()==null){
                Session s = currentSession.get();
                t = s.beginTransaction();
                t.registerSynchronization(new Synchronization() {

                    @Override
                    public void beforeCompletion() {
                        logger.debug("Transaction-beforeCompletion............... ");

                    }

                    @Override
                    public void afterCompletion(int status) {
                        logger.debug("Transaction-afterCompletion............... "+status);

                    }
                });
                transaction.set(t);
                callerXPath.set(getCallerMethodInvolvedinTransaction());

                if(transactionMap.get()==null)
                    transactionMap.set(new HashMap<String, Transaction>());

                transactionMap.get().put(callerXPath.get(), t);
                logger.debug("Started new hibernate transaction:"+t);
            }
        }catch (Exception e) {
            logger.error("Error while starting new transaction: ", e);
            return false;
        }
        return true;
    }


    /**
     * if we already have a hibernate transaction created on the current request thread and some thing is committed on it
     * it will rollback the changes done after the transaction initialization
     */
    public static void rollback(){
        try{
            Transaction t = transactionMap.get().get(callerXPath.get());
            if(t != null){
                t.rollback();
                logger.debug("Roll back success on transaction:"+t);
            }
        }catch (Exception e) {
            logger.error("Exception while trying to rollback", e);
        }
    }


    /**
     * Commits all the changes done after the transaction started on the current request thread
     * this accepts the commit command from the only method which started the transaction
     * This will unlink the current session and then currentSession() method can give another session as existing one is unlinked on the thread local
     */
    public static void commit(){
        try{
            logger.debug("commit............... ");

            Transaction t = transaction.get();
            if(t != null /*&& !t.wasCommitted()*/
                    && callerXPath.get()!=null && callerXPath.get().equals(getCallerMethodInvolvedinTransaction())){
                t.commit();

                currentSession.get().clear();
                currentSession.set(null);
                transaction.set(null);
                callerXPath.set(null);

                logger.debug("Commit success on transaction:"+t);
            }
        }catch (Exception e) {
            logger.error("Exception while trying to commit", e);
        }
    }









    /**
     * get the caller method xpath: <package>.<classname>.<methodname>
     * @return
     */
    public static String getCallerMethodInvolvedinTransaction() {
        try{
            StackTraceElement[] stElements = Thread.currentThread().getStackTrace();
            return stElements[3].toString().split("\\(")[0];
            /*for (int i = 3; i < stElements.length; i++) {
                String rawFQN = stElements[i].toString().split("\\(")[0];
                String className = rawFQN.substring(0, rawFQN.lastIndexOf('.'));
                String methodName = rawFQN.substring(rawFQN.lastIndexOf('.')+1);
                Object carObj = Class.forName(className).newInstance();

                ClassPool pool = ClassPool.getDefault();
                CtClass cc = pool.get(className);
                CtMethod methodX = cc.getDeclaredMethod(methodName);
                int xlineNumber = methodX.getMethodInfo().getLineNumber(0);

                Method method = carObj.getClass().getMethod(methodName);
                if (method.isAnnotationPresent(JCTransaction.class))
                {
                    return rawFQN;
                }
            }*/
        }catch (Exception e) {
            logger.error("" , e);
        }
        return null;
    }
}

But same working fine with oracle db (with oracle hib properties).

Answer 1

There has to be something wrong with your code or you should never lock yourself out. Two different connections can block each other, but one connection should never block on its own locks. I haven't looked at the code in detail, I'll focus on why you get the problem with SQL Server and not with Oracle.

Oracle always uses versioning for rows, so rows are never locked just because they are read. SQL Server on the other hand normally takes read locks and a read lock will prevent writes from other sessions. You can probably change the SQL Server isolation level to READ_COMMITED_SNAPSHOT in order to hide the issue, but it will still be there.

I don't understand why you are clearing the session in several locations, that should almost never be done. I also don't understand all that code dealing with the transaction in HibernateSessionManager, that is probably the root cause of the issue. Somehow you're running with more than one transaction. Keep things simple instead and the issue is likely to go away!