title: JPA conditional insertion
body: <p>I have a Java Spring based web application and I want to insert a record to a table only if the table does not contain any rows that are "similar" (according to some specific, irrelevant criteria) to the new row.</p>
  
  <p>Because this is a multi-threaded environment, I cannot use a SELECT+INSERT two-step combination as it would expose me to a race condition.</p>
  
  <p>The same question was first asked and answered <a href="https://www.quora.com/Relational-Databases/JPA-conditional-insertion-RDBMS-transactions-isolation" rel="nofollow noreferrer">here</a> and <a href="https://stackoverflow.com/questions/8985006/jpa-conditional-insertion-rdbms-transactions-isolation">here</a> several years ago. Unfortunately, the questions have got only a little attention and the provided answer is not sufficient to my needs.</p>
  
  <p>Here's the code I currently have and it's not working:</p>
  
  <pre><code>@Component("userActionsManager")
  @Transactional
  public class UserActionsManager implements UserActionsManagerInterface {
  
      @PersistenceContext(unitName = "itsadDB")
      private EntityManager manager;
  
      @Resource(name = "databaseManager")
      private DB db;
  
      ...
  
      @SuppressWarnings("unchecked")
      @Override
      @PreAuthorize("hasRole('ROLE_USER') &amp;&amp; #username == authentication.name")
      public String giveAnswer(String username, String courseCode, String missionName, String taskCode, String answer) {
          ...
  
          List&lt;Submission&gt; submissions = getAllCorrectSubmissions(newSubmission);
          List&lt;Result&gt;     results     = getAllCorrectResults(result);
  
          if (submissions.size() &gt; 0
          ||  results.size()     &gt; 0) throw new SessionAuthenticationException("foo");
  
          manager.persist(newSubmission);
          manager.persist(result);
  
          submissions = getAllCorrectSubmissions(newSubmission);
          results     = getAllCorrectResults(result);
  
          for (Submission s : submissions) manager.lock(s, LockModeType.OPTIMISTIC_FORCE_INCREMENT);
          for (Result     r : results    ) manager.lock(r, LockModeType.OPTIMISTIC_FORCE_INCREMENT);
  
          manager.flush();
  
          ...
      }
  
      @SuppressWarnings("unchecked")
      private List&lt;Submission&gt; getAllCorrectSubmissions(Submission newSubmission) {
          Query q = manager.createQuery("SELECT s FROM Submission AS s WHERE s.missionTask = ?1 AND s.course = ?2 AND s.user = ?3 AND s.correct = true");
          q.setParameter(1, newSubmission.getMissionTask());
          q.setParameter(2, newSubmission.getCourse());
          q.setParameter(3, newSubmission.getUser());
          return (List&lt;Submission&gt;) q.getResultList();
      }
  
      @SuppressWarnings("unchecked")
      private List&lt;Result&gt; getAllCorrectResults(Result result) {
          Query q = manager.createQuery("SELECT r FROM Result AS r WHERE r.missionTask = ?1 AND r.course = ?2 AND r.user = ?3");
          q.setParameter(1, result.getMissionTask());
          q.setParameter(2, result.getCourse());
          q.setParameter(3, result.getUser());
          return (List&lt;Result&gt;) q.getResultList();
      }
  
  ...
  
  }
  </code></pre>
  
  <p>According to the answer provided <a href="https://www.quora.com/Relational-Databases/JPA-conditional-insertion-RDBMS-transactions-isolation" rel="nofollow noreferrer">here</a> I am supposed to somehow use OPTIMISTIC_FORCE_INCREMENT but it's not working. I suspect that the provided answer is erroneous so I need a better one.</p>
  
  <p><strong>edit:</strong></p>
  
  <p>Added more context related code. Right now this code still has a race condition. When I make 10 simultaneous HTTP POST requests approximately 5 rows will get erroneously inserted. Other 5 requests are rejected with HTTP error code 409 (conflict). The correct code would guarantee that only 1 row would get inserted to the database no matter how many concurrent requests I make. Making the method synchronous is not a solution since the race condition still manifests for some unknown reason (I tested it).</p>