Multithreaded Issues When Using Hibernate SessionFactory

Question

There is 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 

Relevant Java pojo http://pastebin.com/JHZwubWd . This table has a unique limitation, so only one entry per student can be active.

2) I have a test code that tries to constantly add entries for the student (each time the old active is active as inactive and adds a new active entry), as well as in another topic that addresses some random (unrelated) 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(); } } 

After running the code after several runs, I get an index restriction exception. This is because, for some strange reason, it does not find the last active record, and instead the old obsolete active record and tries to mark it as inactive before saving (although the database actually has a new active record already present).

Note that both codes use the same sessionfactory, and both codes work on completely different tables. I assume that some internal cache state becomes dirty. If I use 2 different sessions for background and background threads, it works fine.

Another oddity is that in the background thread (where I print no users), if I wrap it in a transaction (although this is just a read operation), the code works fine! Sp looks like I need to wrap all DB operations (regardless of read / write) in a transaction so that it works in a multi-threaded environment.

Can anyone point out a problem?

Answer 1

Yes, basically, transaction demarcation is needed:

Hibernate documentation says:

A database or system, transaction boundaries are always needed. No connection to the database can occur outside of the 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. This may not be necessary depending on the isolation level and database capabilities, but there are no drawbacks if you always clearly separate transactions.

When I tried to reproduce the settings, I ran into some problems caused by the lack of transaction demarcation (although not the same as yours). Further research showed that sometimes, depending on the connection pool configuration, add() is executed in the same database transaction as the previous call() . Adding beginTransaction() / commit() to call() fixed this problem. This behavior may be responsible for your problem, because depending on the transaction isolation level add() may work with an outdated database snapshot taken at the beginning of the transaction, i.e. during the previous call() .