CODEWITHSUNDEEP
javamultithreadingoracle-databasehibernate
Youssef NAIT
Youssef NAIT

Reputation: 1530

SELECT FOR UPDATE locking

I'm using hibernate for data persistence, and I'm counting on the SELECT FOR UPDATE sql statement as a locking system to ensure only one thread is working on my task, but when the internet connection is interrupted, the threads get blocked in the SELECT FOR UPDATE statement.

My theory: the first thread gets in the lock function, executes the SQL statement, and puts a lock, and before committing any changes to the database Table, the connection gets interrupted the thread fails to commit, and unlock the table, therefor the lock keeps living, and when the next threads execute the SQL statement, they are blocked.

In the code snippet bellow, my task is inside the while block, i have represented it with "..."

Code snippet

while(lockIsOk()){
    ...
}

And lockIsOk() is the function that handles the lock.

Code snippet

private boolean lockIsOk(){
    Session session = null;
        Transaction transaction = null;
        try {
            session = HibernateUtil.getSessionFactory().getCurrentSession();
            transaction = session.beginTransaction();
            SQLQuery request = DaoFactory.getCurrentSession().createSQLQuery("SELECT * FROM SYNCHRO_STATUS where id = 1 FOR UPDATE ");

            List<Object> resultList = request.addEntity("synchro_status", SynchroStatusModel.class).list();
            if(!resultList.isEmpty()) {
                SynchroStatusModel docSynchroStatusModel = (SynchroStatusModel) resultList.get(0);
                updateSync(docSynchroStatusModel);
                transaction.commit();
                return true;    
            } else {
                try {
                    Thread.sleep(configurator.getPropertyAsInt(SolrIndexerForStiProperties.SLEEP_TIME));
                } catch (InterruptedException e) {
                    // Restore the interrupted status
                    Thread.currentThread().interrupt();
                    ExploitLogger.error(ExploitError.EXECUTION_FAILED, SeverityError.MINOR, "Error while sleeping thread.", e, StiExploitId.ID_50114);
                }
            }
            // Commit the transaction
            transaction.commit();
        } catch (Exception e) {
            // Rollback the transaction
            ExploitLogger.error(ExploitError.UNATTEMPTED_ERROR, SeverityError.MAJOR, "Error while checking synchronization status table.", e, StiExploitId.ID_50115);
            HibernateTransactionHelper.rollbackTransaction(transaction);
        } finally {
            if (session.isOpen()) {
                session.close();
            }
        }
        return false;
}

private void updateSync(){
        SynchroStatusDao synchroStatusDao = DaoFactory.getSynchroStatusDao();
        String stiName = ManagementFactory.getRuntimeMXBean().getName();
        docSynchroStatusModel.setStiName(stiName);
        docSynchroStatusModel.setSynchroEnabled(1);
        docSynchroStatusModel.setLastActionDate(Calendar.getInstance().getTime());
        synchroStatusDao.update(docSynchroStatusModel);
}

I can provide the log file if needed.

Questions :

Upvotes: 1

Views: 2091

Answers (1)

Warren Dew
Warren Dew

Reputation: 8938

Your theory is reasonable and likely correct.

I would do two things to fix the situation.

  1. If possible, reduce the amount of time you hold the transaction open to reduce the vulnerability to broken connections.

  2. If your database allows, set a database connection timeout time. That way, the database will not hold on to broken connections indefinitely. Note that this solution may require code changes in your application so the application can either keep connections open or reconnect as appropriate.

Upvotes: 2

Related Questions