title: How to avoid two different threads read the same rows from DB (Hibernate and Oracle 10g)
body: <p>Let's suppose I got two different threads, T1 and T2, accessing concurrently the same database and fetching data from the same table.</p>
  
  <p>Now on thread startup I need to fetch data from the table and store the rows into a collection, that I will then use to perform some work elsewhere. I don't want the two threads to be able to process the same data, because it will result in duplicated (and long) work. To be more concrete, this is an enterprise application that needs to load some records at startup and store it in a collection to do some extra work. The problem is that in a clustered environment, this can cause two different instances to load the same data and so the work could be duplicated. So I want the rows to be loaded only once by a single instance.</p>
  
  <p>How can I avoid that scenario? </p>
  
  <p>I'm currently using Hibernate and Oracle 10g. These are my solutions up to now:</p>
  
  <ul>
  <li><p>Lock the row programmatically. The first one that reads it sets some "locked" column to true, but deadlock is very likely to occur if the first thread dies without setting the row as "processed".</p></li>
  <li><p>Using Pessimistic Locking. I tried with LockMode.UPGRADE but that doesn't seem to help, as I'm still able to read the data from both thread at the same time. </p></li>
  </ul>
  
  <blockquote>
  <pre><code>public List&lt;MyObject&gt; getAllNtfFromDb() {
        Session session = HibernateUtil.getOraclesessionfactory().openSession();
        Query q = session.createQuery(
                "from MyObject n where n.state = 'NEW'");
      List&lt;MyObject&gt; list = (List&lt;MyObject&gt;) q.list();
        for (int i=0; i&lt;list.size(); i++)
            session.lock(list.get(i), LockMode.UPGRADE);
  return list;
  }
  </code></pre>
  </blockquote>
  
  <p>Any other hints? What am I doing wrong? </p>
  
  <p>Thanks.</p>