Multithread issues in using Hibernate SessionFactory

Question

Have a table 'temp' .. Code:

CREATE TABLE `temp` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `student_id` bigint(20) unsigned NOT NULL,
  `current` tinyint(1) NOT NULL DEFAULT '1',
  `closed_at` datetime NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_index` (`student_id`,`current`,`closed_at`),
  KEY `studentIndex` (`student_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

The corresponding Java pojo is http://pastebin.com/JHZwubWd . This table has a unique constraint such that only one record for each student can be active.

2) I have a test code which does try to continually add records for a student ( each time making the older active one as inactive and adding a new active record) and also in a different thread accessing some random ( non-related ) table. Code:

public static void main(String[] args) throws Exception {
        final SessionFactory sessionFactory = new AnnotationConfiguration().configure().buildSessionFactory();
        ExecutorService executorService = Executors.newFixedThreadPool(1);
        int runs = 0;
        while(true) {
            Temp testPojo = new Temp();
            testPojo.setStudentId(1L);
            testPojo.setCurrent(true);
            testPojo.setClosedAt(new Date(0));
            add(testPojo, sessionFactory);
            Thread.sleep(1500);

            executorService.submit(new Callable<Object>() {
                @Override
                public Object call() throws Exception {
                    Session session = sessionFactory.openSession();
                    // Some dummy code to print number of users in the system.
                    // Idea is to "touch" the DB/session in this background
                    // thread.
                    System.out.println("No of users: " + session.createCriteria(User.class).list().size());
                    session.close();
                    return null;
                }
            });
            if(runs++ > 100) {
                break;
            }
        }

        executorService.shutdown();
        executorService.awaitTermination(1, TimeUnit.MINUTES);
    }

private static void add(final Temp testPojo, final SessionFactory sessionFactory) throws Exception {
        Session dbSession = null;
        Transaction transaction = null;
        try {
            dbSession = sessionFactory.openSession();
            transaction = dbSession.beginTransaction();

            // Set all previous state of the student as not current.
            List<Temp> oldActivePojos = (List<Temp>) dbSession.createCriteria(Temp.class)
                    .add(Restrictions.eq("studentId", testPojo.getStudentId())).add(Restrictions.eq("current", true))
                    .list();
            for(final Temp oldActivePojo : oldActivePojos) {
                oldActivePojo.setCurrent(false);
                oldActivePojo.setClosedAt(new Date());

                dbSession.update(oldActivePojo);
                LOG.debug(String.format("  Updated old state as inactive:%s", oldActivePojo));
            }
            if(!oldActivePojos.isEmpty()) {
                dbSession.flush();
            }

            LOG.debug(String.format("  saving state:%s", testPojo));
            dbSession.save(testPojo);
            LOG.debug(String.format("  new state saved:%s", testPojo));

            transaction.commit();

        }catch(Exception exception) {
            LOG.fatal(String.format("Exception in adding state: %s", testPojo), exception);
            transaction.rollback();
        }finally {
            dbSession.close();
        }
    }

Upon running the code, after a few runs, I am getting an index constraint exception. It happens because for some strange reason, it does not find the latest active record but instead some older stale active record and tries marking it as inactive before saving ( though the DB actually has a new active record already present).

Notice that both the code share the same sessionfactory and the both code works on a totally different tables. My guess is that some internal cache state gets dirty. If I use 2 different sessionfactory for the foreground and background thread, it works fine.

Another weird thing is that in the background thread ( where I print the no of users), if I wrap it in a transaction ( even though it is only a read operation), the code works fine! Sp looks like I need to wrap all DB operations ( irrespective of read / write ) in a transaction for it to work in a multithreaded environment.

Can someone point out the issue?

Answer 1

Yes, basically, transaction demarcation is always needed:

Hibernate documentation says:

Database, or system, transaction boundaries are always necessary. No communication with the database can occur outside of a database transaction (this seems to confuse many developers who are used to the auto-commit mode). Always use clear transaction boundaries, even for read-only operations. Depending on your isolation level and database capabilities this might not be required, but there is no downside if you always demarcate transactions explicitly.

When trying to reproduce your setup I experienced some problems caused by the lack of transaction demarcation (though not the same as yours). Further investigation showed that sometimes, depending on connection pool configuration, add() is executed in the same database transaction as the previous call(). Adding beginTransaction()/commit() to call() fixed that problem. This behaviour can be responsible for your problem, since, depending on transaction isolation level, add() can work with the stale snapshot of the database taken at the begining of transaction, i.e. during the previous call().