title: Multithread issues in using Hibernate SessionFactory
body: <p>Have a table 'temp' ..
  Code:</p>
  
  <pre><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
  </code></pre>
  
  <p>The corresponding Java pojo is <a href="http://pastebin.com/JHZwubWd" rel="nofollow">http://pastebin.com/JHZwubWd</a> . This table has a unique constraint such that only one record for each student can be active. </p>
  
  <p>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:</p>
  
  <pre><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&lt;Object&gt;() {
                  @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++ &gt; 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&lt;Temp&gt; oldActivePojos = (List&lt;Temp&gt;) 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();
          }
      }
  </code></pre>
  
  <p>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).</p>
  
  <p>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. </p>
  
  <p>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. </p>
  
  <p>Can someone point out the issue?</p>